Export (0) Print
Expand All

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;
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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft