IN operator

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

Overview

expr IN (value1, value2, ...) evaluates to TRUE if expr matches any value in the list, and is equivalent to chaining equality checks with OR: expr = value1 OR expr = value2 OR .... It works with a literal list or with a subquery that returns a single column.

Copy code
SELECT * FROM orders WHERE status IN ('completed', 'shipped', 'delivered');

SELECT * FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_total > 1000);

NOT IN

NOT IN negates the check, but has a well-known trap: if the list (or subquery result) contains even one NULL, the entire NOT IN expression evaluates to NULL/unknown for every row, silently returning zero rows instead of the expected result.

Copy code
-- Dangerous if excluded_ids can contain NULL:
SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM excluded_ids);

-- Safer:
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_ids e WHERE e.customer_id = o.customer_id
);

IN with a subquery vs EXISTS

IN and EXISTS often solve the same problem -- filtering rows based on membership in another table. EXISTS (with a correlated subquery) is generally the safer choice when NULLs might be present, since it uses existence rather than value equality.

DuckDB notes

DuckDB implements IN and NOT IN per the ANSI SQL standard, including the NULL propagation behavior described above. DuckDB optimizes IN lists and IN subqueries into efficient hash-based semi-joins internally, so there's no need to manually rewrite an IN subquery into a JOIN for performance.

Related terms

EXISTS →

EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.

BETWEEN →

The BETWEEN operator tests whether a value falls within an inclusive range, equivalent to combining two comparisons with AND.

NULL →

NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

LIKE operator →

The LIKE operator performs pattern matching on string values, using % as a wildcard for any number of characters and _ for a single character.

LATERAL JOIN →

A LATERAL join lets a subquery in the FROM clause reference columns from earlier tables in the same FROM clause, enabling per-row correlated logic without a scalar subquery.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

FAQS

They're logically equivalent -- col IN (a, b, c) is shorthand for col = a OR col = b OR col = c -- but IN is more concise and typically easier for the query optimizer to reason about.

If the list or subquery on the right side of NOT IN contains a NULL, the whole expression evaluates to unknown for every row, so no rows are returned; use NOT EXISTS with a correlated subquery to avoid this trap.

No -- a subquery used with IN must return a single column; to compare multiple columns at once, use a row-value comparison where supported, or restructure with EXISTS.