IN(), NOT IN()

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

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.

IN(), NOT IN()

On this page

Multi-column IN List Predicate

Was this article helpful?

On this page

Was this article helpful?