Problem
I am just learning SQL and need to know about the IN operator. What does it do? When and how do I use it?
Solution
This SQL tutorial will cover the IN operator in detail. It will explain how the feature works and explain the use cases. It will show several examples of how to use an IN operator.
All of the demos in this tip will use the WideWorldImporters sample database which can be downloaded for free from Github.
SQL IN Syntax
The Microsoft SQL Server IN operator is used to replace a group of arguments using the = operator that are combined with an OR in for SELECT, UPDATE or DELETE statement. It can make code easier to read and understand. Generally, it will not change performance characteristics.
Consider this SELECT statement:
SELECT * FROM Sales.Invoices
WHERE LastEditedBy = 11
OR LastEditedBy = 17
OR LastEditedBy = 13;
This isn’t too hard to comprehend, but it could get much harder to read if another column was added to the WHERE clause as in this version of the following SQL statement:
SELECT * FROM Sales.Invoices
WHERE(LastEditedBy = 11
OR LastEditedBy = 17
OR LastEditedBy = 13)
AND CustomerID = 77;
Now parentheses must be added to group the LastEditBy arguments away from the CustomerID argument.
A much easier way to accomplish this would be with the SQL IN operator.
The IN operator works by comparing one value, usually a column, to a comma-separated list of potential match values held in a set of parentheses. The value to the left of the IN operator is then compared, one at a time, to entire list and an exact match to any one member of the list will cause the argument to evaluate as true. This can be hard to understand, but should make more sense after seeing several examples.
The following query converts the 3 “LastEditedBy =” arguments using a single IN operator instead.
SELECT * FROM Sales.Invoices
WHERE LastEditedBy IN(11,17,13)
AND CustomerID = 77;
This version of the query is functionally equivalent to the prior version, but is shorter and easier to read.
Rules and Best Practices
The IN operator can only replace the = logical operator. It cannot replace <, >, <=, >=, BETWEEN, or SQL LIKE. It will only find exact matches.
Duplicate values in the list are ignored. This means that
WHERE LastEditedBy IN(11,17,13)
is the same as
WHERE LastEditedBy IN(11,17,13,11,17)
The IN operator can be used anywhere any other operator is used including the WHERE clauses, HAVING clauses, IF statements, or join predicates – although they should be extremely rare in SQL join predicates.
The list of values can have as few as one item with no defined maximum limit, although extremely large lists are generally frowned upon. As a rule, this author doesn’t use IN statements with more than 10 or 20 list items. Read more about alternatives to the IN operator and how to test their effectiveness.
Finally, the list of values doesn’t have to be a hard-coded, comma-separated list. It can be defined by a query. When this happens, it opens up the possibility that the list has zero values and the argument will evaluate to false for every row. This option will be covered in detail later in the tip.
IN with Strings
The IN operator can be used to compare a string column (char, nchar, varchar, nvarchar) to a list of strings. The SQL commands below shows 2 functionally equivalent queries - one that uses multiple arguments and a second that uses the IN operator.
--OR Version
SELECT *
FROM [Application].People
WHERE PreferredName = N'Kerstin'
OR PreferredName = N'Kayla';
--IN Version
SELECT *
FROM [Application].People
WHERE PreferredName IN(N'Kerstin', N'Kayla');
The quotes in the list items are necessary because the data types are of the string variety. Without quotes SQL Server would look for 2 columns named “Kerstin” and “Kayla” and would not find them.
The N preceding the quotes tells SQL Server to treat the hard coded value as Unicode. This was done because the data type for the column PreferredName is a NVARCHAR as opposed to VARCHAR. Without the N’s SQL Server would implicitly convert the strings to Unicode and there would be no change in functionality.
IN with Numbers
This next set of example SQL SELECT queries is looking for account persons who have made exactly 6, 8, or 9 sales. Since lucky number 7 is excluded a BETWEEN operator won’t work. It also puts the IN operator in a HAVING clause which works exactly the same way as the prior WHERE examples as shown in this SQL query:
--OR Version
SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices
GROUP BY AccountsPersonID
HAVING COUNT(*) = 6
OR COUNT(*) = 8
OR COUNT(*) = 9;
--IN Version
SELECT AccountsPersonID, COUNT(*) TotalInvoices
FROM Sales.Invoices
GROUP BY AccountsPersonID
HAVING COUNT(*) IN(6,8,9);
Since the result of the COUNT function is a number there are no quotes in the value list. This would be true for any column with a numeric data type as well.
IN with Dates
It is a pretty rare use case, but the IN operator can be used to search for a match in a list of date or datetime values. This example will put an IN operator in an IF statement and compare dates.
Imagine a scenario where the WideWorldImporters store offered to pay a small bonus to employees that worked a holiday during the calendar year 2013. A stored procedure with code that looks like this may end up being written.
DECLARE @InvoiceDate DATE;
SELECT @InvoiceDate = InvoiceDate
FROM Sales.Invoices
WHERE InvoiceID = 18463;
--OR Version
IF @InvoiceDate = '25-Dec-2013' OR @InvoiceDate = '4-Jul-2013' OR @InvoiceDate = '28-Nov-2013'
BEGIN
PRINT 'Pay a holiday bonus';
END;
--IN Version
IF @InvoiceDate IN('25-Dec-2013', '4-Jul-2013', '28-Nov-2013')
BEGIN
PRINT 'Pay a holiday bonus';
END;
There are several ways to handle entering date and datetime values, but the most common method is to use quotes and type in a string that can be easily converted to a date or datetime.
IN with Columns
This is another rare use case, but it is valid nonetheless. All examples thus far have shown one variable or column being compared to a list of hard coded values, but it can be the other way around.
Suppose the WideWorldImporters store recently lost an employee and they want to know every invoice for which that employee had a part. They want to know if that employee was the salesperson, packer, or had any other role associated to them. The query might look something like this.
--OR Version
SELECT *
FROM Sales.Invoices
WHERE ContactPersonID = 3029
OR AccountsPersonID = 3029
OR SalespersonPersonID = 3029
OR PackedByPersonID = 3029;
That query could be rewritten with 3029 on the left and the list of columns after IN.
--IN Version
SELECT *
FROM Sales.Invoices
WHERE 3029 IN(ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID);
IN with Subqueries
All examples to this point have shown a hard coded list following the IN operator. A very common use case for IN is for the list to be generated during query execution by another TSQL query.
There are 2 important things to remember when building a subquery that will be placed in the parentheses of an IN operator. First, it must return exactly 1 column. An error will be raised if any other number of columns are selected. Second, the query must be able to be run by itself – meaning it has no outside dependencies.
Consider a scenario where a box was found on the floor of the WideWorldImporters warehouse on the second of December. The packing label is smeared, but the letter “L” can be made out as the first letter of the person who packed the box. The box has 5 dry items in it. Can a query be written to help identify the box?
This query lists the 58 employees that have an L at the start of their name. Notice that the query fits the rules above in that it can be executed and returns exactly one column.
SELECT PersonID FROM [Application].People WHERE FullName LIKE 'L%';
Simply place this query inside the parentheses following the IN operator, but do so without the semi colon. The following query does just that. It will search for an invoice with the date of 2-Dec that has exactly 5 dry items. The final argument adds that the box was packed by one of the people in the list generated by the query above.
SELECT *
FROM Sales.Invoices
WHERE InvoiceDate = '2-Dec-2013'
AND TotalDryItems = 5
AND PackedByPersonID IN (SELECT PersonID FROM [Application].People WHERE FullName LIKE 'L%');
Final Thoughts
Whether building a list of strings, dates, or numbers, the IN operator can be very useful. When using a subquery, the value becomes even more apparent. The IN operator should be a major part of any TSQL writer’s repertoire.
Next Steps
