ANY Operator in PostgreSQL

The PostgreSQL ANY operator compares a value to a set of values returned by a subquery using a comparison operator. It returns true if the comparison holds for at least one value in the set. SOME is an alias for ANY and behaves identically. Together they cover the “match any element” case that IN handles for equality, while also supporting inequality comparisons like > and <.

Syntax

expression operator ANY (subquery)

• expression — the value to compare, typically a column.
• operator — any comparison operator: =, <>, >, >=, <, <=.
• subquery — must return exactly one column. If it returns zero rows, ANY is always false.

SOME is a drop-in synonym:

expression operator SOME (subquery)

Practical Example

Create a products table and a featured_products table to track which products are currently promoted:

CREATE TABLE products (
  product_id   SERIAL PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(50),
  unit_cost    NUMERIC(10, 2) NOT NULL,
  stock_qty    INT NOT NULL DEFAULT 0
);

CREATE TABLE featured_products (
  feature_id   SERIAL PRIMARY KEY,
  product_id   INT REFERENCES products (product_id),
  feature_cost NUMERIC(10, 2) NOT NULL
);

INSERT INTO products (name, category, unit_cost, stock_qty) VALUES
  ('Widget A',     'Hardware', 9.99,  120),
  ('Widget B',     'Hardware', 14.99,  80),
  ('Gadget Pro',   'Software', 49.99,  30),
  ('Gadget Lite',  'Software', 19.99,  60),
  ('Component X',  'Hardware', 4.50,  350),
  ('Component Y',  'Hardware', 7.25,   90);

INSERT INTO featured_products (product_id, feature_cost) VALUES
  (1, 9.99),
  (3, 49.99),
  (6, 7.25);

Using ANY with the = operator

Find all products whose unit cost matches any featured product’s cost:

SELECT product_id, name, unit_cost
FROM products
WHERE unit_cost = ANY (
  SELECT feature_cost
  FROM featured_products
)
ORDER BY unit_cost;

 product_id |    name     | unit_cost
------------+-------------+-----------
          6 | Component Y |      7.25
          1 | Widget A    |      9.99
          3 | Gadget Pro  |     49.99
(3 rows)

= ANY(subquery) is equivalent to IN (subquery) for equality checks.

Using ANY with the > operator

Find products whose cost is higher than at least one featured product’s cost — meaning their price exceeds the lowest featured price:

SELECT product_id, name, unit_cost
FROM products
WHERE unit_cost > ANY (
  SELECT feature_cost
  FROM featured_products
)
ORDER BY unit_cost;

 product_id |    name      | unit_cost
------------+--------------+-----------
          1 | Widget A     |      9.99
          2 | Widget B     |     14.99
          4 | Gadget Lite  |     19.99
          3 | Gadget Pro   |     49.99
(4 rows)

Any product costing more than 7.25 (the lowest featured cost) satisfies the condition.

Using ANY with the < operator

Find products cheaper than at least one featured product:

SELECT product_id, name, unit_cost
FROM products
WHERE unit_cost < ANY (
  SELECT feature_cost
  FROM featured_products
)
ORDER BY unit_cost;

Any product costing less than 49.99 (the highest featured cost) satisfies the condition, returning five of the six products.

ANY vs. IN vs. ALL

Warning: <> ANY is not the same as NOT IN. <> ANY returns true if the value differs from at least one element — which is almost always true when the set has more than one distinct value. To exclude rows matching any element, use NOT IN or <> ALL.

Testing with Vela

ANY-based queries against large lookup tables can produce surprising execution plans once data volumes grow. Vela’s database branching lets you clone production data to a branch and run EXPLAIN ANALYZE against real row counts. Test whether the planner is choosing an index scan on the subquery column before the query reaches production traffic.

Production Tips

• Index the column in the subquery (e.g., featured_products.feature_cost) to speed up the inner scan, especially for large tables.
• Use = ANY(ARRAY[val1, val2, ...]) for a fixed list of values — it avoids a subquery and is easy to read.
• = ANY(subquery) and IN(subquery) are interchangeable for equality checks. Prefer whichever communicates intent more clearly to your team.
• Avoid <> ANY when you mean “not in any of these values” — use <> ALL or NOT IN instead, and be aware that NOT IN silently returns no rows when the subquery contains a NULL.
• If the subquery might return zero rows and you need a default behavior, consider wrapping it in a COALESCE or adding an OR condition for the empty-set case.