LEFT JOIN, RIGHT JOIN Operations

Combines source-table records when used in any FROM clause.

Syntax

FROM table1 [ LEFT | RIGHT ] JOIN table2
ON table1.field1 compopr table2.field2

The LEFT JOIN and RIGHT JOIN operations have these parts:

Part Description
table1, table2 The names of the tables from which records are combined.
field1, field2 The names of the fields that are joined. The fields must be of the same data type (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) and contain the same kind of data, but they do not need to have the same name.
compopr Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."
Remarks

Use a LEFT JOIN operation to create a left outer join (left outer join: An outer join in which all the records from the left side of the LEFT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the right.). Left outer joins include 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.

Use a RIGHT JOIN operation to create a right outer join (right outer join: An outer join in which all the records from the right side of the RIGHT JOIN operation in the query's SQL statement are added to the query's results, even if there are no matching values in the joined field from the table on the left.). Right outer joins include 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.

For example, you could use LEFT JOIN with the Departments (left) and Employees (right) tables to select all departments, including those that have no employees assigned to them. To select all employees, including those who are not assigned to a department, you would use RIGHT JOIN.

The following example shows how you could join the Categories and Products tables on the CategoryID field. The query produces a list of all categories, including those that contain no products:

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

In this example, CategoryID is the joined field, but it is not included in the query results because it is not included in the SELECT statement. To include the joined field, enter the field name in the SELECT statement — in this case, Categories.CategoryID.


 Notes 

To create a query that includes only records in which the data in the joined fields is the same, use an INNER JOIN operation.

  • A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins.
  • You can link multiple ON clauses. See the discussion of clause linking in the INNER JOIN topic to see how this is done.

If you try to join fields containing Memo (Memo data type: In a Microsoft Access database, this is a field data type. Memo fields can contain up to 65,535 characters.) or OLE Object (OLE Object data type: A field data type that you use for objects created in other applications that can be linked or embedded (inserted) in an Access database.) data, an error occurs.

 
 
Applies to:
Access 2010, Access 2007