Join fundamentals: five drills

Five JOIN-only drills, ordered easy → hard. Each one isolates a single JOIN concept. The default editor shows a working solution. Try clearing it and writing your own first; the hidden checks will grade what's there when you press Run.

Tables: users, products, orders, reviews.

Task: list every product that has at least one order. Return id, name, and category, one row per product, ordered by id.

The trap: a naive SELECT p. FROM products p JOIN orders o ON o.product_id = p.id returns one row per order*, not per product.

Task: list every product (even those that have never been ordered) with the number of orders it has. Return id, name, and order_count. Products with no orders should show 0, not NULL. Order by id.

The trap: COUNT(*) returns 1 for an unmatched LEFT JOIN row (the row exists, even if all the right-side columns are NULL). Use COUNT(o.id) to count only matched rows.

Task: find every ordered pair of paid orders by the same user, where the first order id is smaller than the second. Return user_id, first_order_id, second_order_id, ordered by user_id, first_order_id, second_order_id.

The trap: joining a table to itself without an asymmetric condition (a.id < b.id) yields each pair twice and self-pairs ((1, 1), (2, 2), …) once.

Task: find users who left a 5-star review on a product they personally ordered. Return email, product_name, and comment. Order by email, then product_name.

The skill: chaining four tables (users → reviews → products and a separate join through orders) and using INNER JOIN as a filter — only users who actually ordered the reviewed product show up.

Task: find users who have placed at least one order but have never written a review. Return id and email, ordered by id.

The skill: anti-join. Two valid patterns — NOT EXISTS (usually clearest) and LEFT JOIN ... WHERE r.id IS NULL (the "missing match" idiom). Both should produce the same result; NOT EXISTS is generally more index-friendly on real engines.