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.