SQL - ALL Operator

The ALL operator compares a column value or literal value with the result of a subquery that returns a single-column values.

  • A subquery used with the ALL operator, can only return a single column values.
  • The ALL operator must be preceded by comparison operators like =, !=, >, >=, <, <=.
  • The ALL operator uses AND with the result values of a subquery to compare a column of the outer query.
  • The data type of the returned values from a subquery must be the same data type as the outer query expression.

Syntax:

expression <operator> ALL( subquery );
-- or    
SELECT * FROM table_name
WHERE column_name <operator> ALL( subquery );

Database like SQL Server, PostgreSQL, SQLite supports ALL operator with subquery whereas Oracle allows ALL operator with subquery and literal values.

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 10
2 'James' 'Bond' 10
3 'Neena' 'Kochhar' '[email protected]' 17000 20
4 'Lex' 'De Haan' '[email protected]' 15000 10
5 'Amit' 'Patel' 18000 10
6 'Abdul' 'Kalam' '[email protected]' 25000 20
Employee_backup Table
EmpId FirstName LastName Email Salary DeptId
1 'John' 'King' '[email protected]' 33000 10
2 'James' 'Bond' 10
3 'Neena' 'Kochhar' '[email protected]' 17000 20
4 'Lex' 'De Haan' '[email protected]' 15000 10

ALL with Expression

The ALL operator can be used with a scalar expression. For example, the following is used with IF condition:

SQL Script: ALL in IF Condition
IF 15000 <=  ALL(SELECT salary FROM Employee)
	print('All employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

Above, the expression IF 15000 < all(SELECT salary FROM Employee) would be transformed using AND operator, as below:

SQL Script: ALL in IF Condition
IF 15000 <= 33000 AND 15000 <= 17000 AND 15000 <= 15000 AND 15000 <= 18000 AND 15000 <= 25000
	print('All employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

So, the above expression would print All employee's salaries are equal to or more than 15000.

ALL with Query

The ALL operator can also be used with a query. The following query uses the ALL operator with the = operator.

SQL Script: ALL with =
SELECT * FROM Employee 
WHERE EmpId = ALL(
                    SELECT EmpId FROM Employee_backup 
                    WHERE Salary > 15000
            );

In the above query, a subquery SELECT EmpId FROM Employee_backup WHERE Salary > 15000 would be executed first, and it will return EmpId values 1, 3. Now, the ALL operator would use AND operator with all the return values and form the query as shown below.

SQL Script: All uses AND
SELECT * FROM Employee 
WHERE EmpId = 1 AND EmpId = 3;

So, it is obvious that the above query will not return anything because it is not possible that the EmpId will have two values in the same record.

Note:
The ALL operator uses AND with all the return values of a subquery. If a subquery returns multiple values, then the ALL operator with = will not return any records.

ALL with !=

The most practical use of the ALL operator is with the <> operator. For example, the following query finds the difference between the two tables.

SQL Script: ALL with <>
SELECT * FROM Employee 
WHERE EmpId != ALL(
                    SELECT EmpId FROM Employee_backup 
            );

The above query returns the records from the Employee table whose EmpId is not matching with the EmpId of the Employee_backup table. Think the above query would transform like the below:

SQL Script: ALL uses AND
SELECT * FROM Employee 
WHERE EmpId != 1 AND EmpId != 2 AND EmpId != 3 AND EmpId != 4; 

Thus, the above query would return the difference between two tables based on EmpId values. The following is the result of the above query.

EmpId FirstName LastName Email Salary DeptId
5 'Amit' 'Patel' 18000 1
6 'Abdul' 'Kalam' '[email protected]' 25000 2

In the same way, you can use the ALL operator with <=, <, =>, and > operators.