PostgreSQL ALL Operator

PostgreSQL ALL Operator

The ALL operator in PostgreSQL is used to compare a value with every value returned by a subquery or array.
The condition is TRUE only if it satisfies the comparison for all values.

🔹 Think of ALL as “AND logic” over multiple values.

1️⃣ Basic ALL Syntax

With a subquery

expression operator ALL (subquery);

With an array

expression operator ALL (ARRAY[value1, value2, value3]);

2️⃣ Simple ALL Example (Subquery)

Find products whose price is greater than all products in category mobile.

SELECT *
FROM products
WHERE price > ALL (
SELECT price
FROM products
WHERE category = 'mobile'
);

✔ Returns products priced higher than the most expensive mobile.

3️⃣ ALL with Comparison Operators

Operator Meaning with ALL
= ALL Equal to every value
> ALL Greater than maximum
< ALL Less than minimum
>= ALL ≥ maximum
<= ALL ≤ minimum
!= ALL Not equal to any value

Example:

SELECT *
FROM employees
WHERE salary != ALL (
SELECT salary FROM employees WHERE department = 'HR'
);

✔ Excludes employees with any HR salary value.

4️⃣ ALL with Array Example

SELECT *
FROM users
WHERE age > ALL (ARRAY[18, 21, 25]);

✔ Returns users older than 25.

5️⃣ ALL vs ANY (Key Difference)

ALL ANY
Must satisfy all values Satisfy at least one
AND logic OR logic
More restrictive Less restrictive

Example:

-- ANY
salary > ANY (subquery); -- greater than minimum

-- ALL
salary > ALL (subquery); -- greater than maximum

6️⃣ ALL vs IN

WHERE age = ALL (ARRAY[25, 25, 25]); -- works
WHERE age IN (25); -- simpler

✔ IN is preferred for equality checks.

7️⃣ ALL with UPDATE

UPDATE products
SET status = 'premium'
WHERE price > ALL (
SELECT price FROM products WHERE category = 'basic'
);

8️⃣ Edge Cases (Important!)

Empty Subquery

price > ALL (empty_set) → TRUE
price > ANY (empty_set) → FALSE

✔ This difference is critical in logic-heavy queries.

9️⃣ Common Mistakes

❌ Confusing ALL with ANY
✔ Remember: ALL = every value

❌ Using ALL for existence checks
✔ Use EXISTS instead

🔟 Best Practices

✔ Use ALL when comparing against extremes (max/min)
✔ Use ANY or IN for less strict logic
✔ Index subquery columns for performance
✔ Always test edge cases

Quick Summary

> ALL → greater than maximum
< ALL → less than minimum
!= ALL → not equal to any value

You may also like...