PostgreSQL ALL

PostgreSQL ALL Operator

The ALL operator is a logical operator in PostgreSQL. It is used to compare a single value with a list of values returned by a subquery.

ALL returns TRUE if all of the values from the subquery satisfy the condition. Otherwise, it returns FALSE.

Basic Syntax

The ALL operator must be preceded by a comparison operator. These include =, !=, >, <, >=, <=.

It is used in a WHERE or HAVING clause.

SELECT column_name(s)
FROM table_name
WHERE column_name comparison_operator ALL (subquery);

A Practical Example

Let's find all products whose price is greater than the price of every single product from a specific supplier. For example, find products more expensive than all products from supplier with SupplierID = 1.

The subquery will get all prices for that supplier. The outer query will then check if a product's price is greater than ALL of those values.

-- Find products more expensive than every product from supplier 1
SELECT ProductName, Price
FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products WHERE SupplierID = 1
);

This is logically equivalent to finding products with a price greater than the MAX() price from that supplier. WHERE Price > (SELECT MAX(Price) FROM Products WHERE SupplierID = 1)

Another Example with < ALL

Now let's find products that are cheaper than every single product from another supplier. We can use the < operator with ALL.

-- Find products cheaper than every product from supplier 2
SELECT ProductName, Price
FROM Products
WHERE Price < ALL (
  SELECT Price FROM Products WHERE SupplierID = 2
);

This is logically equivalent to finding products with a price less than the MIN() price from that supplier.

The = ALL Operator

The = ALL operator is rarely used in practice. It checks if a value is equal to every value in the subquery's result set.

This would only be true if the subquery returns exactly one row with that specific value. Or if the subquery returns multiple rows that all contain the exact same value.

The != ALL Operator

The != ALL operator means "not equal to any value in the list". This is equivalent to using NOT IN.

For example, col != ALL (1, 2) is the same as col NOT IN (1, 2). It means the column value is not 1 AND it is not 2.

Summary

The ALL operator is a powerful way to compare a value against an entire list. It must be used with a comparison operator. It returns true only if the comparison holds for every value in the subquery result.