SQL IN operator allows you to determine if a value is contained in a set of values. The syntax of using SQL IN operator is as follows:
SELECT column_list
FROM table_name
WHERE column IN (value1, value2, value3…)
The set of values must be comma-delimited and enclosed within parentheses.
To find all products which have unit price are 18, 19 and 20 you perform the following query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice IN (18, 19, 20)
productName unitPrice
---------------- ---------
Chai 18.0000
Chang 19.0000
Steeleye Stout 18.0000
Inlagd Sill 19.0000
Chartreuse verte 18.0000
Maxilaku 20.0000
Lakkalikööri 18.0000
The query above can be rewritten as following by using OR operator to combine multiple conditions.
SELECT productName, unitPrice
FROM products
WHERE unitPrice = 18 OR unitPrice = 19 OR unitPrice = 20
As you can see, SQL IN operator helps you to reduce the complexity of combining multiple OR conditions therefore it make SQL statement easier to understand and maintain.
To find all records which has a column value are not in a set you can use NOT IN. for instance, you can find all products which have unit price not 18 and not 19 and not 20 by performing the following query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice NOT IN (18, 19, 20)
Here is the excerpt of the result
productName unitPrice
--------------------------------- ---------
Aniseed Syrup 10.0000
Chef Anton's Cajun Seasoning 22.0000
Chef Anton's Gumbo Mix 21.3500
Grandma's Boysenberry Spread 25.0000
Uncle Bob's Organic Dried Pears 30.0000
Northwoods Cranberry Sauce 40.0000
Mishi Kobe Niku 97.0000
Ikura 31.0000
Queso Cabrales 21.0000
Queso Manchego La Pastora 38.0000
Konbu 6.0000
Tofu 23.2500
...
Beside these above usages, SQL IN operator is also used in subquery which you will learn later in SQL subquery tutorial.
In this tutorial, you’ve learn how to use SQL IN operator to find records which has value in a set. Furthermore, you’ve also learnt how to combine NOT operator with SQL IN operator to find all records which has value not in a set.
SQL IN operator, you learn how to use SQL IN to find value in a set of values. SQL IN tutorial with examples. SQL IN to combine with SQL NOT operator.SQL IN tutorial with clear explaination.