DevGuru Jet SQL LEFT JOIN Operation

Quick References

Features

Partners


			OPERATION: LEFT JOIN FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2 The LEFT JOIN and the similar RIGHT JOIN operations can be used in any FROM clause to combine records from two tables. The LEFT JOIN operation 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. The RIGHT JOIN operation is used to create a right outer join and includes all of the records from the second (right) of the two tables, even if there are no matching values for records in the first. Although a LEFT JOIN or RIGHT JOIN operation can be nested inside an INNER JOIN, the converse is not true. An INNER JOIN operation cannot be nested inside a LEFT JOIN or RIGHT JOIN. Using the tables 'Employee' and 'Project', the LEFT JOIN operation would return the names of all employees whether or not they were currently engaged in any project, but with project names where applicable: SELECT Employee.username, Project.ProjectName FROM Employee LEFT JOIN Project ON Employee.EmployeeID = Project.EmployeeID; Using the same tables, the RIGHT JOIN operation would return the names of all projects, whether or not any of the employees were currently working on them, but with employee names where applicable: SELECT Employee.username, Project.ProjectName FROM Employee RIGHT JOIN Project ON Employee.EmployeeID = Project.EmployeeID; The above two examples are 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. Microsoft warns, "If you try to join fields containing Memo or OLE Object data, an error will occur."