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.

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);
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 |
+-----------+----------+----------+--------+

Last modified: February 28, 2023

Was this article helpful?