FULL OUTER JOIN and CROSS JOIN
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A full outer join keeps the unmatched rows of both tables, and a cross join pairs every row with every row. MySQL has no FULL OUTER JOIN keyword, so you build it from a left join and a right join; cross joins are easy to write, and just as easy to write by accident.
MySQL has no FULL OUTER JOIN: build it with UNION
A full outer join returns every matched pair, plus the left rows with no match and the right rows with no match. MySQL has no FULL OUTER JOIN keyword, so a left join and a right join are combined with UNION.
Some reports need the unmatched rows of both tables at once. MySQL cannot do it in one keyword, so stack a left join and a right join.
Example 1 · Error: FULL OUTER JOIN in MySQL
SELECT d.dept_name, e.nameFROM departments dFULL OUTER JOIN employees e ON e.dept_id = d.dept_id;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN employees e ON e.dept_id = d.dept_id' at line 3
What this code does
- MySQL does not know
FULL OUTER JOIN, so it reports a syntax error near those words. Many other databases accept this query. - The next example builds the same result in MySQL from a left join and a right join.
Example 2 · Exam registrations versus results
DROP TABLE IF EXISTS exam_results, exam_registered;CREATE TABLE exam_registered (roll_no INT PRIMARY KEY, name VARCHAR(40));CREATE TABLE exam_results (roll_no INT PRIMARY KEY, marks INT);
INSERT INTO exam_registered VALUES (1, 'Aarti'), (2, 'Bilal'), (3, 'Chitra'), (4, 'Dev');INSERT INTO exam_results VALUES (2, 78), (3, 91), (4, 65), (7, 88);
-- BeforeSELECT * FROM exam_registered ORDER BY roll_no;SELECT * FROM exam_results ORDER BY roll_no;
-- After: full outer join = LEFT JOIN + RIGHT JOIN, combined with UNIONSELECT COALESCE(g.roll_no, r.roll_no) AS roll_no, g.name, r.marksFROM exam_registered gLEFT JOIN exam_results r ON r.roll_no = g.roll_noUNIONSELECT COALESCE(g.roll_no, r.roll_no), g.name, r.marksFROM exam_registered gRIGHT JOIN exam_results r ON r.roll_no = g.roll_noORDER BY roll_no;+---------+--------+ | roll_no | name | +---------+--------+ | 1 | Aarti | | 2 | Bilal | | 3 | Chitra | | 4 | Dev | +---------+--------+ +---------+-------+ | roll_no | marks | +---------+-------+ | 2 | 78 | | 3 | 91 | | 4 | 65 | | 7 | 88 | +---------+-------+ +---------+--------+-------+ | roll_no | name | marks | +---------+--------+-------+ | 1 | Aarti | NULL | | 2 | Bilal | 78 | | 3 | Chitra | 91 | | 4 | Dev | 65 | | 7 | NULL | 88 | +---------+--------+-------+
What this code does
- Rolls 2, 3 and 4 come from both halves, and
UNIONkeeps one copy of each. Aarti (roll 1, no result) comes from the left join; roll 7 (a result with no student) comes from the right join. COALESCE(g.roll_no, r.roll_no)takes whichever side is notNULL, so every row has a roll number. TheORDER BYat the end sorts the combined result.
Many databases write it as FULL OUTER JOIN; MySQL (up to 9.x) does not support it and stops with a syntax error 1064.
A full outer join exists for reconciliation: comparing two lists that should agree. Students registered for an exam versus results received: a student with no result was absent, and a result with no registered student is a data-entry error. You need both kinds of mismatch in one report.
Order of execution
Build a full outer join
Put the parts in the order that works in MySQL.
FROM employees FULL JOIN departments USING (dept_id) runs without an error. MySQL reads FULL as an alias for employees and does an inner join. If a "full join" in MySQL runs, it is not a full join.UNION versus UNION ALL
UNION stacks the rows of two SELECT statements into one result and removes duplicate rows. UNION ALL stacks them and keeps every row.
UNION stacks the rows of two SELECTs into one result and removes duplicate rows. UNION ALL stacks them and keeps every row, duplicates included. Unlike a join, which puts columns side by side, a union puts rows one under the other.
Example 3 · UNION removes duplicates, UNION ALL keeps them
DROP TABLE IF EXISTS squad_2024, squad_2025;CREATE TABLE squad_2024 (player VARCHAR(30));CREATE TABLE squad_2025 (player VARCHAR(30));INSERT INTO squad_2024 VALUES ('Gill'), ('Rashid'), ('Shami'), ('Sudharsan');INSERT INTO squad_2025 VALUES ('Gill'), ('Rashid'), ('Siraj');
-- Everyone who played in either season, once eachSELECT player FROM squad_2024UNIONSELECT player FROM squad_2025ORDER BY player;
-- Every row from both seasonsSELECT player FROM squad_2024UNION ALLSELECT player FROM squad_2025ORDER BY player;+-----------+ | player | +-----------+ | Gill | | Rashid | | Shami | | Siraj | | Sudharsan | +-----------+ +-----------+ | player | +-----------+ | Gill | | Gill | | Rashid | | Rashid | | Shami | | Siraj | | Sudharsan | +-----------+
What this code does
- Gill and Rashid are in both squads.
UNIONlists them once (5 players);UNION ALLkeeps both copies (7 rows). - Use
UNIONwhen the question is "which distinct values", andUNION ALLwhen every row counts or you know the halves cannot overlap.
Example 4 · Full outer join with UNION ALL
DROP TABLE IF EXISTS exam_results, exam_registered;CREATE TABLE exam_registered (roll_no INT PRIMARY KEY, name VARCHAR(40));CREATE TABLE exam_results (roll_no INT PRIMARY KEY, marks INT);INSERT INTO exam_registered VALUES (1, 'Aarti'), (2, 'Bilal'), (3, 'Chitra'), (4, 'Dev');INSERT INTO exam_results VALUES (2, 78), (3, 91), (4, 65), (7, 88);
-- Part 1: every registered student, with a result or NULLSELECT g.roll_no, g.name, r.marksFROM exam_registered gLEFT JOIN exam_results r ON r.roll_no = g.roll_noUNION ALL-- Part 2: only the results that have no registered studentSELECT r.roll_no, NULL, r.marksFROM exam_results rLEFT JOIN exam_registered g ON g.roll_no = r.roll_noWHERE g.roll_no IS NULLORDER BY roll_no;+---------+--------+-------+ | roll_no | name | marks | +---------+--------+-------+ | 1 | Aarti | NULL | | 2 | Bilal | 78 | | 3 | Chitra | 91 | | 4 | Dev | 65 | | 7 | NULL | 88 | +---------+--------+-------+
What this code does
- Part 2 keeps only the right rows that found no partner (
g.roll_no IS NULL), so the matched rows are never produced twice and no duplicate check is needed. - The result is the same 5 rows as Example 2, built with
UNION ALL: faster, and it never merges two genuinely identical rows. It is written with two left joins, so no right join is needed at all.
The rules: both SELECTs must return the same number of columns (otherwise ERROR 1222 (21000): The used SELECT statements have a different number of columns), in the same order with compatible types. The column names come from the first SELECT, and one ORDER BY at the very end sorts the whole result.
UNION ALL is faster because MySQL skips the duplicate check. For a full outer join it is also safer: join the left side with a LEFT JOIN, then UNION ALL only the right rows that had no partner (WHERE left.key IS NULL). Plain UNION would also merge two rows that are genuinely identical, such as two equal marks for the same student.
CROSS JOIN: every combination
A CROSS JOIN pairs every row of one table with every row of the other, with no condition. 3 sizes and 2 colours give 3 x 2 = 6 rows.
CROSS JOIN pairs every row of one table with every row of the other, with no condition: 3 sizes × 2 colours = 6 rows. This is the Cartesian product, written on purpose.
Example 5 · Every T-shirt variant
DROP TABLE IF EXISTS tshirt_sizes, tshirt_colours;CREATE TABLE tshirt_sizes (size VARCHAR(3), sort_order INT);CREATE TABLE tshirt_colours (colour VARCHAR(10));INSERT INTO tshirt_sizes VALUES ('S', 1), ('M', 2), ('L', 3);INSERT INTO tshirt_colours VALUES ('Black'), ('White');
SELECT c.colour, s.size, CONCAT('TS-', UPPER(LEFT(c.colour, 3)), '-', s.size) AS skuFROM tshirt_colours cCROSS JOIN tshirt_sizes sORDER BY c.colour, s.sort_order;+--------+------+----------+ | colour | size | sku | +--------+------+----------+ | Black | S | TS-BLA-S | | Black | M | TS-BLA-M | | Black | L | TS-BLA-L | | White | S | TS-WHI-S | | White | M | TS-WHI-M | | White | L | TS-WHI-L | +--------+------+----------+
What this code does
- 2 colours × 3 sizes = 6 rows, one for every variant, with no condition needed: here every pair is meaningful.
- The
skucolumn is built from both tables in each row, which is how a shop could create its product codes in one query.
A cross join is the right tool when you really need all combinations: every T-shirt variant (size and colour), a timetable grid of days and periods, or every student paired with every course so that a LEFT JOIN can then mark which pairs are missing. The result grows fast (1,000 × 1,000 = 1,000,000 rows), so cross join only small tables.
In MySQL, CROSS JOIN, JOIN and INNER JOIN are treated the same: with ON they pair matches, without it they pair everything. Write CROSS JOIN only when you want every combination, so readers know it is deliberate.
Predict the output
Predict the cross join
Remember: rows of the first table times rows of the second.
Round 1 of 2
There are 5 departments and 7 categories.
Spotting an accidental cross join
An accidental cross join is a join that lost its condition: FROM customers, orders with no WHERE, or FROM customers c JOIN orders o with no ON, which MySQL accepts. There is no error; the numbers are just wrong.
Example 6 · An accidental cross join inflates a report
-- Wrong: two tables, no join conditionSELECT COUNT(*) AS rows_without_conditionFROM customers c, orders o;
SELECT c.city, COUNT(*) AS ordersFROM customers c, orders oGROUP BY c.cityORDER BY c.city;
-- Right: the same report with the join conditionSELECT c.city, COUNT(*) AS ordersFROM customers cJOIN orders o ON o.customer_id = c.customer_idGROUP BY c.cityORDER BY c.city;+------------------------+ | rows_without_condition | +------------------------+ | 150 | +------------------------+ +-----------+--------+ | city | orders | +-----------+--------+ | Bengaluru | 15 | | Chennai | 30 | | Delhi | 30 | | Hyderabad | 15 | | Kochi | 15 | | Mumbai | 15 | | Pune | 30 | +-----------+--------+ +-----------+--------+ | city | orders | +-----------+--------+ | Bengaluru | 3 | | Chennai | 4 | | Delhi | 3 | | Kochi | 1 | | Mumbai | 1 | | Pune | 3 | +-----------+--------+
What this code does
- Without a condition, every customer row pairs with all 15 orders: 150 rows, and each city's count is (customers in that city) × 15. Chennai shows 30, Hyderabad 15, even though Hyderabad's only customer never ordered.
- With
ON o.customer_id = c.customer_idthe counts add up to 15, the real number of orders, and Hyderabad correctly disappears.
The signs are easy to learn: row counts that equal one table's count times the other's (10 × 15 = 150), totals that are many times too large, and every customer appearing with every order. A left join without ON fails with a syntax error, but inner and comma joins do not, so you must check.
The habit that catches it: before trusting a join, run SELECT COUNT(*) on it and compare with what you expect (for orders joined to customers, the number of orders). Always write joins as JOIN ... ON, so a missing condition stands out.
Fix the error
Fix the lost condition
The report counts orders per city.
Chennai has 4 orders but the report shows 30.
Error
No error: Chennai shows 30 orders instead of 4.
Next step
Check your understanding
12 quick questions