The SQL In operator compare a column to a list. In this article we’ll see how to use In and NOT IN with list generated using subqueries.
Table of contents
SQL IN Operator and NOT IN Operator Review
The SQL IN operator is considered a membership type. The membership type allows you to conduct multiple match tests compactly in one statement. For instance, consider if you have a couple spelling variations for the leader of the company such as ‘Owner’, ‘President’, and ‘CEO.’ In case like this you could use the in operator to find all matches
ContactTitle IN ('CEO', 'Owner', 'President')
The above will match or return if the contact title is either ‘CEO’, ‘Owner’, or ‘President.’ To use the SQL IN comparison operator separate the items you which to test for with commas and be sure to enclose them in parenthesis. The full SQL statement for our example is
SELECT CompanyName, ContactName, ContactTitle
FROM Customers
WHERE ContactTitle IN ('CEO', 'Owner', 'President');
Note: The above query isn't meant for
the adventure works database
Learn more: Read more about conditions in SQL WHERE – Guide and Examples
Using the SQL IN Operator with a Subquery
When used with subqueries, the list of values is replaced with a subquery. The advantage of using a subquery, in this case, is that it helps to make your queries more data-driven and less brittle.
What I mean is you don’t have to hard code values within the SQL IN operator!
If for instance you’re doing a query to find sales order by top salespeople, the non-sub query way to use the IN statement is
SELECT SalesOrderID, OrderDate, AccountNumber, CustomerID, SalesPersonID, TotalDue FROM Sales.SalesOrderHeader WHERE SalesPersonID IN (279, 286, 289) whose results are
But now since we know about subqueries, we can use the following to obtain the same list
SELECT SalesOrderID, OrderDate, AccountNumber, CustomerID, SalesPersonID, TotalDue FROM Sales.SalesOrderHeader WHERE SalesPersonID IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE Bonus > 5000)
The advantage is that as salespersons sell more or less, the list of salesperson ID’s returned adjusts.
Just like with other queries you can create a correlated subquery to be used with the IN clause.
It returns all sales orders written by salespeople with sales year to date greater than three million dollars, but now we use the SQL IN operator:
SELECT SalesOrderID,
RevisionNumber,
OrderDate
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IN (SELECT SalesPerson.BusinessEntityID
FROM sales.SalesPerson
WHERE SalesYTD > 3000000
AND SalesOrderHeader.SalesPersonID
= Sales.SalesPerson.BusinessEntityID)
As IN returns TRUE if the tested value is found in the comparison list, NOT IN returns TRUE if the tested value is not found. Taking the same query from above, we can find all Sales orders that were written by salespeople that didn’t write 3,000,000 in year-to-date sales, we can write the following query:
SELECT SalesOrderID,
RevisionNumber,
OrderDate
FROM Sales.SalesOrderHeader
WHERE SalesPersonID NOT IN (SELECT SalesPerson.BusinessEntityID
FROM sales.SalesPerson
WHERE SalesYTD > 3000000
AND SalesOrderHeader.SalesPersonID
= Sales.SalesPerson.BusinessEntityID)
WHAT happens to NULL with IN?
When the comparison list only contains the NULL value, then any value compared to that list returns false.
For instance
SELECT SalesOrderID, RevisionNumber, OrderDate FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (SELECT NULL)
returns zero rows. This is because the SQL IN operator always returns false. Contrast this to EXISTS, which returns TRUE even when the subquery returns NULL.
Leave a Reply