PostgreSQL ANY Array vs IN: When Each One Wins
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.
| Criterion | AI for Database (asks for you) | = ANY(array) | IN (list) | = ANY(subquery) / IN (subquery) |
|---|---|---|---|---|
| What you write | A question in plain English; the SQL is shown | id = ANY(ARRAY[1,2,3]) or id = ANY($1) | id IN (1,2,3) | id IN (SELECT ...) |
| Right-hand side | Generated per question | One array value | A list of expressions | A subquery |
| Bind a variable-length list as one parameter | Not needed, no code | Yes, $1 is an int[] | No, needs $1, $2, ... $n | Not applicable |
| Other operators | Any filter you describe | LIKE ANY, ILIKE ANY, <> ANY, > | Equality only | =, <, > and others with ANY |
| Empty input | Not applicable | = ANY('{}') is false | IN () is a syntax error | No rows: false |
| NULL in the list, no match | Not applicable | NULL | NULL | NULL |
| SQL standard | Not applicable | PostgreSQL extension | Standard SQL | Standard SQL |
| Single value plan | Not applicable | Kept as = ANY | Rewritten to = 1 | Depends on subquery |

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.

How does ANY handle NULLs?
ANY follows SQL's three-valued logic, the same as IN. From the PostgreSQL docs:
- If the array itself is NULL, the result is NULL.
- If the left value is NULL, the result is ordinarily NULL.
- If the array contains a NULL and no element matches, the result is NULL, not false.
- An empty array returns false.
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 trapIn 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 dataANY 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:
- Your app passes a list of unknown length as a parameter.
- You need
LIKE,ILIKE,<>or range operators against a list. - You filter on an array column.
- You want one
pg_stat_statementsentry per query shape.
Choose IN (list) if:
- The list is short and literal in the SQL.
- The query must run on other databases too.
- You often pass a single value and want the
= 1rewrite.
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.