Outer Joins and Null Values

Microsoft Jet returns the special value Null in all fields for the records in the “outer” table in which no match is found. You can take advantage of this feature by testing for the presence of Null values in the outer table. For example, the NorthwindTables database contains a query named SuppliersInUSA, which returns records for those suppliers located in the United States. Suppose you want to find all the products in the Products table whose suppliers aren’t in the result set for the SuppliersInUSA query. One way is to create a nested sub-SELECT query:

SELECT Products.* 
FROM Products
WHERE Products.SupplierID NOT IN
	(SELECT SupplierID FROM SuppliersInUSA;);

This query creates a list of products from suppliers who aren’t located in the United States.

Another way to achieve the same result makes use of the fact that nonmatching records are Null. The following example creates a left join between Products and SuppliersInUSA, and then tests for a Null value in the SupplierID field in SuppliersInUSA. SupplierID is the primary key of the Suppliers table and can’t normally be Null, either in the Suppliers table or in the SuppliersInUSA query. Therefore, the presence of a Null value in the result set of the left join implies that there is no join between Products and SuppliersInUSA, which is the condition that should be detected:

SELECT Products.*
FROM Products
LEFT JOIN SuppliersInUSA
ON Products.SupplierID = SuppliersInUSA.SupplierID
WHERE SuppliersInUSA.SupplierID Is Null;