LEFT JOIN vs INNER JOIN
INNER JOIN only keeps rows with a match on both sides. LEFT JOIN keeps every row from the left table, filling in NULLs when there is no match on the right — that gap is exactly what these 20 challenges are built around.
Hard · Interview Simulation
SQL LEFT JOIN Hard Challenge
A real technical SQL interview built entirely around LEFT JOIN, IS NULL, missing-records analysis, and business reporting — no basic syntax, no MCQs.
Challenges 1–54 pts each
Challenges 6–155 pts each
Challenges 16–206 pts each
Completion Bonus+10 pts
Total100 points · Pass at 75
Before you begin
- You have exactly 45 minutes. The timer starts when you click "Start Challenge".
- The sandbox uses a real e-commerce schema with deliberate gaps:
categories,users,products,orders,order_items,payments,calendar_months. - Every challenge requires LEFT JOIN — result matching alone is not enough, your query must actually use it.
- Answer all 20 challenges to earn the +10 completion bonus, on top of your correctness score.
- Passing unlocks a shareable certificate and a spot on the LEFT JOIN Hard Challenge leaderboard.
You need to be logged in to take this challenge.
Learn
What Is SQL LEFT JOIN?
LEFT JOIN returns every row from the left (first) table, and the matching row from the right table when one exists — or NULL in every right-table column when it doesn't. That single behavior is what makes LEFT JOIN the standard tool for "find what's missing": customers with no orders, products with no sales, orders with no payment, categories with no products.
This challenge uses a deliberately imperfect e-commerce dataset — a few customers who never ordered, products that never sold, orders that were placed but never paid, even a product with a broken category reference — because real production databases always have gaps like these, and interviewers know it.
LEFT JOIN vs INNER JOIN
An INNER JOIN silently drops any row without a match on both sides. Swap it in where a LEFT JOIN is required and rows quietly disappear — a customer with zero orders vanishes from the result instead of showing up with NULL order data. This is one of the most common production bugs, and one of the most common interview traps.
Common LEFT JOIN Interview Questions
Why IS NULL matters
After a LEFT JOIN, checking WHERE right_table.id IS NULL is the standard way to isolate rows that had no match — customers with no orders, products with no sales, orders with no payment. Filtering on any other right-side column instead of the join key is a common bug, since a non-key column can be NULL for reasons unrelated to a missing match.
GROUP BY + HAVING after a LEFT JOIN
COUNT(right_table.id) after a LEFT JOIN correctly ignores unmatched rows, which is why COUNT(*) and COUNT(some_column) can disagree — COUNT(*) counts the row itself even when every joined column is NULL.
Multi-table LEFT JOIN chains
Chaining LEFT JOIN users → orders → payments lets a single query answer "did they register, did they order, did they pay" without losing anyone at any stage — the backbone of funnel and retention analysis.
Why Data Analysts Use LEFT JOIN
Almost every "who churned," "what never sold," or "where did we lose revenue" business question is really a missing-records question in disguise — and LEFT JOIN plus IS NULL is how you answer it without writing a NOT IN subquery or a separate anti-join. This challenge builds that instinct across 20 real scenarios: unpaid orders, dormant customers, broken category mappings, and a capstone executive audit that combines several LEFT JOINs into one dashboard query.
NULL Handling Best Practices
After a LEFT JOIN, every column from the right-hand table can be NULL — including columns you might instinctively aggregate. SUM() and AVG() silently skip NULLs, but that means an unmatched row contributes nothing unless you wrap the result in COALESCE(..., 0) to make "no data" explicit as zero rather than leaving a blank cell in a report.
Real Business Use Cases of LEFT JOIN
Revenue leakage investigations, customer retention gap analysis, churn candidate detection, marketing cohort conversion audits — these are the exact questions data teams at e-commerce and fintech companies answer every week, and every one of them starts from a LEFT JOIN. Passing this challenge at 75/100 unlocks a shareable certificate and a spot on the LEFT JOIN Hard Challenge leaderboard.