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 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 math or return turn 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
Using IN 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.
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.
SELECT SalesOrderID, RevisionNumber, OrderDate FROM Sales.SalesOrderHeader WHERE SalesOrderID IN (SELECT NULL)
returns zero rows. This is because the IN clause always returns false. Contrast this to EXISTS, which returns TRUE even when the subquery returns NULL.