By: Aubrey Love | Updated: 2023-10-16 | Comments | Related: > TSQL
Problem
Are you tired of manually searching through large Microsoft SQL Server databases for specific information? In my early years as a junior SQL Server DBA, one of the things I struggled with was filtering data when writing queries. I was told many times not to use SELECT * to return all columns and rows in a production database.
I know I only want specific columns and rows returned. Sure, it's easy to list the columns I want, but what about filtering the rows? More specifically, I want to filter the table based on several values, not just one.
Solution
Look no further than the SQL WHERE IN clause! This powerful tool allows you to easily filter through vast amounts of data and retrieve only the results that meet your criteria. Whether you're a seasoned database expert or just starting out, learning to use the WHERE IN clause effectively will make your life easier and increase efficiency. In this SQL tutorial, we'll explore the WHERE IN clause and show you how to use it in your SQL queries. So, grab a cup of coffee, and let's dive in!
Prerequisite
To complete the examples in this tip, download and install (restore) the AdventureWorks2019 sample database.
SQL WHERE IN Clause with an Array
Often, you will need to retrieve data based on two or more values. In this section, we will show, by example, how to filter based on the values in an "array or expression list."
On a side note: I used the term "array" throughout this article, along with the term "expression list." In the programming world, SQL's expression list is often called an array. Either term will work just fine. Both objects perform the same task. That is, to hold a range or collection of data.
The SQL WHERE IN clause is a powerful tool that allows you to specify multiple values in a WHERE clause. This can be very useful when you want to find records in a database that have a specific value in one column and another value in another column or table.
The SQL WHERE IN clause is used to specify a list of values in a SELECT, INSERT, UPDATE, or DELETE statement. The clause is used to help narrow down results from a query and is generally used in conjunction with other clauses such as WHERE, HAVING, and ORDER BY.
Let's look at our first example, where we return only people with the first name of Ajay, Alvaro, or Yale from the Person.Person table in our SELECT statement. We will do this by putting the specific names we want to search for in an array. Here is the syntax:
USE AdventureWorks2019;
GO
SELECT
firstname,
LastName
FROM Person.Person
WHERE FirstName IN ('Ajay', 'Alvaro', 'Yale')
ORDER BY FirstName;
GO
Results:
It's true that you could get the results by using the OR operator instead of WHERE IN. However, as you grow as a DBA or programmer, you will quickly learn to condense your code to make it more readable and cost-effective. The code block below shows how our sample code from above would look when using the OR operator.
SELECT firstname, LastName FROM Person.Person WHERE FirstName = 'Ajay' OR FirstName = 'Alvaro' OR FirstName = 'Yale' ORDER BY FirstName; GO
As you can see in this second option, where we use the OR operator, we were required to create 11 rows of code versus only nine rows of code when using the SQL WHERE IN clause. And we are not repeating our column name "firstname" repeatedly. Remember, cleaner and more precise code is always the best option.
SQL WHERE NOT IN Clause with an Array
As you might imagine, we can do the opposite of the former example by adding the NOT operator to the SQL query. The following query will return every row in the table where the values of the array are not present.
USE AdventureWorks2019;
GO
SELECT
firstname,
LastName
FROM Person.Person
WHERE FirstName NOT IN ('Ajay', 'Alvaro', 'Yale')
ORDER BY FirstName;
GO
Results: (Partial. In the AdventureWorks2019 sample database, the query should return 19,966 rows)
If you scroll down the list of results, you will notice no entries where the first name contains any of the values in our array.
SQL IN with a Subquery
Retrieving data based on values in an array is a great option, but what if you don't know the values or they are too numerous to list? We can always nest a subquery inside the WHERE IN clause. In the following example, we want to return the first name, last name, and salary of all employees with a salary greater than 250,000.00. At first glance of the question, you may be thinking, I can do that with just a simple INNER JOIN. For the most part, you would be correct, but not entirely. Remember, we want to return all employees whose salary is "greater than" 250,000.00.
Here's our attempt using a simple INNER JOIN approach. (Note: this is a simple INNER JOIN, not a subquery. We will demonstrate the subquery option in the next example.)
SELECT p.firstname, p.lastname, sp.SalesQuota FROM Person.Person AS p INNER JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID WHERE sp.SalesQuota > 250000.00 ORDER BY sp.SalesQuota; GO
Results:
Notice that we had 14 rows returned, 11 of which have a SalesQuota value of 250,000.00. Remember, we only wanted rows where the SalesQuota was "more than" 250,000.00. We can accomplish this more accurately using a nested query inside the WHERE IN clause.
USE AdventureWorks2019;
GO
SELECT p.FirstName, p.LastName, sp.SalesQuota
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
Results:
SQL WHERE NOT IN with a Subquery
As you may have guessed, we can return the opposite values (rows) of our previous query (see section "SQL WHERE IN with a Subquery") by simply adding the NOT operator in the WHERE IN clause. This will return all first names, last names, and SalesQuotas that are less than 300,000.00.
SELECT p.FirstName, p.LastName, sp.SalesQuota
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID NOT IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota < 250000);
GO
Results:
SQL WHERE IN with NULL
SQL also allows us to filter based on an empty value, also known as NULL. As with our samples above, we can replace the 250,000.00 value with NULL to return only those rows where the SalesQuota is NULL.
USE AdventureWorks2019;
GO
SELECT p.FirstName, p.LastName, sp.SalesQuota
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota IS NULL);
GO
Results:
Using SQL WHERE IN on INT and NUMERIC Values
Okay, I see where the WHERE IN option would be great for string values, but what if I need to filter by an INT or Numeric value?
This also is doable in SQL. The AdventureWorks2019 database shows three different INT values assigned to the "EmailPromotion" column. Those values are 0, 1, and 2. In the following example, we will filter out the "EmailPromotion" rows with a 0 (zero) value and return only the rows with a value of 1 or 2.
USE AdventureWorks2019; GO SELECT FirstName, LastName, EmailAddress, EmailPromotion FROM [Sales].[vIndividualCustomer] WHERE EmailPromotion IN (1, 2) GO
Notice the absence of single quote marks around the values 1 and 2? That's because the "EmailPromotion" column in the view "Sales.vIndividualCustomer" only has INT values, not string or character values. So, we should reference them as INT values.
Results: (Partial)
This process can also be used on varchar, numeric, and decimal values.
Using SQL WHERE IN with the GROUP BY and HAVING Clause
Organizing your results with a GROUP BY clause is a relatively simple approach. As in this sample, list your desired group column first. All trailing columns will come second to that column's order.
USE AdventureWorks2019; GO SELECT FirstName, LastName, EmailPromotion FROM [Sales].[vIndividualCustomer] WHERE EmailPromotion IN (1, 2) GROUP BY EmailPromotion, FirstName, LastName
This query returns 8,147 rows. There are 4,662 rows with an email promotion code of 1 and 3,485 rows with an email promotion code of 2. Therefore, the result set shown below is between rows 4,658 and 4,667. This will allow you to see the filtered and grouped results that show the two values in the "EmailPromotion" column. Otherwise, the result image would be very large.
Results: (Partial)
Now, can we throw in the HAVING clause with the SQL SELECT code? Absolutely. In the following sample, working with a modified version of our previous example, we will show how to do this. Let's say we want to return only the rows with an "EmailPromotion" value of 1. We can do this with the HAVING clause. Granted, this is not always the most optimal way of accomplishing this, but it works, and it gives you a basic understanding of how it does the filtering.
USE AdventureWorks2019; GO SELECT FirstName, LastName, EmailPromotion FROM [Sales].[vIndividualCustomer] WHERE EmailPromotion IN (1, 2) GROUP BY EmailPromotion, FirstName, LastName HAVING EmailPromotion IN (1)
Results: (Partial)
This returned a total of 4,662 rows. As you may remember from the previous sample, that's how many rows have an "EmailPromotion" value of 1.
Using SQL WHERE IN with the Update and Delete Commands
For this example, create a new test table in a test database. Next, we will populate this new table with some basic data and update the table using the WHERE IN clause.
Create the table and insert the following data. Remember to use a test database for this.
USE myTestDB;
GO
CREATE TABLE testTable101 (
colID INT IDENTITY,
firstName VARCHAR(20),
jobTitle VARCHAR(20)
);
GO
INSERT INTO testTable101(firstName, jobTitle)
VALUES('John', 'Sales'),
('Sam', 'JuniorSales'),
('Henry', 'SalesManager'),
('David', 'JuniorSales');
GO
SELECT *
FROM testTable101;
GO
Results:
Now, we want to give each of the "JuniorSales" team members a promotion to "Sales" status, and we need to do this using the WHERE IN option.
UPDATE testTable101
SET jobTitle = 'Sales'
WHERE jobTitle IN ('JuniorSales');
GO
SELECT * FROM testTable101;
GO
Results:
As you can see from the results, all the personnel with a job title of "JuniorSales" now have a job title of "Sales." We can also do this with the delete function as well. The following SQL statement will remove the Sales Manager from our table using the WHERE IN clause in a DELETE statement.
DELETE
FROM testTable101
WHERE jobTitle IN ('SalesManager');
GO
SELECT * FROM testTable101;
GO
Results:
Wrap-Up
This article taught that the SQL WHERE IN clause is a versatile way to select data from a database. It can be used to select data based on a list of values or multiple criteria. We also learned that the SQL WHERE IN clause can be used with an array or expression list, or we can add a nested subquery within our SQL WHERE IN clause.
The WHERE IN clause can be used with any type of data, including text, numbers, dates, and even NULL values.
Next Steps
- Check out these tips:
About the author
Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-10-16
