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 15 challenges are built around.
Medium · Interview Practice
SQL LEFT JOIN Medium Challenge
A hands-on SQL coding assessment built around LEFT JOIN, IS NULL, missing-records analysis, and business reporting — bridging beginner JOIN concepts and advanced SQL interview questions. No MCQs.
Challenges 1–55 pts each
Challenges 6–107 pts each
Challenges 11–158 pts each
Completion Bonus+5 pts
Total100 points · Pass at 70
Before you begin
- You have exactly 30 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. - Every challenge requires LEFT JOIN — result matching alone is not enough, your query must actually use it.
- Answer all 15 challenges to earn the +5 completion bonus, on top of your correctness score.
- Passing unlocks a shareable certificate and a spot on the LEFT JOIN Medium 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 the same e-commerce dataset as the LEFT JOIN Hard Challenge — a few customers who never ordered, products that never sold, orders placed but never paid, and 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 SQL LEFT JOIN Mistakes
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.
Common LEFT JOIN mistakes
Putting a right-table filter in the WHERE clause instead of the ON clause silently turns a LEFT JOIN back into an INNER JOIN, because WHERE runs after the join and drops any row where that column is NULL — exactly the rows a LEFT JOIN was meant to keep.
Handling NULL Values After LEFT JOIN
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.
SQL LEFT JOIN Interview Questions
"Find customers who never ordered," "find products never sold," "find orders without payments," "show every category even if empty" — these missing-records questions show up constantly in SQL rounds at Accenture, Deloitte, EY, KPMG, Infosys, TCS Digital, Cognizant, Capgemini, Flipkart, Swiggy, and Zomato, because they test whether a candidate actually understands LEFT JOIN or just memorized INNER JOIN syntax.
Real Business Use Cases
Customer re-engagement lists, revenue leakage investigations, product performance dashboards, monthly activity reports — these are the exact questions data analysts answer every week, and every one of them starts from a LEFT JOIN. Passing this challenge at 70/100 unlocks a shareable certificate and a spot on the LEFT JOIN Medium Challenge leaderboard.