ALL Operator in PostgreSQL
In PostgreSQL, the ALL operator is a potent comparison operator that may be used with array expressions or subqueries to compare a condition to each value that the array contains or that the subquery returns. By stating that a condition must be true for every element in the given set, it serves as a quantifier.
Syntax and Basic Usage
The ALL operator usually comes before a parenthesised subquery or an array expression and after a comparison operator (like =, <, >, <=, >=, <>). The fundamental forms of syntax are:
- ALL is an expression operator (subquery).
- array expression using the expression operator ALL
- operator ALL for row_constructor (subquery)
The subquery must return precisely one column when using the expression operator ALL (subquery). Using the designated operator, the phrase on the left-hand side is assessed and contrasted with every row of the subquery result. The overall result of ALL is true if all comparisons produce true. Any comparison that produces a false result is false. ALL also evaluates to true if the subquery yields no rows.
For instance, to identify vehicles with more doors than any other value in a given list of door counts. SELECT min_doors FROM restrictions_table (SELECT car_name FROM cars WHERE number_of_doors > ALL);. Only if car_name’s number_of_doors is strictly greater than the maximum min_doors in restrictions_table will this query return it.
Comparison with Other Operators
In contrast to other comparison operators, the ALL operator is frequently understood as follows:
ANY (or SOME): The ANY operator in PostgreSQL, which is synonymous with SOME, contrasts with the ALL operator in that it compares criteria to a set of values that are either present in an array or returned by a subquery. If the comparison operator is true for at least one value in the set, ANY (or SOME) returns true, whereas ALL needs the condition to be true for every single item in the set.
IN and NOT IN: The IN and NOT IN operators are specialised comparison constructs that are commonly used with lists of values or subqueries in PostgreSQL. If the result of the left-hand expression equals any of the right-hand expressions which might be a parenthesised list of values or the single column result of a subquery the IN operator returns true.
Code Example:
CREATE TABLE numbers (
id SERIAL PRIMARY KEY,
value INT
);
INSERT INTO numbers (value) VALUES
(10), (20);
SELECT *
FROM (
VALUES
('50 > ALL', (50 > ALL (SELECT value FROM numbers))::text),
('15 > ALL', (15 > ALL (SELECT value FROM numbers))::text),
('15 > ANY', (15 > ANY (SELECT value FROM numbers))::text),
('5 > ANY', (5 > ANY (SELECT value FROM numbers))::text),
('20 IN', (20 IN (SELECT value FROM numbers))::text),
('15 NOT IN', (15 NOT IN (SELECT value FROM numbers))::text),
('20 NOT IN', (20 NOT IN (SELECT value FROM numbers))::text)
) AS results(test, result);Output:
CREATE TABLE
INSERT 0 2
test | result
-----------+--------
50 > ALL | true
15 > ALL | false
15 > ANY | true
5 > ANY | false
20 IN | true
15 NOT IN | true
20 NOT IN | false
(7 rows)Handling Multiple Columns (Row Constructors)
PostgreSQL uses row builders to expand the ALL operator to compare several columns simultaneously. A row constructor is a statement that combines many values into a single row or composite value using the ROW keyword or brackets. When row_constructor operator ALL (subquery) is used on the left side of an ALL comparison, the right-hand subquery must return the same number of columns as expressions in the left-hand row constructor.
Row-wise comparisons evaluate each pair of expressions from the left-hand row constructor and each row of the subquery result using the specified operator. All rows returned by the subquery must pass this row-wise comparison for the ALL condition to be true. If one row-wise comparison fails, the ALL condition fails. Interestingly, the ALL condition is true if the subquery returns no rows. For row constructor comparisons, the system offers operators like =, >,, =, >, or >=, which must be members of a B-tree operator class or negator.
Usage with Arrays
PostgreSQL ALL operator evaluates a condition against every element in an array. PostgreSQL extends SQL with this usage. The left-hand expression is compared to each array element using the given comparison operator when ALL is used. ALL is true if all these comparisons are true, even if the array has 0 elements. If even one array comparison fails, it returns false.
The query SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter) will only return records if every value in the array for a given employee is 10000. Similarly, a > ALL (ARRAY[x, y, z]) means ‘a’ must be greater than every entry in the array.
ALL will usually return NULL if the array expression on the right side generates a NULL array or if the left-hand expression is NULL (assuming a strict comparison operator). If the array contains NULL elements and no comparison explicitly returns false but at least one comparison returns NULL, ALL will return NULL (unknown). This matches SQL’s three-valued logic for NULL-valued Boolean operations. Although indicate GIN indexes handling arrays and operators like @>, <@, =, and &&, they do not specify direct indexing for the ALL operator with arrays.
NULL Value Handling
The way the ALL operator interacts with NULL values in PostgreSQL is crucial, because it is controlled by SQL’s three-valued logic, which comprises true, false, and unknown (denied by NULL). In order for the ALL operator to function, a comparison condition must be true for each result that the operator’s subquery returns or that is contained in an array expression. Incorporating a NULL value into this assessment may cause the result to change from a decisive true or false to NULL (unknown).
In particular, the ALL construct will produce NULL if the subquery or array on the right-hand side of the ALL operator contains NULL values and no comparison against the left-hand expression definitively yields false, but at least one comparison returns NULL (unknown). Because ALL requires that every comparison be true, this happens because if a comparison is unknown, the condition cannot be universally verified as true. SQL’s basic rules for Boolean combinations containing NULL values, which state that the majority of comparisons involving NULL also return NULL (unknown), are in line with this behaviour.
The result of ALL will also be NULL if the array expression itself evaluates to a NULL array. Similarly, assuming a rigorous comparison operator is applied, the result of ALL is often NULL if the left-hand expression being compared is NULL. Because direct comparisons with = NULL will always result in NULL, PostgreSQL offers special operators like IS NULL, IS NOT NULL, IS DISTINCT FROM, and IS NOT DISTINCT FROM to explicitly verify for the existence or absence of NULL values.
Performance Considerations
PostgreSQL’s ALL operator’s speed depends on how the query planner interprets the subquery or array expression. PostgreSQL’s query optimiser may not fully evaluate the right-hand side of an ALL expression if a condition is fulfilled or broken early. Developers shouldn’t assume ALL subqueries run to completion. Like EXISTS subqueries, this behaviour does not guarantee complete evaluation.
ALL’s performance is often compared to NOT IN when used with the <> operator. The term NOT IN (subquery) is semantically similar to <> ALL (subquery). The optimiser may struggle to use indexes to evaluate NOT IN queries, causing performance concerns in PostgreSQL. The nonexistence check can be more expensive than the existence check. Thus, ALL is a strong tool for universal comparisons, but its use, especially in NOT IN semantics, requires careful consideration of index utilisation and subquery evaluation strategies’ performance consequences. Rewriting NOT IN with EXISTS can increase subquery speed.
Conclusion
The powerful PostgreSQL ALL operator compares a condition to every subquery or array expression value. It acts as a universal quantifier in SQL comparisons by asserting that a condition must hold for all set elements. The ALL operator is used after a comparison operator (e.g. =, <, >) and before a parenthesised subquery or array expression. Subqueries must return one column when coupled. Evaluation and systematic comparison of the expression on the left side of the ALL operator against each row of the subquery’s result set follows.
A true ALL comparison result is only true if all comparisons are true. If one comparison is false, it resolves to false. ALL evaluates to true if the subquery produces no rows, which is intriguing. A > ALL (subquery) is true only if ‘a’ is greater than the subquery’s greatest value. PostgreSQL’s query optimiser may not always fully evaluate the subquery on the right-hand side of an ALL expression, therefore developers should not expect its complete execution.