SQL Server IN

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the IN operator which is an elegant alternative to the OR operator.

What is IN operator in SQL Server?

The SQL Server IN operator is a logical operator which does away with the need to use OR multiple times in a query by allowing us to specify multiple values for evaluation at the same time. It is a shorthand for multiple OR conditions.

Operation

The SQL Server IN operator follows the WHERE clause in a SQL query and allows us to specify a list of values or a subquery returning a list of values. If one or more value matches the condition specified in the expression the consequent action (which might be a SELECT, UPDATE or DELETE) follows. A variation of IN is NOT IN which evaluates against a set of specified values and performs the consequent action (which might be a SELECT, UPDATE or DELETE) if any one of the values do not meet the condition specified in the expression. A SQL statement can contain one or more IN operators.

SQL Server IN Syntax

The basic syntax of SQL Server IN operator is as follows.

SELECT expressions 
FROM tables 
WHERE [condition IN | NOT IN (value1, value2, …, valueN | subquery)];

In this syntax,

• expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
• tables – one or more than one table from where you want to retrieve data.
• WHERE conditions –   This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
• IN – Logical operator used to evaluate a list of values and perform consequent action if one or more values match the expression.
• NOT IN– Logical operator used to evaluate a list of values and perform consequent action if one or more values do not match the expression.
• VALUES – The exact values (which may be numeric or string) specified against which to evaluate.
• SUBQUERY– Optional. A SELECT subquery can be used to fetch values from the same or another table for evaluation instead of specifying the values explicitly.

SQL Server IN operator Examples

Let us see how it works starting from the simple (i.e. explicit value list scenario) to the complex (i.e. SELECT subquery scenario). NOTE: Columns are also referred to as fields or attributes and the terms are used interchangeably. Suppose we have a database for a bookstore with a table called books containing the list of available books and a table called categories which categorizes the books as per their genre.

category_id	broad_category	sub_category	category_stock
C1009	Fiction	Horror	229
C1122	Non-Fiction	Essay	157
C1195	Fiction	General	215
C1255	Fiction	Romance	155
C1744	Non-Fiction	Management	115

 

book_id	book_name	category_id	list_price	book_stock
B1010	A History of India’s Geography	C1122	149	7
B1011	A Short History of the World	C1122	249	7
B1050	A tale of two cities	C1195	199	5
B1199	Dracula	C1009	199	10
B1335	Organizational Behavior	C1744	219	22
B1519	Classic Ghost Stories	C1009	250	9
B1555	Effective Time Management	C1744	199	10
B1563	The Invisible Man	C1195	149	31
B1775	Romeo and Juliet	C1255	137	15

1)  SQL Server IN example– explicit value list example

The below example shows the most traditional use of the SQL Server IN operator on the books table where the SELECT query retrieves the list of books whose category matches the category_id specified in the query.

SELECT * 
FROM books 
WHERE category_id IN (‘C1122’, ‘C1009’, ‘C1195’);

The above query will generate the following output.

book_id	book_name	category_id	list_price	book_stock
B1010	A History of Indias Geography	C1122	149	7
B1011	A Short History of the World	C1122	249	7
B1050	A tale of two cities	C1195	199	5
B1199	Dracula	C1009	199	10
B1519	Classic Ghost Stories	C1009	250	9
B1563	The Invisible Man	C1195	149	31

2)  SQL Server IN example – with SELECT subquery instead of explicit value list

The below is a traditional example of a subquery scenario with the IN operator. The query returns the list of those books from the books table whose category id corresponds to the broad category FICTION in the categories table. Here the inner query (i.e. subquery) is executed first and fetches the category_id corresponding to broad_category FICTION and then the outer query executes using the category_id from the inner query (i.e. subquery) to filter out and present the matching values.

select * 
from books 
where category_id in (select category_id from categories where broad_category='Fiction');

The above query will generate the following output.

book_id	book_name	category_id	list_price	book_stock
B1050	A tale of two cities	C1195	199	5
B1199	Dracula	C1009	199	10
B1519	Classic Ghost Stories	C1009	250	9
B1563	The Invisible Man	C1195	149	31
B1775	Romeo and Juliet	C1255	137	15

3)  SQL Server NOT IN – evaluating for NOT TRUE against a list of values

The below example shows a traditional use of SQL Server NOT IN on the books table where the SELECT query returns the list of books whose list prices are not what is specified in the query.

SELECT * 
FROM books 
WHERE list_price NOT IN (149, '199');

The above query will generate the following output.

book_id	book_name	category_id	list_price	book_stock
B1011	A Short History of the World	C1122	249	7
B1335	Organizational Behavior	C1744	219	22
B1519	Classic Ghost Stories	C1009	250	9
B1775	Romeo and Juliet	C1255	137	15

4)  SQL Server IN example – update query example

The following is an example of the usage of IN operator in an update query on the books table which updates the list price for books whose list price is the same as what is specified in the query.

UPDATE books 
SET list_price=200 
WHERE list_price IN (149, 199);

After running above query if we run a SELECT query on the table, we will get the below output which shows that old list prices specified in the query have been updated to list price 200.

book_id	book_name	category_id	list_price	book_stock
B1010	A History of India’s Geography	C1122	200	7
B1011	A Short History of the World	C1122	249	7
B1050	A tale of two cities	C1195	200	5
B1199	Dracula	C1009	200	10
B1335	Organizational Behavior	C1744	219	22
B1519	Classic Ghost Stories	C1009	250	9
B1555	Effective Time Management	C1744	200	10
B1563	The Invisible Man	C1195	200	31
B1775	Romeo and Juliet	C1255	137	15

5)  SQL Server IN – DELETE query example

The following is an example of the usage of IN operator in a DELETE query on the books table which deletes records from the table where the number of copies available for a book is what is specified in the query.

DELETE 
FROM books 
WHERE book_stock IN (7, 5, 9);

After running above query if we run a SELECT query on the table, we will get the below output which shows that records for books whose number is less than 10 has been deleted.

book_id	book_name	category_id	list_price	book_stock
B1199	Dracula	C1009	200	10
B1335	Organizational Behavior	C1744	219	22
B1555	Effective Time Management	C1744	200	10
B1563	The Invisible Man	C1195	200	31
B1775	Romeo and Juliet	C1255	137	15