The Left and Right Outer Joins

The INNER JOIN clause retrieves records only when there is a match in both tables. In some cases, you may want to retrieve the values from one table even when there are no matching values in the other joined table. For example, you want to create a join from the Orders table to the Employees table, but you don’t want to exclude orders from the result set simply because there is no corresponding record in the Employees table:

SELECT 
	Orders.*, 
	Employees.LastName
FROM Employees 
RIGHT OUTER JOIN Orders 
ON Employees.EmployeeID = Orders.EmployeeID;

When you specify a left or right outer join, Microsoft Jet retrieves all records from one table even though there are no matches in the other table. In this example, all records from the Orders table are retrieved even when the value of the EmployeeID field is unknown.

Note that the word “OUTER” is optional in Microsoft Jet SQL syntax, because a left or right join implies an outer join.

Whether the join is considered a left or a right join is determined from the order of the tables listed in the join clause. There is no effect on efficiency, or in the actual records returned. The previous query can be rewritten as follows, and the same records would be returned:

SELECT 
	Employees.LastName, 
	Orders.*
FROM Orders 
LEFT JOIN Employees 
ON Orders.EmployeeID = Employees.EmployeeID;

Microsoft Jet treats a right join as if it were a left join with the tables reversed.