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.

• 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 Employee_backup tables in all examples.

ALL with Expression

The ALL operator can be used with a scalar expression. For example, the following is used with 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:

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.

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.

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.

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.

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:

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.

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