Chapter 6 Quiz: Joining Tables

Test your understanding of INNER, OUTER, CROSS, and LATERAL joins. Choose the best answer for each question.

Question 1

What does an INNER JOIN return?

A) All rows from the left table, with NULLs for unmatched right-side rows B) All rows from both tables, with NULLs where no match exists C) Only rows where matching values exist in both tables D) The Cartesian product of both tables

Answer **C) Only rows where matching values exist in both tables.** An INNER JOIN returns only the rows where the join condition is satisfied on both sides. Unmatched rows from either table are excluded.

Question 2

Given CUSTOMER has 100 rows and ACCOUNT has 250 rows (every account has a valid customer), how many rows does this query return at most?

SELECT * FROM CUSTOMER c INNER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID;

A) 100 B) 250 C) 350 D) 25,000

Answer **B) 250.** Each account has exactly one customer, so each of the 250 ACCOUNT rows matches exactly one CUSTOMER row. The result has 250 rows. (If some customers had multiple accounts, the number could exceed 100 but not 250 given the one-customer-per-account constraint.)

Question 3

What is the difference between JOIN and INNER JOIN in DB2?

A) JOIN defaults to LEFT OUTER JOIN B) JOIN defaults to CROSS JOIN C) There is no difference; both are INNER JOIN D) JOIN is invalid syntax in DB2

Answer **C) There is no difference; both are INNER JOIN.** The SQL standard specifies that `JOIN` without a qualifier defaults to `INNER JOIN`. DB2 follows this standard. However, writing `INNER JOIN` explicitly is recommended for clarity.

Question 4

In a self-join of the EMPLOYEE table, why are table aliases mandatory?

A) DB2 requires aliases for all joins B) Without aliases, DB2 cannot distinguish between the two references to the same table C) Self-joins do not actually work without aliases D) Aliases improve performance in self-joins

Answer **B) Without aliases, DB2 cannot distinguish between the two references to the same table.** When a table appears twice in the FROM clause, the database needs a way to identify which reference you mean in the SELECT and ON clauses. Aliases provide this disambiguation.

Question 5

Which query correctly finds customers who have NO accounts?

A)

SELECT c.* FROM CUSTOMER c INNER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID
WHERE a.ACCOUNT_ID IS NULL;

B)

SELECT c.* FROM CUSTOMER c LEFT OUTER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID
WHERE a.ACCOUNT_ID IS NULL;

C)

SELECT c.* FROM CUSTOMER c RIGHT OUTER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID
WHERE a.ACCOUNT_ID IS NULL;

D)

SELECT c.* FROM CUSTOMER c CROSS JOIN ACCOUNT a WHERE a.ACCOUNT_ID IS NULL;
Answer **B) LEFT OUTER JOIN with WHERE a.ACCOUNT_ID IS NULL.** This is the anti-join pattern. The LEFT OUTER JOIN preserves all CUSTOMER rows, and the WHERE clause filters to keep only those with no matching ACCOUNT row (where ACCOUNT_ID is NULL). Option A is incorrect because INNER JOIN never produces NULLs from unmatched rows. Option C is incorrect because RIGHT OUTER JOIN preserves ACCOUNT rows, not CUSTOMER rows. Option D is nonsensical.

Question 6

What is the critical difference between placing a filter condition in the ON clause versus the WHERE clause of a LEFT OUTER JOIN?

A) There is no difference; the optimizer treats them identically B) ON clause filters apply before the join; WHERE clause filters apply after, potentially eliminating preserved NULL rows C) WHERE clause filters are faster because they reduce data earlier D) ON clause conditions can only reference the left table

Answer **B) ON clause filters apply before the join; WHERE clause filters apply after, potentially eliminating preserved NULL rows.** For INNER JOINs, the placement makes no logical difference. For OUTER JOINs, a WHERE clause condition on the outer (nullable) table can eliminate the preserved rows, effectively converting the outer join to an inner join. Always place conditions about the optional table in the ON clause when using outer joins.

Question 7

