Unmatched queries

LEFT and RIGHT JOINS are useful for finding unmatched records. For example, to produce a list of all employees who have not taken holidays, using the Employee table and Holiday table:

SELECT Employee.* FROM Employee LEFT JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID WHERE Holiday.EmployeeID IS NULL;

To produce a list of holidays that have not been assigned to any employee, if there be any:

SELECT Holiday.* FROM Employee RIGHT JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID WHERE Employee.EmployeeID IS NULL;

See also:

Relational links and joins

Join types

Other types of SQL statements