The SQL Union Join

The Union Join statement is a powerful join in SQL. The Union Join is not to be confused with the Union set operator. The Union Join enables you to write a SQL statement to retrieve records that do not match on both sides of the join. This is the exact opposite of a left and right outer join, or for that matter, a full outer join. This is part of the SQL standard. However, not many database vendors have implemented it.

To run a Union Join, you can use the Full Outer join syntax and construct a condition that is always false. For example table 1 Full Outer Join table 2 on 3=6.  Here is an example using a department table(department_id PK) and employee table(department_id FK), with the cardinality of one department may contain many employees

SELECT E.last_name,E.first_name,E.department_id,D.department_id
FROM employees E FULL OUTER JOIN departments D on 3=6;

With this SQL statement, you will see all the records from departments with no matching employees and all employees with no matching department.