SQL In Operator

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.

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.

For instance

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.

Dig Deeper:

• SQL Subqueries – The Ultimate Guide
• Using WHERE EXISTS in SQL