Part 4: Mastering the RIGHT JOIN | SQL Challenges & Capstone
SQL Joins & Advanced Group Filtering Practice · Part 4 · Lecture Notes
Mastering the RIGHT JOIN
How to preserve all records from the right table — all transactions, all orders, all subscriptions — for completeness checks and reverse relationship analysis.
Part 4 of 580% Complete
RIGHT JOIN
All Right Records
NULL for No Match
Completeness Checks
8 Practice Questions
Learning Objectives
Use RIGHT JOIN to return all rows from the right table including those without a match in the left table
Understand that unmatched right-table rows produce NULL in the left-table columns
Recognise that any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order
Apply RIGHT JOIN for completeness checks — preserving all operational records and identifying orphaned data
Click any section header to open or close it
RIGHT JOIN returns all records from the right table and the matching records from the left table. If a row in the right table has no match in the left table, it still appears — with NULL in the left-table columns. RIGHT JOIN is the mirror image of LEFT JOIN: instead of preserving the left table, it preserves the right table.
In industry, analysts use RIGHT JOIN to preserve operational records, analyze reverse business relationships, identify unmatched records from the right side, perform completeness checks on right-table data, and create reports where the right-side table must be shown in full regardless of whether matching left-side data exists.
The Basic Structure
The left table goes after FROM. The right table goes after RIGHT JOIN. ON defines the matching condition. Every row from the right table appears in the result — with left-table data if a match exists, or NULL in left-table columns if no match is found.
Basic RIGHT JOIN Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
RIGHT JOIN guarantees that every row from the right table appears in the output. A transaction with no matching user in the users table still appears — with NULL in the first_name column. This is useful when the right-side table is the operational record that must be preserved — every transaction, every order, every subscription.
RIGHT JOIN vs LEFT JOIN
Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. FROM users RIGHT JOIN transactions is equivalent to FROM transactions LEFT JOIN users. Most analysts prefer LEFT JOIN because it reads more naturally, but understanding RIGHT JOIN is important for reading queries written by others.
All Transactions Including Those Without Matching Users
SELECT
users.first_name,
transactions.amount
FROM users
RIGHT JOIN transactions
ON users.user_id = transactions.user_id;
RIGHT JOIN shifts the analytical perspective to the right table. Instead of asking “which users have orders?”, RIGHT JOIN asks “which orders have users?” — and includes every order regardless. This reverse perspective is useful when the operational record (order, ticket, subscription) is the primary subject of analysis.
When the Right Table Is the Primary Subject
Use RIGHT JOIN when the right-side table contains the records you must always show — every order, every subscription, every campaign event — and the left-side data (user details) is supplementary. All right-side records appear; left-side details are added where available.
All Orders Including Those Without Matching Users
SELECT
users.first_name,
orders.order_status
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;
All Order Items Including Those Without Matching Products
SELECT
products.product_name,
order_items.quantity
FROM products
RIGHT JOIN order_items
ON products.product_id = order_items.product_id;
RIGHT JOIN is the natural tool when checking whether all operational records have the expected left-side data. Do all subscriptions have a matching user? Do all support tickets have a user_id that exists in users? RIGHT JOIN preserves every subscription and ticket — NULL in the user columns signals an orphaned record without a valid user.
Orphaned Records Are Data Quality Issues
A subscription with no matching user (NULL in first_name after a RIGHT JOIN from users to subscriptions) represents a data quality problem — a subscription record that references a user_id that does not exist in the users table. RIGHT JOIN surfaces these orphaned records.
All Subscriptions Including Those Without Matching Users
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 transaction amounts and matching user names rounded to 2 decimal places using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all transaction amounts and matching user names rounded to 2 decimal places using RIGHT JOIN.
SQL Answer
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 order statuses and matching user names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all order statuses and matching user names using RIGHT JOIN.
SQL Answer
SELECT
users.first_name,
orders.order_status
FROM users
RIGHT JOIN orders
ON users.user_id = orders.user_id;
Practice 3Retrieve all order item quantities and matching product names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all order item quantities and matching product names using RIGHT JOIN.
SQL Answer
SELECT
products.product_name,
order_items.quantity
FROM products
RIGHT JOIN order_items
ON products.product_id = order_items.product_id;
Practice 4Retrieve all subscription monthly_fee values and matching user names rounded to 2 decimal places using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all subscription monthly_fee values and matching user names rounded to 2 decimal places using RIGHT JOIN.
SQL Answer
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 support ticket statuses and matching user names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all support ticket statuses and matching user names using RIGHT JOIN.
SQL Answer
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 campaign event types and matching user names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all campaign event types and matching user names using RIGHT JOIN.
SQL Answer
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 order_items and associated product names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all order_items and associated product names using RIGHT JOIN.
SQL Answer
SELECT
order_items.order_id,
products.product_name
FROM products
RIGHT JOIN order_items
ON products.product_id = order_items.product_id;
Practice 8Retrieve all session device types and matching user names using RIGHT JOIN
AI Prompt
Write a MySQL query to retrieve all session device types and matching user names using RIGHT JOIN.
SQL Answer
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 RIGHT JOIN preserve that LEFT JOIN does not?
AAll records from the left table
BAll records from the right table
COnly records with matching values in both tables
DRecords where both sides have NULL values
Correct. RIGHT JOIN preserves all rows from the right table. Every right-table row appears in the result — with NULL in left-table columns if no match exists. LEFT JOIN preserves the left table instead.
Not quite. RIGHT JOIN preserves all rows from the right table — every right-table row appears, even without a left-table match.
2. In FROM users RIGHT JOIN subscriptions ON users.user_id = subscriptions.user_id, which table is fully preserved?
Ausers
Bsubscriptions
CBoth tables equally
DNeither — only matching rows appear
Correct. In RIGHT JOIN, the table named after RIGHT JOIN is the right table — subscriptions. Every subscription row appears regardless of whether a matching user exists.
Not quite. In a RIGHT JOIN, the table named after RIGHT JOIN (subscriptions) is fully preserved — every subscription row appears.
3. How can a RIGHT JOIN be rewritten as a LEFT JOIN?
ABy adding WHERE right_table.column IS NULL
BBy swapping the order of the two tables
CBy adding DISTINCT to the SELECT clause
DBy replacing ON with WHERE
Correct. FROM table1 RIGHT JOIN table2 ON … is equivalent to FROM table2 LEFT JOIN table1 ON … — just swap the table positions. Most analysts prefer LEFT JOIN because it reads more naturally.
Not quite. Swapping the table order converts RIGHT JOIN to LEFT JOIN: FROM users RIGHT JOIN transactions becomes FROM transactions LEFT JOIN users.
4. After a RIGHT JOIN, a row shows NULL in the users.first_name column. What does this mean?
AThe user has no first name in the database
BThe right-table record has no matching user_id in the users table
CThe JOIN condition was incorrect
DThe user was deleted after the join ran
Correct. NULL in a left-table column after RIGHT JOIN means the right-table row had no corresponding row in the left table — an orphaned record with no matching user.
Not quite. NULL in left-table columns after RIGHT JOIN signals a right-table row with no match in the left table — no corresponding user exists.
5. An analyst wants to preserve all subscription records and find which ones have no matching user. Which JOIN and WHERE condition achieves this?
AFROM subscriptions INNER JOIN users ON subscriptions.user_id = users.user_id WHERE users.user_id IS NULL
BFROM users RIGHT JOIN subscriptions ON users.user_id = subscriptions.user_id WHERE users.user_id IS NULL
CFROM users LEFT JOIN subscriptions ON users.user_id = subscriptions.user_id WHERE subscriptions.user_id IS NULL
DFROM subscriptions LEFT JOIN users ON subscriptions.user_id = users.user_id
Correct. RIGHT JOIN preserves all subscriptions. WHERE users.user_id IS NULL keeps only subscriptions with no matching user — the orphaned records. Option C would find users without subscriptions instead.
Not quite. FROM users RIGHT JOIN subscriptions preserves all subscriptions. WHERE users.user_id IS NULL then filters to subscriptions without a matching user.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
RIGHT JOIN helps analysts preserve all records from the right table while analyzing linked business relationships and operational completeness. All right-table rows appear. Non-matching rows show NULL in left-table columns. RIGHT JOIN is the mirror of LEFT JOIN.
RIGHT JOINAll Right RowsNULL for No MatchMirror of LEFT JOINCompleteness CheckOrphaned RecordsReverse Analysis
