PostgreSQL ANY Array vs IN: When Each One Wins

· 7 MIN

TL;DR: In PostgreSQL, col = ANY(array) and col IN (list) return the same rows, and PostgreSQL turns an IN list into = ANY(ARRAY[...]) internally. Use = ANY($1) when your app sends a list of unknown length as one array parameter; use IN for short literal lists and single values. AI for Database writes and runs these queries for you from plain English, and shows the SQL behind every answer.

Try AI for Database free at aifordatabase.com. Sign up at app.aifordatabase.com/signup, sample data included.

The PostgreSQL ANY array operator compares a value against every element of an array: WHERE id = ANY(ARRAY[1,2,3]) is true when id equals any element. It returns the same result as WHERE id IN (1,2,3). The differences are practical: ANY takes a single array value (so a driver can bind a whole list as $1), it works with any operator (LIKE ANY, <> ANY, > ANY), it returns false for an empty array, and the planner treats a one-element ANY differently from IN (1).

PostgreSQL ANY vs IN: comparison table

Checked against the PostgreSQL 19 documentation (released November 19, 2026), read 2026-11-22.

CriterionAI for Database (asks for you)= ANY(array)IN (list)= ANY(subquery) / IN (subquery)
What you writeA question in plain English; the SQL is shownid = ANY(ARRAY[1,2,3]) or id = ANY($1)id IN (1,2,3)id IN (SELECT ...)
Right-hand sideGenerated per questionOne array valueA list of expressionsA subquery
Bind a variable-length list as one parameterNot needed, no codeYes, $1 is an int[]No, needs $1, $2, ... $nNot applicable
Other operatorsAny filter you describeLIKE ANY, ILIKE ANY, <> ANY, >Equality only=, <, > and others with ANY
Empty inputNot applicable= ANY('{}') is falseIN () is a syntax errorNo rows: false
NULL in the list, no matchNot applicableNULLNULLNULL
SQL standardNot applicablePostgreSQL extensionStandard SQLStandard SQL
Single value planNot applicableKept as = ANYRewritten to = 1Depends on subquery
IN list and = ANY array return the same rows from the same table
FIG 01 — Same result set; the difference is how the list is passed.

How AI for Database handles this for you

If you are filtering a Postgres table by a list of customers, plans or statuses, the ANY vs IN choice is a detail you should not have to think about. In AI for Database you connect your PostgreSQL or Supabase database and ask "Show me revenue for the enterprise and team plans last month". It writes the SQL, runs it read-only, and returns a table or chart with the query shown, so an engineer can check whether it used IN or = ANY and copy it into code. Pin the answer to a self-refreshing dashboard, or set a workflow such as "when refunds for these plans pass 20 a day, send a Slack alert". See the PostgreSQL connector page for setup.

It does not tune your application queries or rewrite your ORM. For plan analysis on your own queries, see our guide to PostgreSQL EXPLAIN.

What is the difference between ANY and IN in PostgreSQL?

IN takes a parenthesized list of expressions and is shorthand for col = v1 OR col = v2 OR .... ANY takes one expression that must yield an array, and compares the left value to each element with the operator you choose. PostgreSQL's planner turns a literal IN list into = ANY(ARRAY[...]) internally, which is why EXPLAIN on an IN query shows a condition like this:

EXPLAIN SELECT * FROM orders WHERE id IN (1, 2, 3);
-- Index Scan using orders_pkey on orders
--   Index Cond: (id = ANY ('{1,2,3}'::integer[]))

The array forms are PostgreSQL extensions. IN (list) is standard SQL, so it is the portable choice if the same query runs on MySQL or SQL Server.

A common mistake is putting an array inside IN:

-- Wrong: compares id to the whole array value, fails for an integer column
SELECT * FROM orders WHERE id IN (ARRAY[5, 6]);

-- Right
SELECT * FROM orders WHERE id = ANY(ARRAY[5, 6]);

How do I pass an array parameter to a query?

Bind the list as one array and use = ANY($1). A single placeholder in IN ($1) expects one scalar, so sending six IDs fails with a wrong-parameter-count or type error. Crunchy Data documented this with the Vert.x Postgres client; the same holds for node-postgres, psycopg and pgx.

-- One parameter, any number of IDs
SELECT * FROM orders WHERE customer_id = ANY($1::bigint[]);
# psycopg 3: a Python list is adapted to a Postgres array
cur.execute("SELECT * FROM orders WHERE customer_id = ANY(%s)", ([101, 102, 103],))
// node-postgres: a JS array is sent as a Postgres array
await client.query('SELECT * FROM orders WHERE customer_id = ANY($1::bigint[])', [[101, 102, 103]]);