Given 4 branches and 3 account types in a VALUES clause, how many rows does this CROSS JOIN produce?

SELECT * FROM BRANCH b CROSS JOIN (VALUES ('CHECKING'), ('SAVINGS'), ('CD')) AS at(ACCOUNT_TYPE);

A) 3 B) 4 C) 7 D) 12

Answer **D) 12.** A CROSS JOIN produces the Cartesian product: 4 branches x 3 account types = 12 rows. Every branch is paired with every account type.

Question 8

What happens if you omit the ON clause in a regular JOIN (not a CROSS JOIN)?

A) DB2 treats it as a CROSS JOIN B) DB2 returns an error C) DB2 performs a natural join on identically named columns D) DB2 returns an empty result set

Answer **B) DB2 returns an error.** The ON clause (or USING clause) is required for INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN in DB2. Omitting it produces a syntax error. Only CROSS JOIN is written without an ON clause.

Question 9

Which statement about RIGHT OUTER JOIN is true?

A) It preserves all rows from the left table B) It preserves all rows from the right table C) It preserves all rows from both tables D) It is required for self-joins

Answer **B) It preserves all rows from the right table.** RIGHT OUTER JOIN preserves every row from the right table, filling in NULLs for left-table columns when no match exists. It is the mirror image of LEFT OUTER JOIN.

Question 10

These two queries produce the same result. True or false?

-- Query 1: RIGHT OUTER JOIN
SELECT a.ACCOUNT_ID, c.LAST_NAME
FROM ACCOUNT a RIGHT OUTER JOIN CUSTOMER c ON a.CUSTOMER_ID = c.CUSTOMER_ID;

-- Query 2: LEFT OUTER JOIN
SELECT a.ACCOUNT_ID, c.LAST_NAME
FROM CUSTOMER c LEFT OUTER JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID;

A) True B) False

Answer **A) True.** A RIGHT OUTER JOIN with table A on the left and table B on the right is logically identical to a LEFT OUTER JOIN with table B on the left and table A on the right. Both preserve all CUSTOMER rows, filling in NULLs for ACCOUNT columns when no match exists.

Question 11

When is FULL OUTER JOIN most appropriate?

A) When you want only matching rows B) When you want to preserve rows from one specific table C) When you need to find unmatched rows on both sides, such as data reconciliation D) When generating test data

Answer **C) When you need to find unmatched rows on both sides, such as data reconciliation.** FULL OUTER JOIN preserves unmatched rows from both the left and right tables. This makes it ideal for comparing two data sources to find records that exist in one but not the other.

Question 12

What does LATERAL allow in a join that a regular subquery in FROM does not?

A) Aggregation functions B) References to columns from the outer (left-side) table C) ORDER BY within the subquery D) FETCH FIRST N ROWS ONLY

Answer **B) References to columns from the outer (left-side) table.** A regular derived table (subquery in FROM) is evaluated independently and cannot reference columns from other tables in the same FROM clause. LATERAL lifts this restriction, allowing the subquery to correlate with the left-side table — similar to a correlated subquery but in the FROM clause.

Question 13

To preserve left-side rows when using LATERAL (i.e., keep rows even when the LATERAL subquery returns nothing), you should use:

A) INNER JOIN LATERAL (...) AS alias B) , LATERAL (...) AS alias (comma syntax) C) LEFT OUTER JOIN LATERAL (...) AS alias ON 1=1 D) CROSS JOIN LATERAL (...) AS alias

Answer **C) LEFT OUTER JOIN LATERAL (...) AS alias ON 1=1.** The comma syntax and INNER JOIN LATERAL both exclude left-side rows when the LATERAL subquery returns no rows (equivalent to an inner join). LEFT OUTER JOIN LATERAL preserves the left-side row with NULLs. The `ON 1=1` is required syntactically for the LEFT OUTER JOIN but always evaluates to true; the actual correlation is inside the LATERAL subquery.

Question 14

You join CUSTOMER (5 rows) to ACCOUNT (10 rows) to TRANSACTION (1000 rows) with INNER JOINs. What is the maximum possible number of rows in the result?

