The SQL ALL operator is a comparison operator used to check whether all values in a subquery meet a specified condition. It returns a Boolean value of true if all values satisfy the condition and false if at least one value does not satisfy the condition.
Syntax
The syntax for using the ALL operator is as follows:
SELECT column_name(s) FROM table_name WHERE column_name operator ALL (subquery);
In the syntax, column_name(s) refers to the name of the column(s) to be selected from the table_name. The operator is any valid comparison operator such as =, >, <, >=, <=, or <>. The subquery is a SQL query that returns one or more values to be compared with the selected column(s) using the specified operator.
Example
Here is an example to illustrate the usage of the ALL operator in SQL:
Suppose we have two tables named students and grades as shown below:
Students table:
| id | name | age |
|---|---|---|
| 1 | John Doe | 20 |
| 2 | Jane Doe | 19 |
| 3 | Bob Smith | 21 |
Grades table:
| student_id | grade |
|---|---|
| 1 | 80 |
| 1 | 85 |
| 2 | 90 |
| 3 | 75 |
We want to select the names of the students who have grades greater than or equal to 80 in all their courses. We can use the ALL operator to achieve this as follows:
SELECT name FROM students WHERE id = ALL (SELECT student_id FROM grades WHERE grade >= 80);
The subquery SELECT student_id FROM grades WHERE grade >= 80 returns the IDs of all students who have grades greater than or equal to 80. The main query then selects the names of all students whose IDs match all the IDs returned by the subquery.