Share via

Subqueries with NOT IN

Subqueries introduced with the keyword NOT IN also return a list of zero or more values.

The following query finds the names of the products that are not finished bicycles.

USE AdventureWorks2008R2;
FROM Production.Product
WHERE ProductSubcategoryID NOT IN
    (SELECT ProductSubcategoryID
     FROM Production.ProductSubcategory
     WHERE Name = 'Mountain Bikes' 
        OR Name = 'Road Bikes'
        OR Name = 'Touring Bikes')

This statement cannot be converted to a join. The analogous not-equal join has a different meaning: It finds the names of products that are in some subcategory that is not a finished bicycle. For information about interpreting the meaning of joins not based on equality, see Joining Three or More Tables.

See Also