SingleStore DB

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.

SELECT * FROM hrRec;
****
+-----------+-----------+----------+--------+
| 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 |
+-----------+-----------+----------+--------+
SELECT * FROM hrRec WHERE City IN('DC','New York');
****
+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+
SELECT * FROM hrRec WHERE City NOT IN('DC','Brooklyn');
****
+-----------+----------+----------+--------+
| FirstName | LastName | City     | Tenure |
+-----------+----------+----------+--------+
| Samantha  | Beck     | New York |     44 |
| Ron       | Fletcher | New York |     23 |
+-----------+----------+----------+--------+