JetSQL » Operators » LEFT JOIN
- Syntax:
-
FROM table1 LEFT JOIN table2
ON table1.field1 compoperator table2.field2 - table1
- The name of the first (left) table from which information is to be retrieved.
- table2
- The name of the second (right) table from which information is to be retrieved.
- field1
- The field from the first table that is being compared to field2 from the second table.
- compoperator
- A comparison operator such as =, >, <, etc.
- field2
- 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.
Examples
Code:
SELECT Employee.Username, Project.ProjectName
FROM Employee LEFT JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
Output:
Username ProjectName Jack Smith Hardwork Herman Allen Hardwork Jill Swafford Hardwork Bob Thornton Hardwork Mike Sosebee Grindstone Jill Swafford Grindstone Zack Womble Hardwork Chris Lowery NULL Samuel Jones NULL Robert Nicholson NULL
(10 row(s) affected)
Explanation:
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:
