LEFT JOIN and RIGHT JOIN in SQL
LEFT JOIN returns all rows from the left table, paired with matching rows from the right table where the join condition is true, and fills unmatched positions with NULL. RIGHT JOIN does the same in the opposite direction, preserving all rows from the right table.
LEFT JOIN keeps every row from the left table, whether or not it finds a match in the right table.
You're building a customer report and you need to see all customers — including the ones who haven't placed any orders yet. INNER JOIN would silently drop customers with no orders, because there's nothing to match them against in the orders table. LEFT JOIN keeps every customer. Where there's no matching order, the order columns come back as NULL.
That's the core distinction: INNER JOIN only returns rows with matches on both sides; LEFT JOIN returns everything from the left side plus any matches from the right.
Here's what it looks like. All customers, with their orders where they exist:
SELECT c.name, o.id AS order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_idCustomers with orders get rows showing both customer and order data. Customers with no orders still appear — one row per customer, with order_id and total_amount as NULL.
The NULL on the right side becomes a tool. You can use WHERE right_table.id IS NULL to find exactly the rows with no match:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULLThis returns customers with no orders at all. The join finds the unmatched rows; the WHERE filter keeps only those. It's the standard pattern for gap analysis — finding records in one table with nothing corresponding in another.
RIGHT JOIN is the same logic flipped: every row from the right table is kept, with NULLs filling the left side where there's no match. In practice, most analysts flip the table order and use LEFT JOIN instead of writing RIGHT JOIN — it reads more naturally to keep the main table on the left.
Every category appears, even ones with no products. Products with no category are excluded because categories is the right-side table being preserved.
The one thing that trips people up: confusing which side is being preserved.
In FROM A LEFT JOIN B, all rows in A survive — A is the left table. In FROM A RIGHT JOIN B, all rows in B survive — B is the right table. When a result is missing rows you expected, check which table is on the preserved side and whether it should be.
You write: FROM customers c LEFT JOIN orders o ON c.id = o.customer_id. Which rows are guaranteed to appear?
9 LEFT JOIN and RIGHT JOIN practice problems
Write a query to return each customer's name, order ID, and order total. Order columns will be missing for customers who have placed no orders.
Write a query to return the user ID and conversion amount for every user, including users who have never converted (their conversion amount will be `NULL`).
Write a query to return the category name and product name for every category. Empty categories should still appear, with the product column missing.
Write a query to return the names of every customer with no order history.
Write a query to return each Canadian customer's name alongside their order ID and order total. Canadian customers who have not yet placed any orders should still appear, with the order columns missing.
Write a query to return the user ID and account plan for every user with no recorded sessions.
Write a query to return the product name for every product that has never appeared on any order line.
Write a query to return the category name for every empty category.
Write a query to return every free-plan user alongside their conversion amounts. Users who have never converted must still appear, with the conversion column missing.
These problems are part of the LEFT JOIN and RIGHT JOIN lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.