Selecting Data with SQL IN

SQL IN allows you to select values which match any one of a list of values. The usage 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 selected, 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, you 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  you want to find out all offices which are located in US and France, you 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 countries which does are not located in USA and France, we can use NOT IN in the 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 sub-query. For example, if you want to find out all orders in the orders table which have total cost greater than $60000, we can use SQL IN with sub-query.
First to select all the orders which has total cost greater than $60000, you can retrieve it from orderDetails table as follows:

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

Second you use the sub-query 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

)
You get all the orders which have 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 |
+-------------+----------------+---------+---------------------+