LEFT JOIN, RIGHT JOIN Operations (SQL Only)

See AlsosqlLeftRightJoinSee                 ExamplesqlLeftRightJoinExample>Low

Use a LEFT JOIN operation to create a left join1LQIV1I.  Left joins include all of the records from the first (left-hand) of two tables, even if there are no matching values for records in the second (right-hand) table.

Use a RIGHT JOIN operation to create a right join1LQIV1I.  Right joins include all of the records from the second (right-hand) of two tables, even if there are no matching values for records in the first (left-hand) table.

For example, you might 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.

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

LEFT JOIN and RIGHT JOIN are optional but when included are part of the FROM clause.

You use the following syntax to create a left or right join:

          table1 LEFT JOIN table2
          ON table1.field1 = table2.field2

          table1 RIGHT JOIN table2
          ON table1.field1 = table2.field2

The arguments table1 and table2 are the names of the tables you want to join.  The arguments table1.field1 and table2.field2 identify the fields in the tables you want to join on.  The fields must be of the same data type17F82T4 and contain the same kind of data, but they don't have to have the same name.

The following example shows how you might join the Titles and Publishers tables on the PubID field. The query produces a list of all titles, including those that contain no publisher.

SELECT Title, [Company Name]
FROM Titles, Publishers,
Titles LEFT JOIN Publishers
ON Titles.PUBID = Publishers.PUBID

In this example, PUBID is the joined field, but it doesn't appear in the query output because it isn't included in the SELECT statement.  To include the joined field, enter the field name in the SELECT statementin this case, Titles.PUBID.