PostgreSQL ANY Operator

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

Getting Started with PostgreSQL ANY Operator #

In PostgreSQL, the ANY operator allows you to compare a value with a set of values returned by a subquery.

Here’s the syntax of the ANY operator:

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

In this syntax:

• First, specify a value, which can be a column or an expression, you want to compare.
• Second, provide a comparison operator (=, <, >, <=, >=, and !=) to compare the value with a set of values.
• Third, define a subquery within the parentheses () followed by the ANY operator. The subquery needs to return a list of values for comparing with the value. In other words, it should returns a result set that consists of a single column.

The ANY operator returns true if the comparison returns true for at least one value in the set. The ANY operator returns false if all comparisons are false.

If the subquery returns no row, the ANY operator always returns true.

In PostgreSQL, SOME and ANY are synonyms, so you can use them interchangeably.

PostgreSQL ANY operator example #

Suppose we a products table that includes id, name, price, and brand:

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: PHP (php)

The following example uses the ANY operator to find all products whose prices are higher than any prices of Apple products:

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

Try it

Output:

       name        |  brand  |  price
-------------------+---------+---------
 iPhone 16 Pro Max | Apple   | 1399.99
 iPhone 16 Plus    | Apple   | 1199.99
 Galaxy S24 Ultra  | Samsung | 1299.99
 Galaxy S24 Plus   | Samsung | 1119.99Code language: plaintext (plaintext)

How it works.

First, the subquery returns the prices of all Apple products:

  price
---------
 1099.99
 1399.99
 1199.99Code language: plaintext (plaintext)

Second, the WHERE clause compares the price of each product in the outer query to the set of prices returned by the subquery using the greater than (>).

It returns true if the price is higher than any of the prices of Apple products. If so, the WHERE clause includes the product in the result set.

Rewriting the ANY operator #

You can rewrite the above query using the MIN aggregate function to find products whose prices are higher than any of Apple products:

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

Try it

First, the subquery returns the lowest price of all Apple’s products:

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

Try it

Output:

   min
---------
 1099.99Code language: plaintext (plaintext)

Second, the outer query returns products whose price is higher than the lowest price of all Apple’s products.

Summary #

• Use the ANY operator to compare a value with a set of values a subquery returns.

Quiz #