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;

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

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

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

IN(), NOT IN()

Was this article helpful?

Was this article helpful?