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 AdventureWorks;
GO
SELECT Name
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

Concepts

Subquery Types

Help and Information

Getting SQL Server 2005 Assistance