SQL Quiz: Left Join
10 questions · exam conditions
0:00
Left JoinQuestion 1 of 10

A database contains four projects. Project P1 has two tasks, P2 has no tasks, P3 has three tasks, and P4 has no tasks. There is also one task whose project_id is NULL. The query is:

SELECT p.project_id, t.task_id FROM projects p LEFT JOIN tasks t ON p.project_id = t.project_id;

How many rows does the query return?

Six rows, because only the tasks associated with an existing project contribute rows.
Seven rows, because each matched task and each project without a task contributes a row.
Eight rows, because the task with a NULL project and both taskless projects are preserved.
Four rows, because a left join always returns exactly one row for each left-side project.
← Back to quizzes

SQL Quiz

SQL Quiz: Left Join

Practice Left Join in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Left Join, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A database contains four projects. Project P1 has two tasks, P2 has no tasks, P3 has three tasks, and P4 has no tasks. There is also one task whose project_id is NULL. The query is:

SELECT p.project_id, t.task_id FROM projects p LEFT JOIN tasks t ON p.project_id = t.project_id;

How many rows does the query return?

  1. Six rows, because only the tasks associated with an existing project contribute rows.
  2. Seven rows, because each matched task and each project without a task contributes a row. (correct answer)
  3. Eight rows, because the task with a NULL project and both taskless projects are preserved.
  4. Four rows, because a left join always returns exactly one row for each left-side project.
Explanation: When working through JOIN questions, your job is to trace exactly what happens to each row on both sides of the join — don't just count one side and assume that's your answer. A LEFT JOIN returns one row for every match between the left and right tables, plus one row for every left-side record with no match (filling right-side columns with NULL). Here's the row-by-row breakdown: P1 matches two tasks → 2 rows. P2 matches no tasks → 1 row (with NULL task_id). P3 matches three tasks → 3 rows. P4 matches no tasks → 1 row (with NULL task_id). That gives you 2 + 1 + 3 + 1 = 7 rows total, making B correct. The task with a NULL project_id is the key trap. Because the join condition is p.project_id = t.project_id, and NULL never equals anything — including another NULL — that orphaned task matches no project row and is completely excluded from the result. Choice C falls for this trap, incorrectly adding an extra row for the NULL-project task and arriving at eight. Choice A is wrong because it ignores the taskless projects (P2 and P4), which do contribute rows in a LEFT JOIN — that's the whole point of LEFT vs. INNER JOIN. Choice D reflects a common misconception that a LEFT JOIN returns exactly one row per left-side record regardless of matches; in reality, each match generates its own row, so a project with three tasks produces three rows. Study tip: Always remember that NULL = NULL is false in SQL join conditions — orphaned records on the right side vanish in a LEFT JOIN, while orphaned records on the left side survive with NULLs.

Question 2

The column payments.payment_id is a non-NULL primary key, while payments.amount is nullable. An analyst needs the IDs of accounts that have no payment records at all.

Which predicate correctly completes this anti-join query?

SELECT a.account_id FROM accounts a LEFT JOIN payments p ON a.account_id = p.account_id WHERE <span class="fill-in-blank">&nbsp;</span>;

  1. p.payment_id IS NULL, because only a NULL-extended row lacks the non-NULL payment key (correct answer)
  2. p.amount IS NULL, because a missing payment and a payment without an amount are equivalent
  3. a.account_id IS NULL, because unmatched rows set the preserved account identifier to NULL
  4. p.payment_id <> 0, because valid payment identifiers indicate that no payment is missing
Explanation: When you write a LEFT JOIN, SQL preserves every row from the left table and fills in NULL for all right-table columns whenever no match exists. An anti-join exploits this: you filter specifically for those NULL-filled rows to find left-table records with no matching right-table record. The key question is: which column do you test for NULL? You want a column that is guaranteed non-NULL whenever a real match exists. That's exactly what a primary key gives you — payment_id is defined as non-NULL, so it can only appear as NULL in your result set when the row was NULL-extended (i.e., no payment matched). Testing p.payment_id IS NULL therefore unambiguously identifies accounts with zero payment records, confirming A as correct. B is a dangerous trap. Because p.amount is nullable, it could be NULL either because no payment exists or because a payment exists but has no recorded amount. These two situations are not equivalent, so filtering on p.amount IS NULL would incorrectly include accounts that do have payments — just payments with a NULL amount. C gets the logic backwards. In a LEFT JOIN, the left table's columns are always preserved; it's the right table's columns that become NULL on unmatched rows. a.account_id will never be NULL here. D confuses the anti-join pattern entirely. Checking p.payment_id <> 0 filters for existing payments, which is the opposite of what you want — and it also excludes NULL rows, defeating the anti-join. Your study tip: always identify the right table's primary key (guaranteed non-NULL) as your anti-join filter column. Never use a nullable column for this test.