A) 1,000 B) 1,015 C) 5,000 D) 50,000

Answer **A) 1,000.** Each TRANSACTION row matches exactly one ACCOUNT row (via ACCOUNT_ID), and each ACCOUNT row matches exactly one CUSTOMER row (via CUSTOMER_ID). The join cannot produce more rows than the most granular table (TRANSACTION), assuming the foreign keys are valid. The maximum is 1,000 rows.

Question 15

What is the "row multiplication" problem in joins?

A) The optimizer duplicates rows for parallel processing B) Joining a one-to-many relationship produces more rows than expected, inflating aggregation results C) Cross joins multiply row counts D) Indexes multiply the number of data pages read

Answer **B) Joining a one-to-many relationship produces more rows than expected, inflating aggregation results.** When you join ACCOUNT to TRANSACTION (one-to-many) and then aggregate ACCOUNT.BALANCE, the balance is repeated for every transaction, inflating the SUM. The fix is to aggregate at the appropriate level using subqueries before joining.

Question 16

Which of the following is the old-style (pre-SQL:1992) join syntax, and why should it be avoided?

A) FROM A INNER JOIN B ON A.id = B.id — verbose B) FROM A, B WHERE A.id = B.id — mixes join and filter logic C) FROM A NATURAL JOIN B — implicit column matching D) FROM A CROSS APPLY B — non-standard

Answer **B) FROM A, B WHERE A.id = B.id — mixes join and filter logic.** The comma-separated FROM clause places both the join condition and business filters in the WHERE clause, making it easy to accidentally omit a join condition (creating a Cartesian product). The explicit JOIN...ON syntax separates join logic from filter logic and clearly expresses outer joins.

Question 17

DB2's optimizer uses three primary join strategies. Which one builds a hash table from the smaller table and probes it with the larger?

A) Nested loop join B) Merge scan join C) Hash join D) Index join

Answer **C) Hash join.** A hash join builds an in-memory hash table from the smaller (build) table on the join key, then scans the larger (probe) table and looks up each row in the hash table. It is effective for large tables without useful indexes, but only works with equi-joins.

Question 18

Does the order in which you list tables in the FROM clause determine the order DB2 processes them?

A) Yes, DB2 processes them left to right B) Yes, but only for INNER JOINs C) No, the optimizer determines the processing order independently D) No, DB2 always processes the smallest table first

Answer **C) No, the optimizer determines the processing order independently.** DB2's cost-based optimizer evaluates many possible join orders and selects the one with the lowest estimated cost. The textual order in your SQL is for human readability only (with some edge cases involving outer join semantics where order matters logically).

Question 19

What is "join elimination" in DB2?

A) The optimizer removing unnecessary joins when the joined table does not affect the result B) The user deleting redundant join conditions C) DB2 refusing to execute joins on tables without indexes D) The optimizer converting outer joins to inner joins

Answer **A) The optimizer removing unnecessary joins when the joined table does not affect the result.** If you join to a table but select no columns from it, and a foreign key constraint guarantees every row will match, DB2 may eliminate the join entirely for better performance. This is transparent and produces the correct result.

Question 20

Which query correctly shows all branches and their employee count, returning 0 for branches with no employees?

A)

SELECT b.BRANCH_NAME, COUNT(*) FROM BRANCH b
LEFT OUTER JOIN EMPLOYEE e ON b.BRANCH_ID = e.BRANCH_ID
GROUP BY b.BRANCH_NAME;

B)

SELECT b.BRANCH_NAME, COUNT(e.EMPLOYEE_ID) FROM BRANCH b
LEFT OUTER JOIN EMPLOYEE e ON b.BRANCH_ID = e.BRANCH_ID
GROUP BY b.BRANCH_NAME;

C)

SELECT b.BRANCH_NAME, COUNT(e.EMPLOYEE_ID) FROM BRANCH b
INNER JOIN EMPLOYEE e ON b.BRANCH_ID = e.BRANCH_ID
GROUP BY b.BRANCH_NAME;

