Subqueries with NOT EXISTS
SQL Server 2008 R2
NOT EXISTS works like EXISTS, except the WHERE clause in which it is used is satisfied if no rows are returned by the subquery.
For example, to find the names of products that are not in the wheels subcategory:
USE AdventureWorks2008R2; GO SELECT Name FROM Production.Product WHERE NOT EXISTS (SELECT * FROM Production.ProductSubcategory WHERE ProductSubcategoryID = Production.Product.ProductSubcategoryID AND Name = 'Wheels')