Part 5: Understanding FULL JOIN | SQL Challenges & Capstone
SQL Joins & Advanced Group Filtering Practice · Part 5 · Lecture Notes
Understanding FULL JOIN
How to combine all records from both tables — simulated in MySQL using LEFT JOIN UNION RIGHT JOIN — for complete relationship visibility and data quality audits.
Part 5 of 5100% Complete
FULL JOIN
LEFT JOIN UNION RIGHT JOIN
All Records Both Sides
Complete Visibility
8 Practice Questions
Learning Objectives
Understand that FULL JOIN returns all records from both tables with NULLs where no match exists
Simulate FULL JOIN in MySQL using LEFT JOIN UNION RIGHT JOIN
Understand how UNION removes duplicate rows from the combined result
Identify the three row types in FULL JOIN output: matched rows, left-only rows, and right-only rows
Click any section header to open or close it
FULL JOIN returns all records from both tables — rows that have matches appear with complete data from both sides, and rows without a match appear with NULL in the columns from the other table. FULL JOIN is the most inclusive join type: no row from either table is excluded.
In industry, analysts use FULL JOIN to analyze complete business relationships, identify missing matches on both sides simultaneously, perform data completeness checks across two tables, combine all operational records for full visibility, and review both linked and unlinked activity in one output.
MySQL Does Not Support FULL JOIN Directly
MySQL does not have a native FULL JOIN keyword. Analysts simulate FULL JOIN using a LEFT JOIN combined with a RIGHT JOIN using UNION, which merges the two result sets and removes duplicates. This is the standard MySQL pattern for full outer join behaviour.
FULL JOIN Simulation in MySQL — Campaign Events
SELECT c.campaign_id,
c.campaign_name,
ce.event_type
FROM campaigns c
LEFT JOIN campaign_events ce
ON c.campaign_id = ce.campaign_id
UNIONSELECT c.campaign_id,
c.campaign_name,
ce.event_type
FROM campaigns c
RIGHT JOIN campaign_events ce
ON c.campaign_id = ce.campaign_id;
The UNION of a LEFT JOIN and a RIGHT JOIN produces FULL JOIN behaviour. The LEFT JOIN captures all left-table rows (including non-matching ones). The RIGHT JOIN captures all right-table rows (including non-matching ones). UNION merges both result sets and automatically removes duplicate rows — the matched rows that appear in both.
How UNION Works Here
UNION combines two SELECT results into one, removing duplicate rows. In the FULL JOIN simulation, matched rows appear in both the LEFT JOIN and RIGHT JOIN results. UNION removes the duplicates, leaving: all matched rows + all unmatched left rows + all unmatched right rows = full outer join.
All Users and All Transactions (FULL JOIN)
SELECT
users.first_name,
transactions.amount
FROM users
LEFT JOIN transactions
ON users.user_id = transactions.user_id
UNION
SELECT
users.first_name,
transactions.amount
FROM users
RIGHT JOIN transactions
ON users.user_id = transactions.user_id;
FULL JOIN provides the most complete picture of any two-table relationship. Every user appears — whether they have an order or not. Every order appears — whether its user_id matches a user or not. The result shows the full landscape: matched pairs, users without orders, and orders without users all in one output.
When to Use FULL JOIN
FULL JOIN is most valuable for data quality audits, completeness reports, and full reconciliation across two tables. For standard analytics, LEFT JOIN or INNER JOIN usually suffices. Reserve FULL JOIN for situations where missing data on both sides needs to be visible simultaneously.
All Users and All Orders (FULL JOIN)
SELECT
users.first_name,
orders.order_status
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id
UNION
SELECT
users.first_name,
orders.order_status
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;
FULL JOIN exposes two types of gaps simultaneously: left-side records with no match (NULL in right-table columns) and right-side records with no match (NULL in left-table columns). This dual gap visibility makes FULL JOIN the standard tool for reconciliation — verifying that all records on both sides are properly linked.
NULL Patterns in FULL JOIN Output
Three row types appear in FULL JOIN output: rows where both sides match (no NULLs from the join), rows where only the left side exists (NULLs in right columns), and rows where only the right side exists (NULLs in left columns). These three patterns describe the complete state of the relationship.
All Users and All Subscriptions (FULL JOIN)
SELECT
users.first_name,
subscriptions.status
FROM users
LEFT JOIN subscriptions
ON users.user_id = subscriptions.user_id
UNION
SELECT
users.first_name,
subscriptions.status
FROM users
RIGHT JOIN subscriptions
ON users.user_id = subscriptions.user_id;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve all users and all transaction amounts rounded to 2 decimal places using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all transaction amounts rounded to 2 decimal places using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
LEFT JOIN transactions
ON users.user_id = transactions.user_id
UNION
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
RIGHT JOIN transactions
ON users.user_id = transactions.user_id;
Practice 2Retrieve all users and all order statuses using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all order statuses using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
orders.order_status
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id
UNION
SELECT
users.first_name,
orders.order_status
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;
Practice 3Retrieve all products and all order item quantities using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all products and all order item quantities using FULL JOIN logic.
SQL Answer
SELECT
products.product_name,
order_items.quantity
FROM products
LEFT JOIN order_items
ON products.product_id = order_items.product_id
UNION
SELECT
products.product_name,
order_items.quantity
FROM products
RIGHT JOIN order_items
ON products.product_id = order_items.product_id;
Practice 4Retrieve all users and all subscription monthly_fee values rounded to 2 decimal places using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all subscription monthly_fee values rounded to 2 decimal places using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
ROUND(subscriptions.monthly_fee, 2) AS monthly_fee
FROM users
LEFT JOIN subscriptions
ON users.user_id = subscriptions.user_id
UNION
SELECT
users.first_name,
ROUND(subscriptions.monthly_fee, 2) AS monthly_fee
FROM users
RIGHT JOIN subscriptions
ON users.user_id = subscriptions.user_id;
Practice 5Retrieve all users and all support ticket statuses using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all support ticket statuses using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
LEFT JOIN support_tickets
ON users.user_id = support_tickets.user_id
UNION
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
RIGHT JOIN support_tickets
ON users.user_id = support_tickets.user_id;
Practice 6Retrieve all users and all campaign event types using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all campaign event types using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
campaign_events.event_type
FROM users
LEFT JOIN campaign_events
ON users.user_id = campaign_events.user_id
UNION
SELECT
users.first_name,
campaign_events.event_type
FROM users
RIGHT JOIN campaign_events
ON users.user_id = campaign_events.user_id;
Practice 7Retrieve all orders and all product names using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all orders and all product names using FULL JOIN logic.
SQL Answer
SELECT
orders.order_id,
products.product_name
FROM orders
LEFT JOIN order_items
ON orders.order_id = order_items.order_id
LEFT JOIN products
ON order_items.product_id = products.product_id
UNION
SELECT
orders.order_id,
products.product_name
FROM orders
RIGHT JOIN order_items
ON orders.order_id = order_items.order_id
RIGHT JOIN products
ON order_items.product_id = products.product_id;
Practice 8Retrieve all users and all session device types using FULL JOIN logic
AI Prompt
Write a MySQL query to retrieve all users and all session device types using FULL JOIN logic.
SQL Answer
SELECT
users.first_name,
sessions.device_type
FROM users
LEFT JOIN sessions
ON users.user_id = sessions.user_id
UNION
SELECT
users.first_name,
sessions.device_type
FROM users
RIGHT JOIN sessions
ON users.user_id = sessions.user_id;
Knowledge Check
Five questions — select the best answer for each.
1. What does FULL JOIN return?
AOnly records that match in both tables
BAll records from the left table only
CAll records from both tables — matched and unmatched — with NULLs where no match exists
DAll records from the right table only
Correct. FULL JOIN returns the complete picture: matched rows appear with full data from both sides; unmatched left rows have NULLs in right columns; unmatched right rows have NULLs in left columns.
Not quite. FULL JOIN returns all records from both tables — matched pairs plus unmatched rows from each side, with NULLs where no match exists.
2. Why does MySQL require LEFT JOIN UNION RIGHT JOIN to simulate FULL JOIN?
AMySQL has a bug that prevents FULL JOIN from working
BMySQL does not support the FULL JOIN keyword and requires this workaround
CUNION is always required before any JOIN in MySQL
DLEFT JOIN and RIGHT JOIN produce identical results in MySQL
Correct. MySQL does not support FULL JOIN syntax. The LEFT JOIN UNION RIGHT JOIN pattern is the standard workaround — it produces identical results to a FULL OUTER JOIN in databases that support it.
Not quite. MySQL simply does not support the FULL JOIN keyword. The LEFT JOIN UNION RIGHT JOIN pattern is the accepted MySQL workaround.
3. What does UNION do in the FULL JOIN simulation?
AIt filters out NULL rows from both join results
BIt combines the LEFT JOIN and RIGHT JOIN results and removes duplicate rows
CIt sorts the combined result alphabetically
DIt requires both SELECT statements to return the same number of rows
Correct. UNION merges two SELECT results into one output and automatically removes duplicate rows. Matched rows appear in both the LEFT JOIN and RIGHT JOIN — UNION keeps them once. This produces the full outer join effect.
Not quite. UNION combines both result sets and removes duplicates. Matched rows that appear in both LEFT and RIGHT JOIN results are deduplicated, producing the FULL JOIN effect.
4. In FULL JOIN output, what does NULL in both table columns on a single row indicate?
AA row that matched in both tables but had NULL data values
BThis cannot happen in FULL JOIN — there are no fully NULL rows
CA data entry error
DIt depends on the JOIN condition
Correct. In FULL JOIN output via LEFT UNION RIGHT, every row comes from at least one table. A row will have either the left table data (with right side NULL if no match), the right table data (with left side NULL if no match), or complete data from both sides.
Not quite. Every row in a FULL JOIN result originates from at least one table. NULLs appear only in the columns of the table that had no matching row.
5. When is FULL JOIN most analytically valuable?
AWhen you want only matching records from both tables
BWhen you want to preserve all left-table records
CWhen you need complete visibility of both sides including all missing matches from either side
DWhen you want to sort results alphabetically
Correct. FULL JOIN is most valuable when you need to see everything simultaneously: matched records, left-side records without a match, and right-side records without a match. It is the tool for complete reconciliation and data quality audits.
Not quite. FULL JOIN provides complete visibility across both tables — matched rows, left-side gaps, and right-side gaps all in one output.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
FULL JOIN helps analysts combine all matching and non-matching records across business datasets to achieve complete relationship visibility. MySQL simulates FULL JOIN using LEFT JOIN UNION RIGHT JOIN. Three row types appear: matched, left-only, and right-only.
FULL JOINUNIONLEFT JOINRIGHT JOINAll RecordsNULL PatternsComplete VisibilityReconciliationData Quality