D)

SELECT b.BRANCH_NAME, COUNT(*) FROM BRANCH b
INNER JOIN EMPLOYEE e ON b.BRANCH_ID = e.BRANCH_ID
GROUP BY b.BRANCH_NAME;
Answer **B) COUNT(e.EMPLOYEE_ID) with LEFT OUTER JOIN.** Option A uses COUNT(*), which counts all rows including the preserved NULL rows, giving 1 instead of 0 for branches with no employees. Option B correctly uses COUNT(e.EMPLOYEE_ID), which does not count NULLs, returning 0 for unmatched branches. Options C and D use INNER JOIN, which excludes branches with no employees entirely.

Question 21

You need to find each customer's 3 most recent transactions. Which approach is most appropriate?

A) INNER JOIN with ORDER BY and FETCH FIRST 3 ROWS ONLY B) LEFT OUTER JOIN with a WHERE clause limiting to 3 rows C) LATERAL join correlating on CUSTOMER_ID with ORDER BY and FETCH FIRST 3 ROWS ONLY D) CROSS JOIN with a filter

Answer **C) LATERAL join correlating on CUSTOMER_ID with ORDER BY and FETCH FIRST 3 ROWS ONLY.** LATERAL allows you to write a correlated subquery in the FROM clause that returns the top 3 transactions per customer. Option A would limit the entire result set to 3 rows total, not 3 per customer. Option B has the same problem. Option D makes no sense for this requirement.

Question 22

What does ON 1=1 mean in LEFT OUTER JOIN LATERAL (...) AS lt ON 1=1?

A) It is a Cartesian product condition B) It is a syntactic requirement for LEFT OUTER JOIN; the actual correlation is inside the LATERAL subquery C) It means "always match" and overrides the LATERAL correlation D) It is DB2-specific syntax for unconditional joins

Answer **B) It is a syntactic requirement for LEFT OUTER JOIN; the actual correlation is inside the LATERAL subquery.** LEFT OUTER JOIN requires an ON clause. Since the LATERAL subquery already contains the correlation (WHERE ... = outer_table.column), the ON clause just needs a condition that is always true. `ON 1=1` fulfills this requirement without adding any additional filtering.

Question 23

Which of the following is NOT a valid use case for CROSS JOIN?

A) Generating a date-branch reporting skeleton B) Creating all possible combinations of categories for a pivot report C) Looking up a customer's account balance D) Generating test data by combining value sets

Answer **C) Looking up a customer's account balance.** Looking up a specific customer's account balance requires a targeted join on CUSTOMER_ID, not a Cartesian product. CROSS JOIN is appropriate for deliberately generating all combinations of two sets (options A, B, D).

Question 24

A query joins 5 tables. How many possible join orders can the optimizer consider (before pruning)?

A) 5 B) 25 C) 120 D) 3,125

Answer **C) 120.** The number of possible orderings of N tables is N! (N factorial). 5! = 5 x 4 x 3 x 2 x 1 = 120. In practice, the optimizer prunes many candidates using heuristics and cost estimates, but the theoretical search space is 120.

Question 25

You write a LEFT OUTER JOIN query but get the same results as an INNER JOIN. What is the most likely cause?

A) The optimizer converted the outer join for performance B) A WHERE clause condition on the right-side table is filtering out the NULL rows C) There are no unmatched rows; every left-side row has a match D) Either B or C

Answer **D) Either B or C.** If every left-side row matches at least one right-side row, then LEFT OUTER JOIN and INNER JOIN produce identical results (option C). Alternatively, a WHERE clause filter on the right-side table eliminates the preserved NULL rows, making the outer join behave like an inner join (option B). Both are common in practice.

Scoring Guide

Score Level
23-25 Expert — ready for advanced query optimization
19-22 Proficient — solid understanding of join mechanics
14-18 Developing — review outer join semantics and LATERAL
Below 14 Revisit this chapter, especially Sections 6.4, 6.7, and 6.9