The INNER JOIN

The SQL query produced by the query design grid in Microsoft Access uses the INNER JOIN clause to join two tables. Here is the same query as produced by Microsoft Access:

SELECT 
	Products.ProductID, 
	Products.ProductName, 
	Categories.CategoryID, 
	Categories.CategoryName
FROM Categories 
INNER JOIN Products 
ON Categories.CategoryID = Products.CategoryID;

The INNER JOIN names the linking criterion used to find matches between the two tables. This is the preferred format for specifying joins with Microsoft Jet, for two reasons. First, this format makes the join criteria explicit, rather than inferring it from the condition in the WHERE clause. Second, Microsoft Jet requires the use of this format in order for the results of this join to be updatable.

See Also For a full discussion of query updatability, see Chapter 5, “Working with Records and Fields.”

Many people also prefer this format because it’s self-documenting, unlike the implicit join specified in the WHERE clause. The WHERE clause is then reserved for selection criteria, rather than doing dual-duty as a join specifier:

SELECT 
	Products.ProductID, 
	Products.ProductName, 
	Categories.CategoryID, 
	Categories.CategoryName
FROM Categories 
INNER JOIN Products 
ON Categories.CategoryID = Products.CategoryID
WHERE Products.CategoryID IN (1,3);

A join can specify more than one linking condition. The NorthwindTables sample database contains the saved query PreferredSuppliers, which includes all suppliers that provide a product that has sold more than $10,000 worth of inventory. The following query is based on the PreferredSuppliers query and the Products table:

SELECT 
	PreferredSuppliers.ProductID, 
	PreferredSuppliers.SupplierID, 
	Products.CategoryID, 
	Products.ProductName
FROM PreferredSuppliers 
INNER JOIN Products 
ON (PreferredSuppliers.SupplierID = Products.SupplierID) 
AND (PreferredSuppliers.ProductID = Products.ProductID);

Complex join conditions can be specified. The following example joins six tables:

SELECT 
	Orders.OrderID, 
	Employees.EmployeeID, 
	Customers.CompanyName, 
	[Order Details].ProductID, 
	Products.ProductName, 
	Categories.CategoryName
FROM 
	Categories INNER JOIN 
	 (Products INNER JOIN 
		(Employees INNER JOIN 
		 (Customers INNER JOIN 
			(Orders INNER JOIN [Order Details] 
			 ON Orders.OrderID = [Order Details].OrderID) 
			ON Customers.CustomerID = Orders.CustomerID) 
		 ON Employees.EmployeeID = Orders.EmployeeID) 
		ON Products.ProductID = [Order Details].ProductID) 
	 ON Categories.CategoryID = Products.CategoryID;