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;