Question 3

A report must list every product and any reviews written in 2026. Product P1 has only a 2025 review, P2 has a 2026 review, and P3 has no reviews. A developer proposes filtering with WHERE r.review_year = 2026 OR r.review_year IS NULL.

Which query correctly returns P1, P2, and P3 while showing only 2026 reviews?

  1. SELECT p.product_id, r.review_id FROM products p LEFT JOIN reviews r ON p.product_id = r.product_id AND r.review_year = 2026; (correct answer)
  2. SELECT p.product_id, r.review_id FROM products p LEFT JOIN reviews r ON p.product_id = r.product_id WHERE r.review_year = 2026 OR r.review_year IS NULL;
  3. SELECT p.product_id, r.review_id FROM products p INNER JOIN reviews r ON p.product_id = r.product_id AND r.review_year = 2026;
  4. SELECT p.product_id, r.review_id FROM products p LEFT JOIN reviews r ON p.product_id = r.product_id WHERE r.review_year <> 2025 OR r.review_year IS NULL;
Explanation: When working with LEFT JOINs, the critical question is: where does your filtering logic live — in the ON clause or the WHERE clause? This distinction completely changes which rows survive. In a LEFT JOIN, the ON clause filters rows during the join itself, before unmatched left-side rows are added back as NULLs. The WHERE clause filters after the join is assembled, which can silently eliminate those NULL rows you were counting on to preserve unmatched products. Option A is correct because placing r.review_year = 2026 directly in the ON clause means the database only attempts to match reviews from 2026. P2 gets its 2026 review matched, while P1 and P3 — having no qualifying review — are preserved with NULL review columns. All three products appear, and no 2025 data leaks through. Option B is the developer's flawed proposal. It moves the filter to the WHERE clause, which seems clever with the IS NULL safety net. However, P1's 2025 review does join successfully, producing a non-NULL review_year of 2025 — so the IS NULL check fails, and P1 gets dropped entirely. The trap is thinking IS NULL catches all unmatched products when it actually only catches products with no reviews at all. Option C uses an INNER JOIN, which immediately eliminates P1 (no 2026 review) and P3 (no reviews), returning only P2. Option D also filters in WHERE and tries to exclude 2025 rows with <> 2025, but again, P1's matched 2025 row produces a non-NULL value that passes the IS NULL check inconsistently across engines. The core study tip: ON filters before NULLs are added; WHERE filters after. If you need to restrict joined rows while keeping all left-side records, always put the condition in the ON clause.

Question 4

The column enrollments.enrollment_id is non-NULL. A report must show every instructor and the number of that instructor's enrollments having score >= 70. Instructors with no qualifying enrollments must show zero.

Which query satisfies both the preservation and counting requirements?

  1. SELECT i.instructor_id, COUNT(e.enrollment_id) FROM instructors i INNER JOIN enrollments e ON i.instructor_id = e.instructor_id AND e.score >= 70 GROUP BY i.instructor_id;
  2. SELECT i.instructor_id, COUNT(e.enrollment_id) FROM instructors i LEFT JOIN enrollments e ON i.instructor_id = e.instructor_id WHERE e.score >= 70 GROUP BY i.instructor_id;
  3. SELECT i.instructor_id, COUNT(*) FROM instructors i LEFT JOIN enrollments e ON i.instructor_id = e.instructor_id AND e.score >= 70 GROUP BY i.instructor_id;
  4. SELECT i.instructor_id, COUNT(e.enrollment_id) FROM instructors i LEFT JOIN enrollments e ON i.instructor_id = e.instructor_id AND e.score >= 70 GROUP BY i.instructor_id; (correct answer)