Two side benefits: the query text stays the same for any list length, so pg_stat_statements records one entry instead of one per list size, and you do not need to build placeholder strings yourself, which removes a source of SQL injection bugs.

Binding a list: IN needs one placeholder per value, ANY takes one array parameter
FIG 02 — ANY keeps the SQL text fixed for any list length.

How does ANY handle NULLs?

ANY follows SQL's three-valued logic, the same as IN. From the PostgreSQL docs:

SELECT 3 = ANY(ARRAY[1, 2, NULL]);  -- NULL
SELECT 2 = ANY(ARRAY[1, 2, NULL]);  -- true
SELECT 3 = ANY('{}'::int[]);        -- false
SELECT 3 <> ALL(ARRAY[1, 2, NULL]); -- NULL, the NOT IN trap

In a WHERE clause NULL behaves like false for that row, so the risk is in the negative form. NOT IN and <> ALL return no rows at all when the list holds a NULL. The docs' own tip is to express conditions positively. If the list comes from user data, strip NULLs with array_remove(arr, NULL) first. For NULL handling in general, see NVL in Postgres.

Is = ANY faster than IN?

For literal lists, no: they become the same plan. Before PostgreSQL 14, large IN lists and ANY arrays were checked element by element; version 14 added hashed lookups for long constant lists, which removed the small edge Kaarel Moppel and others had measured for ANY.

The one case where they differ is a single value. The parser rewrites IN (1) to = 1, while = ANY(ARRAY[1]) stays an array comparison. Moppel reported a production query where that difference changed the plan and the runtime by roughly 90,000x. If your app sometimes sends one ID, either branch to = $1 for single values or check EXPLAIN with a one-element array.

EXPLAIN SELECT * FROM events WHERE project_id = ANY(ARRAY[1]::int8[]);
EXPLAIN SELECT * FROM events WHERE project_id IN (1);
-- compare the two plans on your own data

ANY with other operators

ANY takes any boolean operator, which IN cannot do:

-- Match several patterns at once
SELECT * FROM users WHERE email ILIKE ANY(ARRAY['%@acme.com', '%@globex.com']);

-- Greater than at least one threshold
SELECT * FROM orders WHERE amount > ANY(ARRAY[100, 500]);

-- Array column contains a value
SELECT * FROM posts WHERE 'postgres' = ANY(tags);

The last form is the usual way to filter on an array column. ALL is the counterpart: amount > ALL(ARRAY[100, 500]) needs every comparison true.

Choose ANY if / choose IN if

Choose = ANY(array) if:

Choose IN (list) if:

Use IN (subquery) or EXISTS when the values come from another table. Related reading: PostgreSQL CASE WHEN.

Skip the syntax

The ANY vs IN choice matters when you write the SQL. If you just need the answer, ask your Postgres database in plain English and check the SQL it shows.

Try AI for Database free at aifordatabase.com. Sign up at app.aifordatabase.com/signup, sample data included.

Sources: PostgreSQL docs 9.26 Row and Array Comparisons, PostgreSQL docs 9.24 Subquery Expressions, Crunchy Data: Using ANY Instead of IN, Kaarel Moppel: IN is not the same as ANY, Tom Lane on pgsql-performance.

Frequently Asked Questions

What is the difference between ANY and IN in PostgreSQL?

IN takes a list of expressions and is standard SQL; ANY takes one array value and is a PostgreSQL extension. For a literal list they return the same rows and get the same plan, because PostgreSQL rewrites IN lists as = ANY(ARRAY[...]).

How do I pass an array parameter to a query?

Write WHERE col = ANY($1) and bind a single array, such as a Python list with psycopg or a JavaScript array with node-postgres. IN ($1) accepts one scalar only.

How does ANY handle NULLs?

A NULL array gives NULL, a NULL left value gives NULL, and a NULL element with no match gives NULL. An empty array gives false.

Is = ANY faster than IN?

Not for literal lists; they share a plan, and PostgreSQL 14 added hashed lookups for long lists. A one-element ANY array can get a different plan than IN (1), so test that case with EXPLAIN.

Can I use NOT ANY in PostgreSQL?

There is no NOT ANY operator. Use NOT (col = ANY(arr)) or col <> ALL(arr), and remove NULLs from the array first, or both return NULL for every row.

Ready to try AI for Database?

Query your database in plain English. No SQL required. Start free today.