PostgreSQL ALL

Key ALL Concepts

WHERE value > ALL (
  SELECT column
  FROM table
)

-- True only if greater
-- than ALL values
price > ALL (...)

WHERE price > ALL (...)
WHERE date < ALL (...)
WHERE qty >= ALL (...)

-- ANY: at least one
-- ALL: every single one
value > ANY vs > ALL

🔹 Basic ALL Usage

The ALL operator requires that the comparison condition be true for every value returned by the subquery. This is useful when you need to ensure a value exceeds, is less than, or matches all values in a comparison set. It's stricter than ANY and is perfect for finding maximum or minimum values relative to a group.

-- Find employees earning more than ALL employees in Sales
SELECT employee_name, salary, department
FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees
    WHERE department = 'Sales'
);

🔹 ALL with Greater Than

Using ALL with the greater than operator finds values that are greater than every value in the subquery result. This is equivalent to finding values greater than the maximum value in the set. It's commonly used to identify records that exceed all benchmarks or thresholds in your comparison group, ensuring the highest standards are met.

-- Find products more expensive than ALL budget products
SELECT product_name, price, category
FROM products
WHERE price > ALL (
    SELECT price 
    FROM products
    WHERE category = 'budget'
)
ORDER BY price DESC;

🔹 ALL with Less Than

When using ALL with the less than operator, you're finding values that are less than every value in the subquery result. This is equivalent to finding values less than the minimum value in the set. It's useful for identifying the lowest performers, cheapest products, or records that fall below all comparison thresholds in your dataset.

-- Find orders with amounts less than ALL premium orders
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount < ALL (
    SELECT total_amount 
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.customer_type = 'premium'
);

🔹 ALL with Equality

Using ALL with the equality operator is rare but checks if a value equals every value in the subquery result. This only returns TRUE if all values in the subquery are identical and match the comparison value. It's useful in specific scenarios where you need to verify uniformity across a set of values or ensure consistency in your data.

-- Check if all products in a category have the same price
SELECT category, price
FROM products p1
WHERE price = ALL (
    SELECT price 
    FROM products p2
    WHERE p2.category = p1.category
);

🔹 ALL vs ANY Comparison

ALL and ANY are opposite operators. ANY returns TRUE if the condition is true for at least one value, while ALL requires the condition to be true for every value. Understanding this difference is crucial for writing correct queries. Use ANY for flexible matching and ALL for strict requirements where every value must meet the condition.

-- ANY: Returns TRUE if salary > at least one Sales salary
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department = 'Sales'
);

-- ALL: Returns TRUE only if salary > every Sales salary
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department = 'Sales'
);

🔹 ALL with NOT Operator

Combining ALL with the NOT operator creates complex logical conditions. NOT with ALL can be used to find values that don't match all values in a set, which is logically equivalent to matching at least one value that's different. This is useful for exclusion logic and finding exceptions in your data where uniformity doesn't exist.

-- Find products NOT equal to ALL discontinued products
SELECT product_name, status
FROM products
WHERE product_id <> ALL (
    SELECT product_id 
    FROM products
    WHERE status = 'discontinued'
);

-- Equivalent to:
-- WHERE product_id NOT IN (SELECT product_id FROM products WHERE status = 'discontinued')

🔹 ALL with Aggregate Functions

While you can use ALL with subqueries, sometimes using aggregate functions like MAX or MIN is clearer and more efficient. However, ALL is useful when you need to compare against a filtered set of values or when the logic is more complex than a simple maximum or minimum calculation, providing more flexibility in your queries.

-- Using ALL
SELECT product_name, price
FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'budget'
);

-- Equivalent using MAX (often more efficient)
SELECT product_name, price
FROM products
WHERE price > (
    SELECT MAX(price) FROM products WHERE category = 'budget'
);