PostgreSQL: ALL Operator

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

• When ALL operator is used with =, a subquery should return a single column value.
• 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.

expression <operator> ALL( subquery );

-- or    

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

Let's use the following Employee and Employee_backup for demo purpose.

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 Emp_Id != ALL (
			SELECT Emp_Id FROM Employee_backup
		);

The above query returns the records from the Employee table whose emp_id is not matching with the emp_id of the Employee_backup table. The above query is same as the below query:

SELECT * FROM Employee 
    WHERE emp_id != 1 AND emp_id != 2 
    AND emp_id != 3 AND emp_id != 4;

Thus, the above query would return the difference between two tables based on EmpId values. In the same way, you can use the ALL operator with <, <=, > and >= operators.