Part 3: Mastering the LEFT JOIN | SQL Challenges & Capstone
SQL Joins & Advanced Group Filtering Practice · Part 3 · Lecture Notes
Mastering the LEFT JOIN
How to preserve all records from the left table — including users without orders, products without sales — for complete relationship analysis and gap detection.
Part 3 of 560% Complete
LEFT JOIN
All Left Records
NULL for No Match
Gap Analysis
8 Practice Questions
Learning Objectives
Use LEFT JOIN to return all rows from the left table including those without a match in the right table
Understand that unmatched left-table rows produce NULL in the right-table columns
Identify the missing-match pattern: LEFT JOIN + WHERE right_table.column IS NULL
Apply LEFT JOIN to preserve complete lists — all users, all products — alongside optional related data
Click any section header to open or close it
LEFT JOIN returns all records from the left table and the matching records from the right table. If a row in the left table has no match in the right table, it still appears in the output — but the right-table columns for that row show NULL. No left-table record is ever excluded.
In industry, analysts use LEFT JOIN to identify customers who have never placed an order, users without transactions, products that have never been purchased, customers without subscriptions, and any other scenario where the complete left-table list must be preserved even when matching data is missing.
The Basic Structure
The left table goes after FROM. The right table goes after LEFT JOIN. ON defines the matching condition. Every row from the left table appears in the result — with matching right-table data if it exists, or NULL if it does not.
Basic LEFT JOIN Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
The defining characteristic of LEFT JOIN is that it never drops a left-table row. Even if a user has no matching transaction, that user still appears — with NULL in the amount column. This is the fundamental difference from INNER JOIN, which would silently exclude that user.
Spotting Non-Matching Rows
After a LEFT JOIN, rows where the right-table columns show NULL are the non-matching rows — users with no transactions, customers with no orders, products with no sales. These NULL rows are the analytical gold: they reveal gaps in business activity that INNER JOIN would hide.
All Users Including Those Without Transactions
SELECT
users.first_name,
transactions.amount
FROM users
LEFT JOIN transactions
ON users.user_id = transactions.user_id;
LEFT JOIN combined with WHERE right_table.column IS NULL is one of the most powerful patterns in SQL analytics. It identifies the exact records from the left table that have no match in the right — customers who have never subscribed, products that have never been ordered, users with no support tickets.
The Missing-Match Pattern
FROM users LEFT JOIN orders ON users.user_id = orders.user_id WHERE orders.order_id IS NULL — this returns only users who have placed zero orders. The LEFT JOIN includes all users; the IS NULL filter keeps only those where the order columns are NULL.
All Users Including Those Without Orders
SELECT
users.first_name,
orders.order_id
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;
All Users Including Those Without Support Tickets
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
LEFT JOIN support_tickets
ON users.user_id = support_tickets.user_id;
Many business relationships are optional. Not every user has a subscription. Not every product has been ordered. Not every customer has attended a campaign event. LEFT JOIN models these optional relationships correctly — every left-table record appears whether or not the optional right-table data exists.
LEFT JOIN Preserves the Complete Customer List
Analysts use LEFT JOIN whenever they need the complete list of one entity alongside whatever related data exists. Complete user list + subscription status: LEFT JOIN users to subscriptions. Complete product list + sales quantity: LEFT JOIN products to order_items.
All Users Including Those Without Subscriptions
SELECT
users.first_name,
subscriptions.status
FROM users
LEFT 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 their transaction amounts rounded to 2 decimal places using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and their transaction amounts rounded to 2 decimal places using LEFT JOIN.
SQL Answer
SELECT
users.first_name,
ROUND(transactions.amount, 2) AS amount
FROM users
LEFT JOIN transactions
ON users.user_id = transactions.user_id;
Practice 2Retrieve all users and their order statuses using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and their order statuses using LEFT JOIN.
SQL Answer
SELECT
users.first_name,
orders.order_status
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;
Practice 3Retrieve all products and associated quantities using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all products and associated quantities using LEFT JOIN.
SQL Answer
SELECT
products.product_name,
order_items.quantity
FROM products
LEFT JOIN order_items
ON products.product_id = order_items.product_id;
Practice 4Retrieve all users and subscription monthly_fee values rounded to 2 decimal places using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and subscription monthly_fee values rounded to 2 decimal places using LEFT JOIN.
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;
Practice 5Retrieve all users and support ticket statuses using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and support ticket statuses using LEFT JOIN.
SQL Answer
SELECT
users.first_name,
support_tickets.ticket_status
FROM users
LEFT JOIN support_tickets
ON users.user_id = support_tickets.user_id;
Practice 6Retrieve all users and campaign event types using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and campaign event types using LEFT JOIN.
SQL Answer
SELECT
users.first_name,
campaign_events.event_type
FROM users
LEFT JOIN campaign_events
ON users.user_id = campaign_events.user_id;
Practice 7Retrieve all orders and associated product names using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all orders and associated product names using LEFT JOIN.
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;
Practice 8Retrieve all users and session device types using LEFT JOIN
AI Prompt
Write a MySQL query to retrieve all users and session device types using LEFT JOIN.
SQL Answer
SELECT
users.first_name,
sessions.device_type
FROM users
LEFT JOIN sessions
ON users.user_id = sessions.user_id;
Knowledge Check
Five questions — select the best answer for each.
1. What does LEFT JOIN return that INNER JOIN does not?
AOnly records from the right table
BAll records from the left table, including those with no match in the right table
CDuplicate records from both tables
DOnly records where both tables have NULL values
Correct. LEFT JOIN preserves all rows from the left table. Rows without a matching record in the right table still appear — with NULL in the right-table columns. INNER JOIN would exclude them.
Not quite. LEFT JOIN includes all left-table rows even when no match exists in the right table — those rows show NULL in the right-table columns.
2. In a LEFT JOIN of users and transactions, a user with no transactions appears with what values in the transaction columns?
AZero in the amount column
BThe values from the first transaction in the table
CNULL in all transaction columns
DThe user is excluded entirely
Correct. When LEFT JOIN finds no matching row in the right table, it fills the right-table columns with NULL for that row. This is how LEFT JOIN signals a missing match.
Not quite. When no match exists in the right table, LEFT JOIN fills the right-table columns with NULL — not zero, not excluded, but NULL.
3. What is the analytical purpose of LEFT JOIN combined with WHERE right_table.column IS NULL?
ATo find all rows that exist in both tables
BTo find rows in the left table that have no match in the right table
CTo count the total NULL values in the database
DTo filter out the left table entirely
Correct. LEFT JOIN includes all left-table rows; WHERE right_column IS NULL keeps only those where no match was found. Together they identify the missing relationships — users with no orders, products with no sales.
Not quite. LEFT JOIN + WHERE right_column IS NULL identifies left-table rows with no corresponding right-table match — the gap analysis pattern.
4. Which JOIN should an analyst use to get the complete user list alongside order data, including users who have never ordered?
AINNER JOIN — to ensure only matched records appear
BRIGHT JOIN — to preserve the orders table
CLEFT JOIN — to preserve all users including those without orders
DFULL JOIN — to return all records from both tables
Correct. LEFT JOIN preserves all rows from the left table (users). Users who have never ordered still appear — with NULL in the order columns. This is the standard pattern for complete list + optional relationship analysis.
Not quite. LEFT JOIN preserves all left-table rows. With users on the left, all users appear — including those without orders.
5. An analyst sees NULL in the order_status column after a LEFT JOIN of users and orders. What does this mean?
AThe order was cancelled
BThe order_status column has a data error
CThat user has no matching order in the orders table
DThe JOIN condition was incorrect
Correct. NULL in right-table columns after a LEFT JOIN means the left-table row (user) had no matching row in the right table (orders). That user simply has no orders on record.
Not quite. NULL in a right-table column after LEFT JOIN means there was no matching right-table row for that left-table record — the user has no orders.
Part 3 Summary
The key ideas from this lecture — keep these in mind as you move forward.
LEFT JOIN helps analysts preserve all records from the left table while identifying missing or incomplete business relationships. All left-table rows appear. Non-matching rows show NULL in right-table columns. Use LEFT JOIN when the complete left-table list must be preserved.
LEFT JOINAll Left RowsNULL for No MatchMissing MatchOptional RelationshipsComplete ListGap Analysis