Explanation: Whenever a question asks you to "show every [X]" and count only a subset of related rows, you're being tested on two distinct skills: row preservation (keeping instructors with no matches) and conditional aggregation (counting only qualifying rows without filtering out NULLs). The key insight is that a LEFT JOIN preserves all rows from the left table (instructors), but where you place your filter condition determines whether that preservation holds. Option D places AND e.score >= 70 directly in the ON clause, meaning the join itself filters which enrollment rows attach to each instructor. Instructors with no enrollments scoring ≥ 70 still appear, but their e.enrollment_id is NULL. Then COUNT(e.enrollment_id) counts only non-NULL values — returning 0 for those instructors. This satisfies both requirements perfectly. Option A uses an INNER JOIN, which immediately drops any instructor who has no qualifying enrollments. Those instructors vanish from the result entirely, violating the preservation requirement. Option B starts with a LEFT JOIN (good), but then adds WHERE e.score >= 70 as a post-join filter. A WHERE clause on the right table's column eliminates NULL rows — effectively converting the LEFT JOIN into an INNER JOIN and again dropping instructors with no qualifying enrollments. Option C is close, but uses COUNT(*) instead of COUNT(e.enrollment_id). For instructors with no qualifying enrollments, the row still exists with all-NULL enrollment columns, and COUNT(*) would return 1 instead of 0 — an incorrect count. Study tip: Remember the mantra — filter conditions that restrict right-table rows belong in the ON clause, not the WHERE clause, when you need to preserve left-table rows.

Question 5

A pricing report runs this query:

SELECT s.sale_id, b.band_name FROM sales s LEFT JOIN price_bands b ON s.amount BETWEEN b.minimum_amount AND b.maximum_amount;

There are three sales with amounts 50, 100, and NULL. Two bands exist: Low covers 0 through 100 inclusive, and High covers 100 through 200 inclusive.

How many rows does the query return?

  1. Three rows, because each sale is preserved exactly once regardless of overlapping bands
  2. Two rows, because the sale with a NULL amount is removed and each other sale matches once
  3. Five rows, because both boundary amounts match both bands and NULL matches neither band
  4. Four rows, because the amount 100 matches both bands and the NULL amount is preserved (correct answer)
Explanation: When a query involves a LEFT JOIN with a range-based ON condition, you need to trace each row from the left table individually and count how many right-table rows it matches — because one left-table row can produce multiple output rows if it joins to multiple right-table rows. Here's how each sale resolves: The sale with amount 50 matches only the Low band (0–100), producing one row. The sale with amount 100 falls within both the Low band (0–100 inclusive) and the High band (100–200 inclusive), so it matches twice, producing two rows. The sale with a NULL amount cannot satisfy any BETWEEN comparison — comparing NULL to a range always yields UNKNOWN, never TRUE — so it matches no band. However, because this is a LEFT JOIN, unmatched left-table rows are still preserved, with NULL substituted for the right-table columns. That gives one additional row. Total: 1 + 2 + 1 = four rows, making D correct. A is wrong because it assumes each sale appears exactly once, ignoring that overlapping band ranges cause the amount 100 to join to two rows. B incorrectly treats the LEFT JOIN like an INNER JOIN by dropping the NULL-amount sale entirely. C overcounts by claiming amount 50 also matches two bands — it doesn't, since 50 falls only within Low (0–100), not High (100–200). A key strategy: always distinguish LEFT JOIN from INNER JOIN. A LEFT JOIN preserves every left-table row, and a non-unique join condition can multiply rows. When you see BETWEEN on a join, immediately ask yourself whether boundary values could match more than one band.

Question 6

The query below is run for two users:

SELECT u.user_id, o.order_id, s.shipment_id FROM users u LEFT JOIN orders o ON u.user_id = o.user_id LEFT JOIN shipments s ON o.order_id = s.order_id;

User U1 has two orders. The first order has two shipments, and the second has no shipments. User U2 has no orders.

How many result rows are returned for U1 and U2 combined?

  1. Three rows: two for the first order and one for the second order, with no row for U2
  2. Four rows: two for the first order, one for the second order, and one for U2 (correct answer)
  3. Five rows: two shipment rows plus one additional row for each order and each user
  4. Two rows: one row for each user, with orders and shipments combined into those rows
Explanation: When working with chained LEFT JOINs, your job is to trace each row through every join step individually — SQL doesn't collapse results into one row per user or per order. Start with U1. Their first order has two shipments, so that order produces two rows (one per shipment). Their second order has no shipments, but because it's a LEFT JOIN, the shipment columns appear as NULL — that order still produces one row. U1 total: three rows. Now U2 has no orders. The first LEFT JOIN preserves U2 anyway, filling order columns with NULL. The second LEFT JOIN then has nothing to join against (the order_id is NULL), so shipment columns are also NULL — but U2 still appears as one row. Grand total: four rows, making B correct. A is wrong because it ignores U2 entirely. A LEFT JOIN is specifically designed to retain rows from the left table even when no match exists on the right — U2 must appear. C is wrong because it imagines extra rows being generated beyond what the joins actually produce. There's no mechanism in a LEFT JOIN that adds bonus rows for each "level" of the join hierarchy. D is wrong because SQL never collapses multiple matched rows into a single result row. Each matching combination generates its own row — two shipments on one order means two separate rows, not one merged row. A useful rule of thumb: in a LEFT JOIN chain, the number of output rows equals the number of matches at the deepest join level, with unmatched rows appearing once with NULLs.

