PostgreSQL ALL Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL ALL operator to compare a value with all values in a set of values returned by a subquery.

Getting Started with the PostgreSQL ALL operator #

The PostgreSQL ALL operator allows you to compare a value with a set of values returned by a subquery. The ALL operator returns true if the comparison is true for all values in the set.

Here’s the syntax of the ALL operator:

value operator ALL(subquery)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• value is a column or an expression you want to compare.
• operator can be one of a comparison operators (=, <, >, <=, >=, and !=).
• subquery is a query that returns a result set of a single column.

The ALL operator returns true if all comparisons are true. Otherwise, it returns false.

If the subquery returns no rows, the ALL operator returns true.

PostgreSQL ALL Operator example #

Suppose we have the following products table:

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  price DEC(11, 2) NOT NULL CHECK (price > 0),
  brand VARCHAR(50) NOT NULL
);
INSERT INTO
  products (name, price, brand)
VALUES
  ('Galaxy S24', 799.99, 'Samsung'),
  ('iPhone 16', 1099.99, 'Apple'),
  ('iPhone 16 Pro Max', 1399.99, 'Apple'),
  ('iPhone 16 Plus', 1199.99, 'Apple'),
  ('Galaxy S24 Ultra', 1299.99, 'Samsung'),
  ('Galaxy S24 Plus', 1119.99, 'Samsung');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement uses the ALL operator to find all products that are more expensive than all Samsung products:

SELECT
  name,
  brand,
  price
FROM
  products
WHERE
  price > ALL (
    SELECT
      price
    FROM
      products
    WHERE
      brand = 'Samsung'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        | brand |  price
-------------------+-------+---------
 iPhone 16 Pro Max | Apple | 1399.99

How the query works.

The subquery selects the prices of all products from the Samsung brand:

SELECT
  price
FROM
  products
WHERE
  brand = 'Samsung'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It returns three rows:

  price
---------
  799.99
 1299.99
 1119.99Code language: CSS (css)

The ALL operator compares the price of each product in the outer query to these prices.

The WHERE clause in the outer query checks if the price of each product is higher than all the prices of Samsung products.

The output shows that only iPhone 16 Pro Max has a price (1399.99) higher than prices of all Samsung products.

You can rewrite the above query using the MAX aggregate function:

SELECT
  name,
  brand,
  price
FROM
  products
WHERE
  price > (
    SELECT
      MAX(price)
    FROM
      products
    WHERE
      brand = 'Samsung'
  );Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

       name        | brand |  price
-------------------+-------+---------
 iPhone 16 Pro Max | Apple | 1399.99

How it works.

The subquery returns the highest price of all Samsung products:

SELECT
  MAX(price)
FROM
  products
WHERE
  brand = 'Samsung'Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   max
---------
 1299.99Code language: CSS (css)

The outer query compares the price of each product with the highest price of all Samsung products and selects the products that are more expensive than the most expensive Samsung product.

Summary #

• Use the PostgreSQL ALL operator to compare a value with all values returned by a subquery.

Quiz #