Summary: In this tutorial, you will learn how to select a result set which their values match any one of a list of values by using SQL IN operator.
SQL IN Operator Syntax
The SQL IN operator allows you to select values that match any one of a list of values. The usage of the SQL IN operator is listed as follows:
3 |
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:
1 |
SELECT officeCode, city, phone |
3 |
WHERE country = 'USA' OR country = 'France' |
In this case, we can use SQL IN instead of the above query:
1 |
SELECT officeCode, city, phone |
3 |
WHERE country IN ('USA','France') |
+------------+--------+-----------------+
| 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:
1 |
SELECT officeCode, city, phone |
3 |
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:
4 |
HAVING SUM (quantityOrdered * priceEach) > 60000 |
Second you use the sub-query with SQL IN as follows:
1 |
SELECT orderNumber,customerNumber,status,shippedDate |
7 |
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 |
+-------------+----------------+---------+---------------------+
Related posts:
- Using MySQL SELECT Statement to Query Data
- Change dropdown list (options) values from database with ajax and php
- Managing Database Index in MySQL
- MySQL Data Types
