Q5 introduced UNION, a set operation that “adds” two lists. EXCEPT (called MINUS in Oracle) is the operation that “subtracts”: it removes rows in the right SELECT result from the left SELECT result.

SELECT email FROM A
EXCEPT             -- keep rows present in A but not in B (set difference A − B)
SELECT email FROM B;

all_subscribers stores every newsletter subscriber, while unsubscribed stores the opt-out list. Return the email addresses of the current active subscribers who have not unsubscribed.

Note: subtract the 2 rows in unsubscribed (sato, yamada) from the 4 rows in all_subscribers. Order the result with ORDER BY email.

SELECT email FROM all_subscribers
EXCEPT                       -- subtract the right side from the left (set difference A − B)
SELECT email FROM unsubscribed
ORDER BY email;

/*
  Logical evaluation order:
  1. SELECT email FROM all_subscribers  → get the upper list of 4 rows
  2. SELECT email FROM unsubscribed     → get the lower list of 2 rows
  3. EXCEPT                             → remove rows from the upper list that appear below (set difference: A − B)
  4. ORDER BY email                     → sort email in ascending order
  */

In Q3, GROUP BY + HAVING COUNT(*) > 1 returned a list of “duplicate keys.” However, it only tells you which values are duplicated; it cannot return the specific rows (or other column information such as order_id or amount). Combining a subquery with IN lets you retrieve every row whose key is duplicated.

WHERE (user_id, product_id) IN (
  SELECT user_id, product_id
  FROM orders
  GROUP BY user_id, product_id
  HAVING COUNT(*) > 1   -- list only duplicated combinations
)

The orders table contains duplicate (user_id, product_id) combinations. Return every order row whose combination is duplicated, including both rows in each duplicate pair.

Note: the duplicated combinations are (101, P001) and (102, P002). Each has two rows, so the result contains 4 rows in total.

SELECT
  order_id, user_id, product_id, amount
FROM orders
WHERE (user_id, product_id) IN (
  SELECT   user_id, product_id
  FROM     orders
  GROUP BY user_id, product_id
  HAVING   COUNT(*) > 1   -- list only duplicated combinations
)
ORDER BY user_id, product_id, order_id;

/*
  Logical evaluation order:
  1. Subquery GROUP BY + HAVING                 → extract duplicate keys
  2. FROM orders                                → read all rows in the outer query
  3. WHERE (...) IN                             → keep only rows with duplicate keys
  4. SELECT                                    → project the columns
  5. ORDER BY user_id, product_id, order_id     → sort and output
  */

INTERSECT (set intersection) returns only the common part of two SELECT results. Together with Q5’s UNION (set union) and Q7’s EXCEPT (set difference), it forms the three core set operations.

SELECT email FROM A
INTERSECT        -- keep rows present in both A and B (set intersection A ∩ B)
SELECT email FROM B;

-- Set-operation summary
UNION      -- A ∪ B: add (remove duplicates)
INTERSECT  -- A ∩ B: take the common part
EXCEPT     -- A − B: subtract

Return the email addresses of users who signed up for both the spring and summer campaigns.

Note: sato, suzuki, and tanaka are the 3 people in both campaigns. Exclude yamada, who is only in spring, and kimura, who is only in summer.

SELECT email FROM campaign_spring
INTERSECT                    -- keep only rows present in both (set intersection A ∩ B)
SELECT email FROM campaign_summer
ORDER BY email;

/*
  Logical evaluation order:
  1. SELECT email FROM campaign_spring  → get the upper list of 4 rows
  2. SELECT email FROM campaign_summer  → get the lower list of 4 rows
  3. INTERSECT                          → keep only rows present in both (A ∩ B)
  4. ORDER BY email                     → sort email in ascending order
  */

In Q4, we used ROW_NUMBER() (a window function) to choose one representative row per email. With MIN(id) + GROUP BY, you can achieve the same deduplication without a window function. It also works on older databases and has a simple structure.

-- Q4 approach (window function)
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) = 1

-- Q10 approach (aggregation + subquery)
WHERE id IN (SELECT MIN(id) FROM t GROUP BY email)
-- switch to MAX(id) when you want to keep the latest row

The contacts table contains multiple registrations with the same email address. Without using ROW_NUMBER(), return one row per email, keeping only the first row (the smallest contact_id).

Note: tanaka@ex.com (id=1,3) and sato@ex.com (id=2,5) are duplicated. Use MIN(contact_id) to keep the row with the smallest ID for each email.

SELECT
  c.contact_id, c.email, c.name, c.source
FROM contacts c
WHERE c.contact_id IN (
  SELECT MIN(contact_id)       -- choose the smallest ID for each email
  FROM   contacts
  GROUP BY email
)
ORDER BY c.contact_id;

/*
  Logical evaluation order:
  1. Subquery GROUP BY email       → extract the smallest ID for each email
  2. FROM contacts c               → read all rows in the outer query
  3. WHERE contact_id IN (...)     → keep rows with the smallest IDs
  4. SELECT                        → project the columns
  5. ORDER BY c.contact_id         → sort and output
  */