PostgreSQL IN Operator

The IN operator in PostgreSQL tests whether a value matches any item in a list or the result of a subquery. It produces cleaner, more readable SQL than chaining multiple OR equality conditions and the query planner can often execute it more efficiently.

Syntax

-- List form
value IN (value1, value2, ...)

-- Subquery form
value IN (SELECT column FROM table WHERE condition)

-- Negation
value NOT IN (value1, value2, ...)

IN is equivalent to:

value = value1 OR value = value2 OR ...

NOT IN is equivalent to:

value <> value1 AND value <> value2 AND ...

Practical Example

Create a sample schema for a project tracking system:

CREATE TABLE departments (
  dept_id   SERIAL PRIMARY KEY,
  name      VARCHAR(80) NOT NULL,
  is_active BOOLEAN     NOT NULL DEFAULT TRUE
);

CREATE TABLE projects (
  project_id  SERIAL PRIMARY KEY,
  title       VARCHAR(150) NOT NULL,
  dept_id     INT REFERENCES departments (dept_id),
  priority    VARCHAR(10)  NOT NULL DEFAULT 'medium',
  budget_usd  NUMERIC(12,2),
  status      VARCHAR(20)  NOT NULL DEFAULT 'open'
);

INSERT INTO departments (name, is_active) VALUES
  ('Engineering',  TRUE),
  ('Marketing',    TRUE),
  ('Operations',   FALSE),
  ('Data Science', TRUE);

INSERT INTO projects (title, dept_id, priority, budget_usd, status) VALUES
  ('API Redesign',          1, 'high',   85000.00, 'open'),
  ('Brand Refresh',         2, 'medium', 40000.00, 'open'),
  ('Server Migration',      3, 'low',    22000.00, 'open'),
  ('ML Pipeline',           4, 'high',   120000.00,'open'),
  ('Analytics Dashboard',   4, 'medium', 35000.00, 'closed'),
  ('Mobile App',            1, 'high',   95000.00, 'open'),
  ('SEO Campaign',          2, 'low',    18000.00, 'closed');

Find open projects with high or medium priority:

SELECT title, priority, budget_usd
FROM projects
WHERE status = 'open'
  AND priority IN ('high', 'medium')
ORDER BY budget_usd DESC;

Expected result:

       title        | priority | budget_usd
--------------------+----------+------------
 ML Pipeline        | high     |  120000.00
 Mobile App         | high     |   95000.00
 API Redesign       | high     |   85000.00
 Brand Refresh      | medium   |   40000.00

Find projects belonging to active departments using a subquery:

SELECT p.title, d.name AS department
FROM projects p
JOIN departments d ON d.dept_id = p.dept_id
WHERE p.dept_id IN (
  SELECT dept_id FROM departments WHERE is_active = TRUE
)
ORDER BY d.name, p.title;

Verify the index is used with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT title FROM projects
WHERE priority IN ('high', 'medium');

NOT IN and NULL Pitfall

-- Safe: all IDs are non-NULL
SELECT title FROM projects
WHERE dept_id NOT IN (3, 4);

-- Dangerous: if the subquery returns any NULL, zero rows come back
SELECT title FROM projects
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE is_active = FALSE);

The subquery could return NULL if dept_id is nullable. Fix it with a NOT EXISTS alternative:

-- Safe alternative using NOT EXISTS
SELECT p.title
FROM projects p
WHERE NOT EXISTS (
  SELECT 1 FROM departments d
  WHERE d.dept_id = p.dept_id
    AND d.is_active = FALSE
);

IN with Large Lists

For very large sets of values (hundreds or thousands), a temporary table joined with INNER JOIN is more efficient than a long IN list:

-- Create a temp list
CREATE TEMP TABLE target_depts AS VALUES (1), (2), (4);

-- Join instead of IN list
SELECT p.title
FROM projects p
JOIN target_depts t ON t.column1 = p.dept_id;

Testing with Vela

IN with subqueries is common in data migration scripts and bulk update operations. Before running a large UPDATE ... WHERE id IN (SELECT ...) in production, use Vela’s database branching to validate the subquery returns the expected set of IDs. Clone your production database to a branch, run the SELECT form to inspect affected rows, then apply the UPDATE to the branch first to confirm the results before promoting the change to production.

Production Tips

• Replace same-column OR chains with IN — it is more readable and the planner handles it as a hash lookup for small lists.
• Always index the column tested with IN. The planner can use a bitmap index scan when the list has a reasonable number of values.
• Avoid NOT IN when the list or subquery might include NULL values — use NOT EXISTS instead.
• For IN (subquery), compare performance against EXISTS and INNER JOIN alternatives with EXPLAIN ANALYZE. The planner often transforms IN (subquery) into a semi-join internally, but explicit EXISTS can give the planner more flexibility.
• Large IN lists (thousands of values) increase query parsing and planning time. Prefer a temporary table or ANY(ARRAY[...]) for large dynamic sets.