Question 7

In a multi-tenant database, invoices and customers are related by the composite key (tenant_id, customer_id). Customer IDs may be repeated in different tenants. A report must preserve every invoice, including invoices whose customer row is missing, and must never attach a customer from another tenant.

Which query correctly follows the relationship while preserving all invoices?

  1. SELECT i.invoice_id, c.customer_name FROM invoices i LEFT JOIN customers c ON i.customer_id = c.customer_id OR i.tenant_id = c.tenant_id;
  2. SELECT i.invoice_id, c.customer_name FROM invoices i LEFT JOIN customers c ON i.customer_id = c.customer_id WHERE i.tenant_id = c.tenant_id;
  3. SELECT i.invoice_id, c.customer_name FROM invoices i LEFT JOIN customers c ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id; (correct answer)
  4. SELECT i.invoice_id, c.customer_name FROM customers c LEFT JOIN invoices i ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id;
Explanation: When working with composite keys in SQL joins, you need to think carefully about two things: which table drives the result set (LEFT vs RIGHT), and whether your join conditions use AND versus OR or get filtered after the fact. The goal here is to match invoices to their correct customer using both customer_id and tenant_id together, while keeping invoices that have no matching customer. Option C achieves this perfectly: LEFT JOIN customers c ON i.customer_id = c.customer_id AND i.tenant_id = c.tenant_id. The LEFT JOIN ensures every invoice row is preserved even when no customer matches, and both conditions in the ON clause are required simultaneously, preventing a customer from one tenant from accidentally attaching to an invoice from another. Option A is a trap — using OR instead of AND means a row will match if either condition is true alone. This could attach a customer from a different tenant simply because the customer_id matches, violating the multi-tenant isolation requirement. Option B looks plausible but contains a subtle, critical flaw: the tenant filter is moved into the WHERE clause instead of the ON clause. When you filter on a right-table column in WHERE after a LEFT JOIN, rows where the customer is missing produce NULL for c.tenant_id, which fails the WHERE condition — effectively converting your outer join into an inner join and dropping invoices with no customer. Option D reverses the join direction, putting customers on the left. This preserves every customer row, not every invoice row, which is the opposite of what the report requires. Remember: in a LEFT JOIN, conditions that restrict the right table belong in the ON clause, never in WHERE. Moving them to WHERE silently eliminates your NULL rows.

Question 8

A report must list every active customer, including active customers who have no shipped orders. If a customer has shipped orders, one result row should appear for each such order. Tables customers and orders are related by customer_id.

Which query meets the report requirements without retaining pending orders or removing active customers who have no shipped orders?

  1. SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED' WHERE c.active = 1; (correct answer)
  2. SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE c.active = 1 AND o.status = 'SHIPPED';
  3. SELECT c.customer_id, o.order_id FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED' WHERE c.active = 1;
  4. SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id AND c.active = 1 WHERE o.status = 'SHIPPED';
Explanation: When a question asks you to preserve all rows from one table while optionally matching rows from another, you're in LEFT JOIN territory — but the placement of your filter conditions is what makes or breaks the query. A LEFT JOIN returns every row from the left table (here, customers) and fills in NULLs wherever no matching row exists in the right table (orders). The critical insight is this: filter conditions on the right table belong in the ON clause, not the WHERE clause. Putting them in the WHERE clause effectively converts your LEFT JOIN into an INNER JOIN, because filtering on a right-table column after the join discards the NULL rows you were trying to keep. Option A is correct because o.status = 'SHIPPED' appears in the ON clause. This means the join only tries to match shipped orders, but active customers with no shipped orders still appear with NULL in order_id — exactly what the report requires. Option B moves o.status = 'SHIPPED' into the WHERE clause. Once the LEFT JOIN produces NULLs for customers without shipped orders, that WHERE condition eliminates those NULL rows, silently dropping unmatched customers. Option C uses an INNER JOIN, which outright excludes any customer with no matching shipped orders — the opposite of the stated requirement. Option D places c.active = 1 in the ON clause instead of the WHERE clause, then filters o.status = 'SHIPPED' in WHERE. The active filter no longer reliably excludes inactive customers, and the WHERE clause again discards NULL rows, breaking both requirements. Study tip: On SQL exam questions involving LEFT JOINs, ask yourself: "Does this filter touch the right table?" If yes, it belongs in the ON clause — never the WHERE clause.

