SQL ALL Operator

The SQL ALL operator is used to compare a value to all values in a specified list or subquery. When used with comparison operators like =, >, <, >=, and <=, the ALL operator returns TRUE only if the comparison holds true for every value in the list or subquery.

In this tutorial, we will go through SQL ALL Operator, its syntax, and how to use this operator in to compare a value to all values in a specified list or subquery in SQL statements, with well detailed examples.

Syntax of SQL ALL Operator

The basic syntax of the SQL ALL operator is as follows:

</>
Copy
SELECT column1, column2, ...
FROM table_name
WHERE column_name operator ALL (subquery);

Each part of this syntax has a specific purpose:

  • SELECT: Specifies the columns to retrieve from the table.
  • FROM: Specifies the table from which to retrieve data.
  • WHERE: Introduces the condition used to filter the data.
  • operator: Any comparison operator like =, >, or <.
  • ALL: Returns TRUE if the comparison is true for every value in the subquery.
  • subquery: A query that returns a set of values for comparison.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the ALL operator in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records student grades.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

</>
Copy
CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);