---
title: "IN operator"
description: "The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions."
canonical: "https://motherduck.com/glossary/in-operator/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "CREATE INDEX | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-index/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# 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.

```sql
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);
```

<glossary-callout guide="duckdb-cheatsheet-full" />

## 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.

```sql
-- 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 `NULL`s 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.