After this lesson you can produce a result that keeps the unmatched rows from both sides of a join, and you can generate every combination of two lists on purpose, which is handy for building report grids.
FULL JOIN: keep both sides
A LEFT JOIN rescues unmatched rows from the left table, and a RIGHT JOIN rescues them from the right. A FULL JOIN (also written FULL OUTER JOIN) does both at once. It returns matched pairs, plus left rows with no partner, plus right rows with no partner. The sample data was built with exactly one of each: Ravi has no department, and Research has no employees.
SELECT e.id AS employee_id, e.first_name, d.id AS department_id, d.name AS department
FROM employees e
FULL JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;+-------------+------------+---------------+------------+
| employee_id | first_name | department_id | department |
+-------------+------------+---------------+------------+
| 18 | Ravi | NULL | NULL |
| NULL | NULL | 6 | Research |
+-------------+------------+---------------+------------+The WHERE clause keeps only the unmatched rows from either side, so the result is short: Ravi with NULL department columns, and Research with NULL employee columns. Without that filter you would see all 19 matched pairs as well, and 21 rows in total.
You can check the total with a count:
SELECT COUNT(*) AS rows_in_full_join
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;+-------------------+
| rows_in_full_join |
+-------------------+
| 21 |
+-------------------+That is 19 matched pairs, plus 1 unmatched employee, plus 1 unmatched department.
A full join is the natural tool for reconciling two lists, such as comparing records from two systems, where you need to see what is in one and missing from the other.
Emulating FULL JOIN when it is missing
MySQL does not have FULL JOIN. The standard workaround is to take a LEFT JOIN, add the unmatched rows of a RIGHT JOIN, and combine them with UNION. The WHERE in this example keeps only the unmatched rows, so each half contributes one gap. If you drop the WHERE from both halves to get the full result, UNION removes the matched rows that both halves would otherwise return twice:
SELECT e.id AS employee_id, e.first_name, d.id AS department_id, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL
UNION
SELECT e.id, e.first_name, d.id, d.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;+-------------+------------+---------------+------------+
| employee_id | first_name | department_id | department |
+-------------+------------+---------------+------------+
| NULL | NULL | 6 | Research |
| 18 | Ravi | NULL | NULL |
+-------------+------------+---------------+------------+You will learn UNION properly in a later lesson. The idea is just: one query for each side, stacked together. To avoid RIGHT JOIN altogether, you can swap the tables in the second half and use a LEFT JOIN there too, as long as the columns come out in the same order.
CROSS JOIN: every combination
A CROSS JOIN has no ON condition. It pairs every row of the first table with every row of the second. If one table has 3 rows and the other has 2, you get 6.
SELECT d.name AS department, p.name AS project
FROM departments d
CROSS JOIN projects p
WHERE d.id <= 2 AND p.id <= 3
ORDER BY d.name, p.name;+-------------+-----------------+
| department | project |
+-------------+-----------------+
| Engineering | Customer Portal |
| Engineering | Mobile App |
| Engineering | Spring Campaign |
| Sales | Customer Portal |
| Sales | Mobile App |
| Sales | Spring Campaign |
+-------------+-----------------+Two departments times three projects gives six rows, none of which says anything about who really owns what. That is the point of a cross join: it makes a grid of possibilities, not facts.
A practical use: a complete report grid
Suppose a report should show every department for every quarter, even those with no sales, so that missing values are visible. A cross join builds the skeleton, and you can then left join the real figures onto it. A small list of quarters can come from a subquery:
SELECT d.name AS department, q.quarter
FROM departments d
CROSS JOIN (
SELECT 'Q1' AS quarter
UNION ALL SELECT 'Q2'
UNION ALL SELECT 'Q3'
) AS q
WHERE d.id <= 2
ORDER BY d.name, q.quarter;+-------------+---------+
| department | quarter |
+-------------+---------+
| Engineering | Q1 |
| Engineering | Q2 |
| Engineering | Q3 |
| Sales | Q1 |
| Sales | Q2 |
| Sales | Q3 |
+-------------+---------+Two departments times three quarters gives six rows. With all six departments you would get 18, and you could attach any real quarterly numbers to each row with a LEFT JOIN. The subquery with UNION ALL is a portable way to write a tiny inline list. Some databases also offer a VALUES list for the same purpose, with slightly different syntax.
The accidental cross join
The most common cross join is one nobody meant to write. List two tables with a comma and forget the matching rule in WHERE, or join with a condition that is always true, and every row meets every row:
SELECT COUNT(*) AS accidental_rows
FROM employees, departments;+-----------------+
| accidental_rows |
+-----------------+
| 120 |
+-----------------+That is 20 times 6 rows. In a real database with 100,000 customers and 100,000 orders it would be ten billion. If a query suddenly runs for ages or returns far more rows than expected, check that every join has the right ON.
Differences between databases
FULL JOINworks in PostgreSQL, SQL Server and SQLite 3.39 and newer. MySQL has noFULL JOIN, so use theUNIONpattern above.CROSS JOINworks everywhere. Writing two tables with a comma is the older way to say the same thing.- MySQL accepts
JOINwith noONand treats it as a cross join, while PostgreSQL and SQL Server need the keywordCROSS.
For MySQL's own coverage see Self joins and cross joins in MySQL.
Common mistakes
- Expecting FULL JOIN in MySQL. It is a syntax error there. Use the
UNIONpattern. - Forgetting that unmatched rows have NULLs on one side. In a full join the left key is
NULLfor some rows and the right key for others, so test both when you look for gaps. - Writing a comma join without a WHERE. This is a cross join by accident, and the row count explodes.
- Cross joining big tables on purpose. The row count is the two sizes multiplied. Keep both sides small.
- Using DISTINCT to hide an accidental cross join. It makes the output look right while the query still does far too much work.
Exercise
Show every department and every employee that does not line up: employees with no department and departments with no employees, using a join that keeps both sides. Then build a grid of the first three departments by the two cities Pune and Delhi, with a column saying whether the department is located in that city.
Show one possible answer
SELECT e.first_name, d.name AS department
FROM employees e
FULL JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;
SELECT d.name AS department, c.city,
CASE WHEN d.city = c.city THEN 'yes' ELSE 'no' END AS located_there
FROM departments d
CROSS JOIN (SELECT 'Pune' AS city UNION ALL SELECT 'Delhi') AS c
WHERE d.id <= 3
ORDER BY d.name, c.city;+------------+------------+
| first_name | department |
+------------+------------+
| Ravi | NULL |
| NULL | Research |
+------------+------------+
+-------------+-------+---------------+
| department | city | located_there |
+-------------+-------+---------------+
| Engineering | Delhi | no |
| Engineering | Pune | yes |
| Marketing | Delhi | yes |
| Marketing | Pune | no |
| Sales | Delhi | no |
| Sales | Pune | no |
+-------------+-------+---------------+On MySQL, replace the first query with the LEFT JOIN ... UNION ... RIGHT JOIN version shown earlier in the lesson.
Key points
- FULL JOIN keeps unmatched rows from both tables: employees without a department and departments without employees, in one result.
- MySQL has no FULL JOIN. Combine a LEFT JOIN and a RIGHT JOIN with UNION to get the same rows.
- CROSS JOIN pairs every row of one table with every row of the other. The row count is the product of the two counts.
- Cross joins are useful for building a complete grid, for example every department for every quarter, but an accidental one can flood a query with rows.