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: