SQL Quiz: Right Join And Full Outer Join
10 questions · exam conditions
0:00
Right Join And Full Outer JoinQuestion 1 of 10

A report uses Employees e RIGHT JOIN Departments d ON e.department_id = d.department_id. Some departments have no employees, and some have only employees whose active value is zero.

The report then adds WHERE e.active = 1. Which statement best describes the effect?

Every department remains because the right table is preserved before and after filtering.
Only departments with no employees are removed; departments with inactive employees remain.
Departments remain only when at least one joined employee row satisfies the active condition.
All departments remain, but inactive or missing employees appear as null-extended rows.
← Back to quizzes

SQL Quiz

SQL Quiz: Right Join And Full Outer Join

Practice Right Join And Full Outer 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 Right Join And Full Outer 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 report uses Employees e RIGHT JOIN Departments d ON e.department_id = d.department_id. Some departments have no employees, and some have only employees whose active value is zero.

The report then adds WHERE e.active = 1. Which statement best describes the effect?

  1. Every department remains because the right table is preserved before and after filtering.
  2. Only departments with no employees are removed; departments with inactive employees remain.
  3. Departments remain only when at least one joined employee row satisfies the active condition. (correct answer)
  4. All departments remain, but inactive or missing employees appear as null-extended rows.
Explanation: Whenever you see a JOIN combined with a WHERE clause, ask yourself: does the filter run before or after the join? The answer is always after — and that distinction is everything here. A RIGHT JOIN guarantees that every row from the right table (Departments) appears in the result set, even when no matching left-table row exists. Those unmatched departments appear with NULL for all Employee columns. However, once you add WHERE e.active = 1, the database evaluates that condition against the already-joined result. Rows where e.active is NULL (departments with no employees) fail the filter, and rows where e.active = 0 (inactive employees) also fail. Only rows where an actual employee is active survive. This means a department is kept only if at least one of its joined employee rows has active = 1 — making C the correct answer. A is wrong because it treats the RIGHT JOIN's preservation guarantee as permanent, ignoring that a WHERE clause applied afterward can eliminate those null-extended rows entirely. The right table is not magically protected from post-join filtering. B is wrong because it partially recognizes that null rows are removed, but incorrectly claims departments with inactive employees remain. e.active = 0 does not equal 1, so those rows are also filtered out. D is wrong because it describes the raw RIGHT JOIN result before any WHERE clause is applied — once you add the filter, null-extended rows disappear rather than being preserved. Study tip: If you want to filter a right-side table without losing unmatched rows, move the condition into the ON clause instead of WHERE. That's a classic SQL interview pattern worth memorizing.

Question 2

A database does not support FULL OUTER JOIN. Tables A and B both have non-null primary key id. The intended result must include every match, every A-only row, and every B-only row exactly once.

Which strategy correctly emulates A FULL OUTER JOIN B ON A.id = B.id?

  1. Combine an inner join with all rows from both tables by using UNION ALL without filtering.
  2. Combine A LEFT JOIN B with A RIGHT JOIN B by using UNION ALL without filtering.
  3. Use A LEFT JOIN B, then UNION ALL the B rows from B LEFT JOIN A where A.id IS NULL. (correct answer)
  4. Use A LEFT JOIN B, then UNION it with all rows selected directly from table B.
Explanation: When a database lacks FULL OUTER JOIN, you need to manually combine all rows from both tables — matched rows, left-only rows, and right-only rows — without duplicating anything. The key challenge is avoiding duplicate matched rows. The correct approach, C, works in two clean steps. First, A LEFT JOIN B captures every row from A: matched rows (with B data) and A-only rows (where B columns are NULL). Then, B LEFT JOIN A WHERE A.id IS NULL isolates the B-only rows — those in B that had no match in A. Combining these with UNION ALL produces the complete result with no duplicates, because the two sets are disjoint by construction. A is wrong because using UNION ALL on an inner join plus all rows from both tables would include matched rows multiple times — the inner join rows appear again in the raw table dumps, creating duplicates. B is wrong for a subtler reason: combining A LEFT JOIN B with A RIGHT JOIN B via UNION ALL re-includes every matched row twice (once from each join). You'd need UNION (not UNION ALL) to deduplicate, but even that can fail if rows have no unique distinguishing values beyond id. D is wrong because appending all rows from B directly — without filtering out already-matched rows — duplicates every row from B that had a match in A. As a study tip, whenever you emulate FULL OUTER JOIN, remember: left join + anti-join of the right side. The IS NULL filter on the second half is what prevents duplication.

Question 3

Table Projects contains project IDs ten and twenty. Table Assignments contains four rows: two for project ten, one for project thirty, and one with a NULL project ID. A query uses Projects p RIGHT JOIN Assignments a ON p.project_id = a.project_id with no filtering.

What values are returned by COUNT(*), COUNT(p.project_id), and COUNT(a.project_id), in that order?

  1. Four, three, and two, because only matching project identifiers are counted first.
  2. Five, two, and three, because the unmatched project also creates a result row.
  3. Four, two, and four, because every preserved assignment contributes its project identifier.
  4. Four, two, and three, because null-extended and stored null values affect the counts differently. (correct answer)
Explanation: Whenever you see a question combining JOIN types with COUNT variants, your first move should be to mentally reconstruct the full result set before thinking about counting. A RIGHT JOIN preserves all rows from the right table (Assignments), even when no matching row exists in the left table (Projects). That gives you four result rows — one for each assignment. Two rows match project ten (both tables have data), one row has project thirty (only in Assignments, so p.project_id is NULL-extended), and one row has a stored NULL in a.project_id (so both sides show NULL). Now apply the three COUNT functions to those four rows:
  • COUNT(*) counts every row regardless of NULLs → 4
  • COUNT(p.project_id) counts non-NULL values in the left table's column. The project-thirty row gets a NULL-extended p.project_id, and the stored-NULL row also produces NULL here → only the two project-ten rows are non-NULL → 2
  • COUNT(a.project_id) counts non-NULL values in the right table's column. The project-thirty row has a real value (30), and the two project-ten rows have real values, but the stored-NULL row contributes NULL → 3
That makes D correct: four, two, and three. Choice A wrongly claims only "matching" identifiers matter, ignoring how RIGHT JOIN preserves unmatched rows. Choice B invents a fifth row — no unmatched Projects row appears because Projects is the left table here. Choice C says COUNT(a.project_id) returns four, forgetting that the stored NULL in Assignments is still a NULL and goes uncounted. Study tip: Always distinguish between NULL-extended values (produced by the JOIN) and NULLs stored in the original data — both are invisible to COUNT(column), but for different reasons.

Question 4

Table X contains one row whose join key is NULL, and table Y also contains one row whose join key is NULL. A query performs X FULL OUTER JOIN Y ON X.key_value = Y.key_value.

Assuming the ON clause contains no special null-handling logic, what does the join return for these rows?

  1. Two unmatched rows: one preserves the X row and one preserves the Y row. (correct answer)
  2. One matched row because both join keys contain the same NULL marker.
  3. One unmatched row because a full join merges rows having identical missing keys.
  4. No rows because equality involving NULL prevents either side from being preserved.
Explanation: When working with joins and NULL values, the single most important rule to internalize is: NULL does not equal NULL. In SQL, NULL represents an unknown value, and comparing two unknowns with = yields NULL (not TRUE), so the join condition fails to match. In a FULL OUTER JOIN, both sides are preserved independently — rows from the left table that find no match appear with NULLs for the right columns, and vice versa. Here, X.key_value = Y.key_value evaluates to NULL when both sides are NULL, which SQL treats as a failed condition. Neither row matches the other. However, because it's a FULL OUTER JOIN, each unmatched row is still preserved — the X row appears with NULLs filling Y's columns, and the Y row appears with NULLs filling X's columns. That gives you two separate rows, confirming A is correct. Choice B is the most tempting trap — it assumes NULL = NULL is true because "both sides are missing the same thing." But SQL doesn't work that way; NULL is not a value that can be compared with equality. Choice C invents a fictional behavior where FULL JOINs merge rows with matching NULLs, which has no basis in SQL semantics. Choice D gets the NULL-equality part right but incorrectly concludes the rows disappear entirely — FULL OUTER JOIN exists precisely to preserve unmatched rows from both sides, so neither row is dropped. Your study tip: remember "NULL = NULL is NULL, not TRUE." If a question asks about NULL behavior in joins, ask yourself whether the condition can ever evaluate to TRUE — if not, no match occurs, but outer joins still preserve the rows.

Question 5

A Customers table contains customer IDs one and two. An Orders table contains four rows: two orders for customer one, one order for customer three, and one order whose customer_id is NULL.

What is the result shape of Customers c RIGHT JOIN Orders o ON c.customer_id = o.customer_id?

  1. Four rows are returned, and one row has NULL in the customer columns.
  2. Four rows are returned, and two rows have NULL in the customer columns. (correct answer)
  3. Five rows are returned, and two rows have NULL in the customer columns.
  4. Three rows are returned, and one row has NULL in the customer columns.
Explanation: When you see a JOIN question, your first instinct should be to identify which table drives the result. In a RIGHT JOIN, the right table (here, Orders) is the anchor — every row from Orders appears in the output, regardless of whether a match exists in Customers. Orders has four rows, so the result will always have exactly four rows. Now walk through each match: Customer 1's two orders match rows in Customers, so those return full data. The order for Customer 3 has no match in Customers (Customer 3 doesn't exist there), so the Customers columns come back as NULL. The order with a NULL customer_id also fails to match — NULL never equals anything, even another NULL, so the ON condition evaluates to unknown and the Customers columns are again NULL. That gives you four rows with two NULLs in the customer columns, confirming B is correct. A is wrong because it counts only one NULL row, missing the fact that NULL = NULL is never true in SQL join conditions — the NULL customer_id row also fails to match. C is wrong because it returns five rows, which would suggest Customers is the driving table (a LEFT JOIN), not Orders. RIGHT JOIN doesn't add extra rows for unmatched left-table entries. D is wrong for a similar reason — three rows would mean you're filtering out unmatched rows entirely, which describes an INNER JOIN, not an outer join. Study tip: Memorize this rule — NULL never satisfies a join condition. Any row with NULL in the join column is always unmatched, even if both sides have NULL.

Question 6

Table A has IDs one and two. Table B has IDs two and three. Table C has only ID two. A query evaluates (A FULL OUTER JOIN B ON A.id = B.id) INNER JOIN C ON B.id = C.id.

Which rows survive the complete join expression?

  1. IDs one, two, and three survive because the first join preserves both A and B.
  2. IDs two and three survive because the final condition refers to the preserved B side.
  3. Only ID two survives because the later inner join requires a matching non-null B.id in C. (correct answer)
  4. IDs one and two survive because the full join preserves all rows originally found in A.
Explanation: When multiple joins are chained together, you must evaluate them left to right and track what each step produces before moving to the next condition. This question tests whether you understand that a later join can undo the row-preservation effects of an earlier outer join. Start with A FULL OUTER JOIN B ON A.id = B.id. Table A has IDs {1, 2} and B has {2, 3}, so this produces three rows: ID 1 (with a null B.id), ID 2 (fully matched), and ID 3 (with a null A.id). The full outer join preserves all rows from both sides, so all three rows exist in the intermediate result. Now apply INNER JOIN C ON B.id = C.id. Table C contains only ID 2. The inner join requires a real, non-null match between B.id and C.id. The row for ID 1 has a null B.id — it cannot match anything in C, so it's eliminated. The row for ID 3 does have B.id = 3, but C has no ID 3, so it's also eliminated. Only ID 2 has a non-null B.id that matches C, so only ID 2 survives. That confirms C is correct. Choice A is wrong because the full outer join does not guarantee survival through subsequent joins — it only preserves rows within its own operation. Choice B incorrectly claims ID 3 survives; B.id = 3 finds no match in C, so it's dropped. Choice D is wrong for the same reason as A — rows from A (like ID 1) still get eliminated when B.id is null and the next join is an inner join. A reliable strategy: always simulate joins step by step, writing out the intermediate result set before applying the next join condition. Outer joins create nulls that inner joins will later reject.

Question 7

Consider Accounts a RIGHT JOIN Requests r ON a.account_id = r.account_id AND a.status = 'OPEN'. A request references an existing account whose status is CLOSED.

How does the join represent that request?

  1. It retains the request and supplies NULL for all selected account columns. (correct answer)
  2. It removes the request because its account fails part of the join condition.
  3. It retains the request and supplies the closed account's actual column values.
  4. It returns both a matched request row and a separate null-extended request row.
Explanation: When working with outer joins, the critical distinction is between the join condition and a filter. A RIGHT JOIN guarantees that every row from the right table (here, Requests) appears in the result — regardless of whether a matching row exists in the left table (Accounts). The ON clause defines how rows are matched, not which rows survive. In this scenario, the request's account exists but has status CLOSED. The ON condition requires both a.account_id = r.account_id AND a.status = 'OPEN'. Because the status check fails, the account row doesn't satisfy the full condition — so no Accounts row is matched to this request. But since it's a RIGHT JOIN, the request isn't discarded; instead, SQL null-extends it, filling every selected Accounts column with NULL. That makes A correct. B is wrong because removal is what an INNER JOIN would do — it only keeps rows with successful matches on both sides. A RIGHT JOIN never discards right-table rows. C is the most tempting trap: you might think "the account exists, so its data should appear," but the closed account failed the join condition, so it's treated as if no match was found. The actual closed-account values are not returned. D describes a scenario that simply doesn't happen; SQL won't produce two rows for one right-table record in a standard outer join. A useful rule of thumb: in an outer join, the ON clause filters the optional (outer) side — a failed match on the preserved side just produces NULLs, it never eliminates the row.

Question 8

Table L has two rows with join key one and one row with join key two. Table R has two rows with join key one and one row with join key three. The query performs L FULL OUTER JOIN R ON L.key_value = R.key_value.

How many result rows are produced, and how many of them are unmatched rows?

  1. Four result rows are produced, including two unmatched rows.
  2. Five result rows are produced, including one unmatched row.
  3. Five result rows are produced, including two unmatched rows.
  4. Six result rows are produced, including two unmatched rows. (correct answer)
Explanation: When working through join questions, your job is to enumerate every matching combination, then account for any rows from either table that had no match. Here's how to trace this step by step. Table L has rows with keys {1, 1, 2} and Table R has rows with keys {1, 1, 3}. For a FULL OUTER JOIN, you first produce every matched pair, then append unmatched rows from both sides padded with NULLs. The matched pairs come from key = 1: each of L's two "1" rows pairs with each of R's two "1" rows, giving 2 × 2 = 4 matched rows. Next, L's row with key = 2 finds no match in R, so it becomes one unmatched row (R columns NULL). R's row with key = 3 finds no match in L, so it becomes another unmatched row (L columns NULL). That's 4 + 1 + 1 = 6 total rows, with 2 unmatched rows — confirming answer D. Answer A claims only four rows, ignoring both unmatched rows entirely — essentially describing an INNER JOIN result. Answer B gets five rows but only one unmatched row, which would mean either L's key-2 row or R's key-3 row was dropped, not both preserved. Answer C also reaches five rows with two unmatched rows — this error likely comes from thinking the key-1 match produces only 2 rows (1×1 per side) instead of the correct 2×2 = 4 cross-product. A reliable strategy: always multiply matching-key row counts for the inner product, then add one row per unmatched row from either table. Don't assume a one-to-one match when either side has duplicates.

Question 9

Table L contains (key one, flag Y), (key two, flag N), and (key four, flag Y). Table R contains (key one, flag N), (key three, flag Y), and (key four, flag N). A query full-joins the tables on key and then applies WHERE L.flag = 'Y' OR R.flag = 'Y'.

