SELECT can be used to return information from more than one table. The FROM clause in a SELECT statement can have an expression that identifies one or more tables from which data is retrieved.

The expression can be a single table name, a saved query name, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.

An SQL JOIN operation has these parts:

SELECT columnItems FROM table1 
   [ INNER | LEFT | RIGHT ] JOIN table2 
   ON table1.field1 comparator table2.field2

If you do not use a JOIN clause to perform SQL join operations on multiple tables, the resulting Recordset object will not be updateable.

When joining tables, the columnItems must be specified as

SELECT tableName.fieldName . . .

to avoid errors when two or more fields from different tables use the same column name.

Note that you cannot join fields containing Memo or OLE Object data.

INNER JOIN combines records from two tables whenever there are matching values in a field common to both tables.

SELECT columnItems FROM table1 INNER JOIN table2 
   ON table1.field1 comparator table2.field2

Using Inner Join . . . On Run Sample | View Source

Consider the following:

• you need to display all orders listed in the Order_Details table
• you want to include the ProductName of each order, which is listed in the Products table

Use INNER JOIN to relate the Order_Details and Products tables based ON the ProductID field, and get the ProductName values from the Products table.

SELECT Order_Details.OrderID, Order_Details.ProductID,
   Products.ProductName, Order_Details.Quantity,
   Order_Details.UnitPrice 
   FROM Order_Details INNER JOIN Products 
   ON Order_Details.ProductID=Products.ProductID

You can join any two numeric fields of like types. For example, you can join on AutoNumber and Long fields because they are like types. However, you cannot join Single and Double types of fields.

You can also link several ON clauses in a JOIN statement, using the following syntax:

SELECT columnItems FROM table1 INNER JOIN table2 
   ( ON table1.field1 comparator table2.field1 ) 
   AND ( ON table1.field2 comparator table2.field2 ) 
   OR ( ON table1.field3 comparator table2.field3 )

You can also nest INNER JOIN statements.

LEFT JOIN and RIGHT JOIN also combine records from multiple tables, but in contrast to INNER JOIN, you can create an outer join even if there are no matching values in a field common to the joined tables.

SELECT columnItems FROM table1 [ LEFT | RIGHT ] JOIN table2 
   ON table1.field1 comparator table2.field2

Use a LEFT JOIN operation to create a left outer join, which includes all of the records from the first ( left ) of two tables, even if there are no matching values for records in the second ( right ) table.

Using Left Join . . . On Run Sample | View Source

Consider the following:

• you need to determine the total number of orders for each ProductCode
• the Products table lists each ProductCode, while the quantities ordered for each ProductCode are listed in the Order_Details table

Use LEFT JOIN to link the Products ( left ) and Order_Details ( right ) tables ON ProductID as shown below.

SELECT DISTINCT ProductType, ProductCode, 
   Sum( Order_Details.Quantity ) AS SumOfQuantity, 
   ProductName, Products.UnitPrice 
   FROM Products LEFT JOIN Order_Details 
   ON Products.ProductID=Order_Details.ProductID 
   GROUP BY ProductType, ProductCode, ProductName,
      Products.UnitPrice 
   ORDER BY ProductType, Sum( Order_Details.Quantity ) DESC,
      ProductCode

Use a RIGHT JOIN operation to create a right outer join, which includes all of the records from the second ( right ) of two tables, even if there are no matching values for records in the first ( left ) table.

Using Right Join . . . On Run Sample | View Source

Consider the following:

• you need to determine the Total Sales by employee, sorted on Total Sales from highest to lowest
• the employees are listed in the Employees table
• the orders by each employee are listed in the Orders table
• the details of each order are listed in the Order_Details table

The example at right shows use of a RIGHT JOIN nested inside an INNER JOIN. The RIGHT JOIN relates the Employees and Orders tables ON EmployeeID, while the INNER JOIN relates these joined tables with the Order_Details table based ON OrderID.

SELECT DISTINCT LastName, FirstName, Title, 
   Sum( Quantity ) AS [Total Units], 
   Sum( UnitPrice*Quantity ) AS [Total Sales] 
   FROM ( Employees RIGHT JOIN Orders 
      ON Employees.EmployeeID=Orders.EmployeeID ) 
   INNER JOIN Order_Details
      ON Orders.OrderID=Order_Details.OrderID 
   GROUP BY LastName, FirstName, Title 
   ORDER BY Sum( UnitPrice*Quantity ) DESC

A LEFT JOIN or a RIGHT JOIN may be nested inside an INNER JOIN, but an INNER JOIN may not be nested inside a LEFT JOIN or a RIGHT JOIN.