FULL OUTER JOIN, CROSS JOIN and SELF JOIN

FULL OUTER JOIN

Keeps every row from both tables, filling the missing side with NULL. It is the union of a left and a right join.

-- PostgreSQL, SQL Server, Oracle, SQLite 3.39+
SELECT d.name AS department, e.first_name AS employee
FROM   departments d
FULL OUTER JOIN employees e ON e.dept_id = d.id;

MySQL and MariaDB have no FULL OUTER JOIN. Emulate it:

SELECT d.name AS department, e.first_name AS employee
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
UNION
SELECT d.name, e.first_name
FROM   departments d
RIGHT JOIN employees e ON e.dept_id = d.id;

UNION - not UNION ALL - because the matched rows appear in both halves and must be de duplicated. On the sample data this returns the three matches, plus Research with a NULL employee, plus Nikhil with a NULL department.

CROSS JOIN

Pairs every row of one table with every row of the other. No ON clause, because there is no condition. The result has m x n rows.

-- Deliberate: build a grid of every department and every status
SELECT d.name AS department, s.status_value
FROM   departments d
CROSS JOIN (SELECT 'active' AS status_value
            UNION ALL SELECT 'inactive') s
ORDER BY d.name, s.status_value;

That is the legitimate use: generating a complete grid so a report can show zero rows for combinations that do not exist in the data. The accidental use is the bug - forgetting a join condition in the comma syntax gives you a cross join with no warning.

SELF JOIN

A table joined to itself, with two different aliases. It is not special syntax - just the same table twice.

-- Each employee alongside their manager
SELECT e.first_name  AS employee,
       e.salary      AS employee_salary,
       m.first_name  AS manager,
       m.salary      AS manager_salary
FROM   employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY manager, employee;

LEFT JOIN rather than INNER JOIN, because Asha has no manager - manager_id is NULL - and an inner join would drop the person at the top of the tree.

-- Employees who earn more than their manager
SELECT e.first_name AS employee, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM   employees e
JOIN   employees m ON m.id = e.manager_id
WHERE  e.salary > m.salary;

-- Pairs of colleagues in the same department, each pair listed once
SELECT a.first_name AS person_a, b.first_name AS person_b, a.dept_id
FROM   employees a
JOIN   employees b ON b.dept_id = a.dept_id
                  AND b.id > a.id
ORDER BY a.dept_id;

b.id > a.id does two jobs: it stops a row pairing with itself, and it stops each pair appearing twice in both orders. Using <> instead would return every pair twice.

Important rules

• FULL OUTER JOIN does not exist in MySQL or MariaDB; emulate it with UNION.
• CROSS JOIN takes no ON clause. Row count is the product of both tables - 1,000 x 1,000 is a million rows.
• A self join needs two distinct aliases; without them every column reference is ambiguous.
• Self joins on a parent pointer handle one level of hierarchy. For arbitrary depth you need a recursive CTE.

Common mistakes

• Writing FULL OUTER JOIN on MySQL and getting a syntax error mid demo.
• Emulating a full join with UNION ALL and getting the matched rows twice.
• Producing an accidental cross join by omitting a join condition in comma syntax.
• Using an inner self join for a manager report and losing the CEO.
• Self joining without an inequality and getting every pair twice plus every row matched to itself.

Best practices

• Write CROSS JOIN explicitly when you mean it, so reviewers can see it was deliberate.
• Alias self joined tables meaningfully - e and m, not t1 and t2.
• Use > rather than <> when generating unique pairs.
• Prefer a recursive CTE over stacked self joins once you need more than two levels.

Practice

1. List every employee with their manager's name, keeping employees who have no manager.
2. Emulate a full outer join between customers and orders in MySQL syntax.
3. Generate one row per department per month of 2024, ready to be left joined to actual data.