Which keys remain after the WHERE condition is applied?

  1. Keys one and four remain; the unmatched key-three row fails because L.flag is NULL.
  2. Keys one, three, and four remain; each has Y on at least one available side. (correct answer)
  3. Keys two and three remain; only unmatched rows are preserved by the full outer join.
  4. All four keys remain; a full outer join prevents later filters from removing rows.
Explanation: When working with FULL OUTER JOIN and WHERE clauses together, you need to carefully trace each row through two separate steps: first, understand what the join produces, then apply the filter. A full outer join preserves all rows from both tables, padding with NULL where no match exists. Here's what the joined result looks like before filtering:
  • Key one: L.flag = 'Y', R.flag = 'N' ✓ matched
  • Key two: L.flag = 'N', R.flag = NULL ✓ left-only
  • Key three: L.flag = NULL, R.flag = 'Y' ✓ right-only
  • Key four: L.flag = 'Y', R.flag = 'N' ✓ matched
Now apply WHERE L.flag = 'Y' OR R.flag = 'Y'. A row survives if either side shows 'Y'. Key one passes (L.flag = 'Y'). Key two fails (N and NULL — neither is 'Y'). Key three passes (R.flag = 'Y'). Key four passes (L.flag = 'Y'). That leaves keys one, three, and four, making B correct. Answer A incorrectly claims key three fails because L.flag is NULL — but the OR condition only requires one side to be 'Y', and R.flag = 'Y' satisfies that. Answer C confuses full outer joins with something that only keeps unmatched rows — that would be an exclusive full outer join pattern. Answer D is a common misconception: a full outer join does not immunize rows from WHERE filtering; it only controls how tables are combined before filtering. A useful tip: always mentally separate the join (shapes the result set) from the WHERE clause (filters it). NULL comparisons in OR conditions don't automatically disqualify a row if the other condition evaluates to TRUE.

Question 10

A query contains Suppliers s RIGHT JOIN Deliveries d ON s.supplier_id = d.supplier_id. Which rewrite preserves the same row-preservation behavior and matching condition?

  1. Suppliers s LEFT JOIN Deliveries d ON s.supplier_id = d.supplier_id
  2. Deliveries d LEFT JOIN Suppliers s ON s.supplier_id = d.supplier_id (correct answer)
  3. Deliveries d RIGHT JOIN Suppliers s ON s.supplier_id = d.supplier_id
  4. Suppliers s FULL OUTER JOIN Deliveries d ON s.supplier_id = d.supplier_id
Explanation: When working with outer joins, the key concept to internalize is that the join type determines which table's rows are fully preserved, and this behavior is directional — meaning you can always rewrite a RIGHT JOIN as a LEFT JOIN by simply swapping the table order. In the original query, Suppliers s RIGHT JOIN Deliveries d preserves all rows from the right table — Deliveries. Every delivery appears in the result, even if no matching supplier exists. The matching condition uses s.supplier_id = d.supplier_id. Answer B is correct because flipping the table order — writing Deliveries d LEFT JOIN Suppliers s — now makes Deliveries the left table, so a LEFT JOIN preserves all of its rows. The matching condition remains s.supplier_id = d.supplier_id, which is logically identical. Same rows preserved, same join condition. ✓ Answer A is wrong because it keeps Suppliers on the left and applies a LEFT JOIN, which would preserve all Supplier rows instead of all Delivery rows — the opposite of the original intent. Answer C uses Deliveries d RIGHT JOIN Suppliers s, which preserves all rows from the right table, Suppliers — again the opposite of what the original query does. Answer D — a FULL OUTER JOIN — preserves rows from both tables regardless of matches, which is a superset of the original behavior, not an equivalent rewrite. A useful memory trick: RIGHT JOIN = LEFT JOIN with tables swapped. When you see a RIGHT JOIN rewrite question, mentally flip the table order and change to LEFT JOIN — that's your equivalent query.