SQL Basics | DML: Data Manipulation Language | IN, NOT IN
SQL uses the IN operator in two situations. This page explains one of them: the situation related to WHERE. For this usage, you must know at least one required value. All known values are then entered in the IN clause.
IN, NOT IN syntax
The IN clause is as follows.
SELECT "field_name"
FROM "table_name"
WHERE "field_name" IN ('value1', 'value2', ...);
There is one or more value inside the parentheses, and the values are separated by commas. A value can be a number or a string. If there is only one value inside the parentheses, it is equivalent to the following.
WHERE "field_name" = 'value1'
To query the opposite of the IN clause, use NOT IN.
SELECT "field_name"
FROM "table_name"
WHERE "field_name" NOT IN ('value1', 'value2', ...);
IN example
For example, suppose you want to retrieve all data that includes Los Angeles or San Diego from the store_information table.
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
Enter the following command.
SELECT *
FROM store_information
WHERE store_name IN ('Los Angeles', 'San Diego');
The result is as follows.
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
NOT IN example
Conversely, to retrieve all data excluding Los Angeles and San Diego, use the following.
SELECT *
FROM store_information
WHERE store_name NOT IN ('Los Angeles', 'San Diego');
The result is as follows.
| store_name | sales | txn_date |
|---|---|---|
| Boston | 700 | Jan-08-2018 |