Chapter 6 — Quiz

14 questions. Answers and explanations at the bottom.

Multiple choice

Q1. An INNER JOIN returns: - A) All rows from the left table - B) Only rows that match the condition in both tables - C) All rows from both tables - D) Every possible pairing

Q2. A join is, in relational-algebra terms: - A) A projection followed by a selection - B) A Cartesian product followed by a selection on the match condition - C) A union of two tables - D) A sort

Q3. You want every customer, including those with no orders. Use: - A) INNER JOIN - B) LEFT JOIN (customers on the left) - C) CROSS JOIN - D) RIGHT JOIN (customers on the right)

Q4. customers LEFT JOIN orders ... WHERE orders.order_id IS NULL finds: - A) Customers with the most orders - B) Customers who have never ordered - C) Orders with no customer - D) All customers and all orders

Q5. SELECT * FROM orders, customers; (no condition) returns: - A) Orders joined to their customers - B) The Cartesian product — every pairing - C) An error - D) Only matching rows

Q6. To relate employees to itself via manager_id, you need: - A) A UNION - B) Two aliases of the same table (a self-join) - C) A CROSS JOIN - D) A subquery only

Q7. Why use LEFT JOIN in the self-join "employee with manager"? - A) It's faster - B) To keep the top employee, who has no manager (NULL manager_id) - C) INNER JOIN can't self-join - D) To remove duplicates

Q8. Putting WHERE o.status = 'delivered' after a LEFT JOIN orders o: - A) Has no effect - B) Effectively turns it into an INNER JOIN, dropping unmatched left rows - C) Is a syntax error - D) Keeps all left rows

Q9. Joining orders (1) to order_items (many) produces: - A) One row per order - B) One row per line item (so orders with multiple items repeat) - C) Fewer rows than orders - D) A Cartesian product

Q10. The single most impactful thing you can do for join performance is: - A) Add DISTINCT - B) Index the columns you join on (especially foreign keys) - C) Use SELECT * - D) Avoid aliases

Q11. FULL OUTER JOIN returns: - A) Only matches - B) All rows from both tables, with NULLs where there's no match - C) All left rows only - D) The Cartesian product

True/False

Q12. A RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping the table order. (True / False)

Q13. Adding DISTINCT is the correct general fix for duplicate rows caused by a one-to-many join. (True / False)

Short answer

Q14. Explain the difference between putting a condition on the right table of a LEFT JOIN in the ON clause versus the WHERE clause.

---

Answer key

Q1 — B. Inner join keeps only rows matching in both tables.

Q2 — B. Product + selection. A missing condition leaves the bare product.

Q3 — B. LEFT JOIN with customers on the left keeps all customers, matched or not.

Q4 — B. The anti-join idiom: left rows whose right match is absent (NULL).

Q5 — B. Comma with no WHERE is a Cartesian product (15 × 12 = 180 here).

Q6 — B. A self-join: two aliases of employees, matching m.employee_id = e.manager_id.

Q7 — B. LEFT JOIN preserves the manager-less top of the hierarchy (NULL manager). INNER would drop them.

Q8 — B. A WHERE condition on the right table excludes the NULL (unmatched) rows, collapsing the outer join to an inner one. Put such conditions in ON.

Q9 — B. One row per matching line item; orders with several items appear several times.

Q10 — B. Index the join columns. An unindexed join on a large table is a classic slow query.

Q11 — B. Full outer keeps everything from both sides, NULL-padding non-matches.

Q12 — True. A RIGHT JOIN B ≡ B LEFT JOIN A. LEFT reads more naturally, so it's preferred.

Q13 — False. Duplicates from a one-to-many join usually mean you're querying at the wrong grain; DISTINCT masks the symptom and can hide real over-counting. Aggregate at the correct level instead (Chapter 7).

Q14. In ON, the condition is part of the match: non-matching right rows simply aren't attached, but the left row is still kept (with NULLs). In WHERE, the condition filters the result: rows where the right side is NULL fail the test and are removed, turning the LEFT JOIN into an INNER JOIN. Filter the optional (right) side in ON; filter the preserved (left) side in WHERE.

Scoring: 12–14 you've got joins; 9–11 re-drill LEFT JOIN and anti-joins; below 9, redo Exercises A–C before Chapter 7.