ALL

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.