LEFT JOIN, RIGHT JOIN Operations

Description

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 and contain the same kind of data, but they don't 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 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 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 aren't 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 isn't included in the query results because it isn't 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 or OLE Object data, an error occurs.

See Also

FROM clause, INNER JOIN operation, UNION operation.

Example

This example assumes the existence of hypothetical Department Name and Department ID fields in an Employees table. Note that these fields do not actually exist in the Northwind database Employees table.

This example selects all departments, including those without employees.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub LeftRightJoinX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
    
    ' Select all departments, including those
    ' without employees.
    Set rst = dbs.OpenRecordset _
        ("SELECT [Department Name], " _
        & "FirstName & Chr(32) & LastName AS Name " _
        & "FROM Departments LEFT JOIN Employees " _
        & "ON Departments.[Department ID] = " _
        & "Employees.[Department ID] " _
        & "ORDER BY [Department Name];")

    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 20

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example selects all customers, including those without orders:

SELECT Customers.CustomerID, CompanyName, OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY OrderID;

The next example selects all suppliers, including those without products. Before you try this example, run the following query to add a record to the Suppliers table:

INSERT INTO Suppliers (CompanyName, ContactName, ContactTitle)
VALUES ('Acme Supply Co.', 'That Coyote', 'Sales Manager');

Next create a new query, paste the following SQL statement into the SQL window, and run the query.

SELECT Suppliers.SupplierID, Suppliers.CompanyName,
Products.ProductID, Products.ProductName
FROM Products RIGHT JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
ORDER BY Products.SupplierID;

To delete the additional record you created in the Suppliers table, you can run the following query:

DELETE * FROM Suppliers
WHERE CompanyName = 'Acme Supply Co.';