ALL Operator in PostgreSQL

The PostgreSQL ALL operator compares a value to every value in a set returned by a subquery. It returns true only when the comparison is true for every element in the set. If the subquery returns zero rows, ALL always returns true — because there are no counterexamples. This makes ALL the strict counterpart to ANY.

Syntax

expression operator ALL (subquery)

• operator must be a comparison operator: =, <>, >, >=, <, <=.
• The subquery must return exactly one column.
• If the subquery returns zero rows, ALL evaluates to true for any operator.

Key semantics by operator:

Practical Example

Create a products table and a premium_tier table that defines the price range of premium products:

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(50),
  unit_price   NUMERIC(10, 2) NOT NULL
);

CREATE TABLE premium_tier (
  tier_id      SERIAL PRIMARY KEY,
  product_id   INT REFERENCES products (product_id),
  tier_price   NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (name, category, unit_price) VALUES
  ('Budget Case',    'Accessories',  8.99),
  ('Standard Case',  'Accessories', 19.99),
  ('Pro Case',       'Accessories', 34.99),
  ('Elite Case',     'Accessories', 75.00),
  ('Budget Stand',   'Accessories',  5.50),
  ('Pro Stand',      'Accessories', 28.00);

INSERT INTO premium_tier (product_id, tier_price) VALUES
  (3, 34.99),
  (4, 75.00),
  (6, 28.00);

Products priced higher than all premium tier prices

Find products whose unit price exceeds every premium tier price — meaning they cost more than the most expensive premium product:

SELECT product_id, name, unit_price
FROM products
WHERE unit_price > ALL (
  SELECT tier_price
  FROM premium_tier
)
ORDER BY unit_price;

The highest premium tier price is 75.00. No product in this dataset exceeds that, so the query returns zero rows. If Elite Case were priced at 100.00, it would be the sole result.

Products priced lower than all premium tier prices

Find products cheaper than every premium tier price — meaning they cost less than the least expensive premium product (28.00):

SELECT product_id, name, unit_price
FROM products
WHERE unit_price < ALL (
  SELECT tier_price
  FROM premium_tier
)
ORDER BY unit_price;

 product_id |     name      | unit_price
------------+---------------+------------
          5 | Budget Stand  |       5.50
          1 | Budget Case   |       8.99
          2 | Standard Case |      19.99
(3 rows)

All three products cost less than 28.00 (the minimum premium tier price), so they satisfy < ALL.

Using <> ALL to exclude specific values

Find all products whose price does not match any premium tier price (equivalent to NOT IN):

SELECT product_id, name, unit_price
FROM products
WHERE unit_price <> ALL (
  SELECT tier_price
  FROM premium_tier
)
ORDER BY unit_price;

 product_id |     name      | unit_price
------------+---------------+------------
          5 | Budget Stand  |       5.50
          1 | Budget Case   |       8.99
          2 | Standard Case |      19.99
(3 rows)

ALL vs. ANY vs. NOT IN

NULL caveat: <> ALL and NOT IN both behave identically when a NULL appears in the subquery result — the comparison evaluates to NULL (unknown) rather than true, silently excluding all rows. Use NOT EXISTS when the subquery column is nullable.

Testing with Vela

ALL with an empty subquery silently passes every row, which can lead to unintended full-table access in production if the lookup table is empty. Vela’s database branching lets you clone your production database, introduce edge cases (like an empty premium tier), and verify query behavior before deploying to production.

Production Tips

• Index the column used in the ALL subquery (e.g., tier_price) to avoid sequential scans inside the subquery.
• Use EXPLAIN ANALYZE to verify the inner scan is efficient — the planner sometimes rewrites > ALL as > (SELECT MAX(...)).
• > ALL(subquery) is semantically equivalent to > (SELECT MAX(col) FROM ...). For readability, the MAX form can be clearer in code reviews.
• <> ALL and NOT IN are equivalent in behavior but both fail silently when the subquery contains NULLs. Prefer NOT EXISTS for nullable columns.
• When the subquery might legitimately return zero rows and vacuous truth is not the desired behavior, add a guard: WHERE EXISTS (SELECT 1 FROM premium_tier) AND unit_price > ALL (SELECT tier_price FROM premium_tier).