The IN operator in SQL server is a powerful tool that allows you to search for multiple values in a single query. It is used to filter data based on a set of values that match a certain criteria. In this article, we will explore the different ways the IN operator can be used in SQL Server with examples from the Northwind database.
Simple IN Operator
Let’s say we want to retrieve all the products that have a unit price of either $10, $15, or $20. We can use the following query:
SELECT *
FROM Products
WHERE UnitPrice IN (10, 15, 20)
This query uses the IN operator with a list of values to filter data based on a set of conditions. The UnitPrice column is compared to each value in the list, and the query returns all the rows where the UnitPrice matches any of the values in the list. This query will return all the products that have a unit price of either $10, $15, or $20.
The equivalent query using only the OR operator would look like this:
SELECT *
FROM Products
WHERE UnitPrice = 10 OR UnitPrice = 15 OR UnitPrice = 20
This query is functionally equivalent to the previous example using the IN operator. However, the IN operator can be more concise and easier to read, especially when dealing with longer lists of values.
Another example of using the IN operator. Let’s say we want to retrieve all the orders that have been placed by a set of customers. We can use the IN operator to specify the list of customer IDs we are interested in. The SQL query would look like this:
SELECT *
FROM Orders
WHERE CustomerID IN ('ALFKI', 'ANATR', 'ANTON')
This query will return all the orders that have been placed by the customers with the IDs ‘ALFKI’, ‘ANATR’, and ‘ANTON’.
The IN operator can be used with any data type that can be compared, including text, numbers, and dates.
Using IN Operator with a Subquery
Let’s say we want to retrieve all the orders that have been ordered by customers located in the USA. We can use the following query:
SELECT *
FROM Orders
WHERE CustomerID IN (
SELECT CustomerID
FROM Customers
WHERE Country = 'USA'
)
This query uses the IN operator with a subquery to filter data based on the Country column in the Customers table. The subquery retrieves the CustomerIDs for customers who are located in the USA, and the IN operator then compares the CustomerID column in the Orders table to this list.
This query will return all the orders that were placed by customers located in the USA, based on the Country column in the Customers table.
I prefer to use Common Table Expressions (CTEs) whenever possible for improved readability and maintainability. Below is an example of how to use a CTE to replace the IN operator and subquery in the previous example:
WITH CustomersUSA AS (
SELECT CustomerID
FROM Customers
WHERE Country = 'USA'
)
SELECT *
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM CustomersUSA)
This SQL statement uses a Common Table Expression (CTE) to first create a temporary result set called CustomersUSA, which contains all the CustomerIDs for customers located in the USA. The CTE is defined using the WITH keyword, followed by the table name and the SELECT statement that populates it.
Once the CustomersUSA CTE is defined, it can be used in the main query, which retrieves all columns from the Orders table where the CustomerID is in the CustomerID column of the CustomersUSA CTE. This is achieved using the IN operator and a subquery that retrieves the CustomerIDs from the CustomersUSA CTE.
This query will return the same result as the previous example that uses the IN operator and subquery but using a CTE can make your query more readable, modular, and easier to maintain and debug, especially when dealing with complex subqueries.
Using NOT IN Operator
The NOT IN operator can be used to filter data that does not match a certain criteria. Let’s say we want to retrieve all the products that have not been ordered by customers located in Germany. We can use the following query:
SELECT *
FROM Products
WHERE ProductID NOT IN (
SELECT ProductID
FROM [Order Details]
WHERE OrderID IN (
SELECT OrderID
FROM Orders
WHERE ShipCountry = 'Germany'
)
)
This query will return all the products that have not been ordered in orders shipped to Germany. Since there are multiple subqueries involved, it may be beneficial to use a Common Table Expression (CTE) in this scenario to improve readability and maintainability.
A simpler example. Let’s say we want to retrieve all the products that have not been ordered by any customer. We can use the following query:
SELECT *
FROM Products
WHERE ProductID NOT IN (
SELECT ProductID
FROM [Order Details]
)
This query uses the NOT IN operator with a subquery to filter data based on a negative condition. The subquery retrieves all the product IDs that have been ordered, and the NOT IN operator returns all the products that do not appear in this list.
This query will return all the products that have not been ordered by any customer.
The NOT IN operator is a useful tool for filtering data based on negative conditions. It can be used to retrieve data that does not meet certain criteria, or that has not been associated with certain values or records.
Conclusion
The IN operator in SQL server is a versatile tool that allows you to filter data based on multiple values and criteria. It can be used in simple and complex queries to retrieve the data you need from the database.
Leave a Reply