# IN(), NOT IN()

The `IN()` and `NOT IN()` functions filter the results after checking if the values are in or not in the list of parameters specified.

The maximum number of parameters that can be specified within the `IN()` and `NOT IN()` functions is `1048576` (this limit applies to the number of parameters, not the size of parameters). The following example demonstrates the use of `IN()` and `NOT IN()` functions.

```sql
CREATE TABLE hrRec (FirstName varchar(30), LastName varchar(30), City varchar(50), Tenure int);

INSERT INTO hrRec VALUES 
  ('Adam', 'Gale', 'Brooklyn', 40),
  ('Smantha', 'Beck', 'New York', 44),
  ('Clara', 'Wakefield', 'DC', 24),
  ('Todd', 'Bridges', 'DC', 30),
  ('Ron', 'Fletcher', 'New York', 23);

```

```sql
SELECT * FROM hrRec;

```

```output

+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Adam      | Gale      | Brooklyn |     40 |
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+

```

```sql
SELECT * FROM hrRec WHERE City IN('DC','New York');

```

```output

+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+

```

```sql
SELECT * FROM hrRec WHERE City NOT IN('DC','Brooklyn');

```

```output

+-----------+----------+----------+--------+
| FirstName | LastName | City     | Tenure |
+-----------+----------+----------+--------+
| Samantha  | Beck     | New York |     44 |
| Ron       | Fletcher | New York |     23 |
+-----------+----------+----------+--------+

```

## Multi-column IN List Predicate

You can simultaneously compare multiple columns using the `IN` list in the `WHERE` clause.

For example:

```
SELECT * FROM t1 WHERE (column1,column2) IN ((1,1));
```

is semantically equivalent to

```
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
```

And

```
SELECT * FROM t1 WHERE (c1, c2) IN (('b', 2), ('a', 3));
```

is semantically equivalent to

```
SELECT * FROM t1 WHERE (c1 = 'b' AND c2 = 2) OR (c1 = 'a' AND c2 = 3);
```

Remarks:

* The maximum supported nested depth of the `IN` list is 1.
* The right-hand side list can include only literals. For example: `WHERE (c1, c2) IN (('b', (2,3)), ('a', (4,5))` results in an error.
* The right-hand side list must be parameterizable, i.e. the corresponding entry of each inner list must have the same data type.
* There is no limit to the number of columns that can be specified within the `IN` list.

***

Modified at: November 13, 2024

Source: [/db/v9.1/reference/sql-reference/comparison-operators-and-functions/in-not-in/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/comparison-operators-and-functions/in-not-in/)

(An index of the documentation is available at /llms.txt)