JetSQL » Operators » LEFT JOIN

FROM table1 LEFT JOIN table2
   ON table1.field1 compoperator table2.field2
The name of the first (left) table from which information is to be retrieved.
The name of the second (right) table from which information is to be retrieved.
The field from the first table that is being compared to field2 from the second table.
A comparison operator such as =, >, <, etc.
The field from the second table that is being compared to field1 from the first table.

The LEFT JOIN and the similar RIGHT JOIN operators can be used in any FROM clause to combine records from two tables. The LEFT JOIN operator is used to create a left outer join and includes all of the records from the first (left) of the two tables, even if there are no matching values for records in the second.

Although LEFT JOIN or RIGHT JOIN operators can be nested inside an INNER JOIN, the converse is not true. An INNER JOIN operator cannot be nested inside a LEFT JOIN or RIGHT JOIN.


SELECT Employee.Username, Project.ProjectName
FROM Employee LEFT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
Jack SmithHardwork
Herman AllenHardwork
Jill SwaffordHardwork
Bob ThorntonHardwork
Mike SosebeeGrindstone
Jill SwaffordGrindstone
Zack WombleHardwork
Chris LoweryNULL
Samuel JonesNULL
Robert NicholsonNULL

(10 row(s) affected)

This query uses the 'Employee' and 'Project' tables to demonstrate a LEFT JOIN. This example returns the names of all employees whether or not they were currently engaged in any project, but with project names where applicable.

The above example is in contrast to the INNER JOIN example which only returns those records in which the data in the joined fields is the same (i.e. only records for employees currently engaged in projects).

Language(s): MS SQL Server

See Also: