Selecting Data with SQL IN

SQL IN allows us to select values which match any one of a list of values. The use of SQL IN is as follows:

SELECT column_list
FROM table_name
WHERE column IN ("list_item1","list_item2"…)

The column in WHERE clause does not need to be in column_list you select, but it has to be a column in the table table_name. If the list has more than one value, each item has to be separated by a comma.
In addition, we can use NOT operator with SQL IN to get values which does not match any value in a list of value.

Let’s practice with several examples of SQL IN.

Suppose if we want to find out all offices which locate in US and France, we can perform the following query:

SELECT officeCode, city, phone
FROM offices
WHERE country = 'USA'  OR country = 'France'

In this case, we can use SQL IN instead of the above query:

SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA','France')

Here is the output

+------------+--------+-----------------+
| officeCode | city   | phone           |
+------------+--------+-----------------+
| 2          | Boston | +1 215 837 0825 |
| 3          | NYC    | +1 212 555 3000 |
| 4          | Paris  | +33 14 723 5555 |
| 8          | Boston | +1 215 837 0825 |
+------------+--------+-----------------+

To get all country which does not locate in USA and France, we can use NOT IN in where clause as follows:

SELECT officeCode, city, phone
FROM offices
WHERE country NOT IN ('USA','France')

Here is the output of offices which does not in USA and France

+------------+--------+------------------+
| officeCode | city   | phone            |
+------------+--------+------------------+
| 5          | Tokyo  | +81 33 224 5000  |
| 6          | Sydney | +61 2 9264 2451  |
| 7          | London | +44 20 7877 2041 |
+------------+--------+------------------+

SQL IN is used most often in subquery. For example, if we want to find out all orders in orders table which has total cost greater than 60000, we can use SQL IN with subquery.

First to select all order which has total cost greater than 60000$, we can retrieve it from orderDetails table as follows:

SELECT orderNumber
FROM orderDetails
GROUP BY orderNumber
HAVING SUM (quantityOrdered * priceEach) > 60000

Second we use subquery with SQL IN as follows:

SELECT orderNumber,customerNumber,status,shippedDate
FROM orders
WHERE orderNumber IN (
    SELECT  orderNumber
    FROM  orderDetails
    GROUP BY  orderNumber
    HAVING  SUM(quantityOrdered * priceEach) > 60000

)

We get all orders which has total cost greater than 60000$

+-------------+----------------+---------+---------------------+
| orderNumber | customerNumber | status  | shippedDate         |
+-------------+----------------+---------+---------------------+
|       10165 |            148 | Shipped | 2003-12-26 00:00:00 |
|       10287 |            298 | Shipped | 2004-09-01 00:00:00 |
|       10310 |            259 | Shipped | 2004-10-18 00:00:00 |
+-------------+----------------+---------+---------------------+