Question 9

The column employees.employee_id is a non-NULL primary key. A report must return every department and the number of employees assigned to it, showing zero for departments with no employees.

Which query produces the required employee counts?

  1. SELECT d.department_id, COUNT(*) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id;
  2. SELECT d.department_id, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id; (correct answer)
  3. SELECT d.department_id, COUNT(e.employee_id) AS employee_count FROM departments d INNER JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id;
  4. SELECT d.department_id, COUNT(d.department_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id;
Explanation: When a question asks you to count related records while preserving rows with no matches, you're being tested on two interacting concepts: JOIN type and aggregate function behavior. The requirement has two parts: show every department (including empty ones), and show a count of employees specifically. A LEFT JOIN ensures departments without employees still appear — the employee columns simply come back as NULL for those rows. So far, options A, B, and D all use LEFT JOIN correctly, while C uses INNER JOIN, which silently drops any department with no employees. That eliminates C immediately. Now the subtlety: how you write the COUNT matters enormously. COUNT(*) counts every row, including rows where all employee columns are NULL. So in option A, an empty department would show a count of 1 instead of 0 — because the joined row still exists, just with NULLs. That's a classic trap. Option D makes the same logical mistake but counts d.department_id instead of *; since department_id is never NULL (it's from the left table), it also returns 1 for empty departments. Option B uses COUNT(e.employee_id). Because COUNT ignores NULL values, and e.employee_id is NULL for unmatched departments, empty departments correctly return 0. Since employee_id is a non-NULL primary key, any matched employee will always produce a non-NULL value, making this a reliable column to count. B is the correct answer. As a study tip, always ask yourself two questions on aggregation problems: "Does my JOIN preserve the rows I need?" and "Does my COUNT column go NULL for unmatched rows?" Those two checks will catch most mistakes on questions like this.

Question 10

A report must list every supplier, every product belonging to that supplier, and only inspections where passed = 1. Suppliers with no products must remain, and products with no passing inspection must also remain.

Which query correctly preserves records at both required levels?

  1. SELECT s.supplier_id, p.product_id, i.inspection_id FROM suppliers s LEFT JOIN products p ON s.supplier_id = p.supplier_id INNER JOIN inspections i ON p.product_id = i.product_id AND i.passed = 1;
  2. SELECT s.supplier_id, p.product_id, i.inspection_id FROM suppliers s LEFT JOIN products p ON s.supplier_id = p.supplier_id LEFT JOIN inspections i ON p.product_id = i.product_id WHERE i.passed = 1;
  3. SELECT s.supplier_id, p.product_id, i.inspection_id FROM suppliers s LEFT JOIN products p ON s.supplier_id = p.supplier_id LEFT JOIN inspections i ON p.product_id = i.product_id AND i.passed = 1; (correct answer)
  4. SELECT s.supplier_id, p.product_id, i.inspection_id FROM suppliers s INNER JOIN products p ON s.supplier_id = p.supplier_id LEFT JOIN inspections i ON p.product_id = i.product_id AND i.passed = 1;
Explanation: When chaining multiple LEFT JOINs, the placement of filter conditions — whether in the ON clause or the WHERE clause — determines whether optional records survive. This question tests your ability to preserve rows at two levels simultaneously: suppliers without products, and products without passing inspections. The key insight is that filtering in the ON clause of a LEFT JOIN is safe — it controls what the right table contributes while still keeping unmatched left-side rows (they simply get NULLs). Filtering in the WHERE clause, however, eliminates rows after the join, discarding any NULLs that LEFT JOIN preserved. Answer C is correct because both joins are LEFT JOINs, and the passed = 1 condition lives in the ON clause of the inspections join. This means every supplier appears (even without products), every product appears (even without passing inspections), and inspection data is optionally attached only when it qualifies. A is wrong because the second join is an INNER JOIN on inspections, which eliminates any product that has no passing inspection — and worse, any supplier whose products all lack passing inspections disappears entirely. B is wrong despite using two LEFT JOINs. Moving i.passed = 1 into the WHERE clause turns the outer join into an implicit inner join: rows where i.passed is NULL (products with no passing inspection) are filtered out. D is wrong because the first join is INNER, so suppliers with no products are dropped immediately, violating the first preservation requirement. A reliable rule: in a LEFT JOIN chain, keep optional filters in the ON clause, never in WHERE. WHERE always breaks the "optional" guarantee.