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_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.