Example 1
Find all employees and all departments, showing gaps on both sides
Ada matches Engineering. Bob has no department (dept_id is NULL), so department is NULL. HR has no employees, so employee is NULL. COALESCE in ORDER BY sorts NULL employees and departments last.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE departments (id INT, name VARCHAR(50));
CREATE TABLE employees (id INT, name VARCHAR(50), dept_id INT);
INSERT INTO
departments (id, name)
VALUES
(1, 'Engineering'),
(2, 'HR');
INSERT INTO
employees (id, name, dept_id)
VALUES
(1, 'Ada', 1),
(2, 'Bob', NULL);Validated query Shared across supported engines.
SQL
SELECT
e.name AS employee,
d.name AS department
FROM
employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id
ORDER BY
COALESCE(e.name, 'zzz'),
COALESCE(d.name, 'zzz');Expected result Returned rows for the shared example.
| employee | department |
|---|---|
| Ada | Engineering |
| Bob | NULL |
| NULL | HR |
MySQL and MariaDB do not support FULL OUTER JOIN natively. The emulation uses LEFT JOIN UNION RIGHT JOIN to produce the same result. SQL Server and PostgreSQL use identical native syntax.