# IN

IN tests whether a value occurs in a list or query result. `NOT IN` negates the result using SQL three-valued logic.

## Syntax

```text
expression [NOT] IN (value, ...)
expression [NOT] IN (SELECT ...)
expression [NOT] IN [schema.]table
```

The left side can be a scalar or a row value. Its field count must match the right side. Table shorthand is equivalent to `IN (SELECT * FROM table)` and therefore uses every column of that table.

## NULL and empty input

A matching value makes `IN` true. If no value matches and the right side contains `NULL`, the result can be `NULL` instead of false. A `NULL` left operand also generally gives `NULL` for a nonempty right side.

An empty right side makes `IN` false and `NOT IN` true, including when the left side is `NULL`. Use `EXISTS` for a correlated presence test when the selected values themselves are irrelevant.

## Examples

### Lists and missing values

```sql
SELECT 2 IN (1, 2, 3),
       4 IN (1, NULL),
       4 NOT IN (1, 2, 3)
FORMAT csv;
```

```text
1,\N,1
```

### Table shorthand

```sql
CREATE TABLE allowed(value INTEGER);
INSERT INTO allowed VALUES (1), (2);
```

```sql
SELECT 2 IN allowed, 3 NOT IN main.allowed
FORMAT csv;
```

```text
1,1
```

### An empty result

```sql
SELECT NULL IN (SELECT value FROM allowed WHERE 0),
       NULL NOT IN (SELECT value FROM allowed WHERE 0)
FORMAT csv;
```

```text
0,1
```

See [`WHERE`](https://coordinatordb.com/docs/reference/statements/select/where) for filtering rows with membership predicates.