SQL JOIN practice

SELECT s.first_name, s.last_name, c.name AS country
FROM suspects s
JOIN countries c ON c.country_id = s.country_id;

Why it works: An INNER JOIN pairs rows that match on the ON condition and drops rows with no partner. That's why the two hired guns (NULL country_id) disappear. The beams show each suspect linked to their country.

Other databases: Identical everywhere. JOIN on its own means INNER JOIN.

Expected output (any row order):

SELECT c.name, COUNT(s.suspect_id) AS headcount
FROM countries c
LEFT JOIN suspects s ON s.country_id = c.country_id
GROUP BY c.country_id, c.name;

Why it works: LEFT JOIN keeps every row from the left table even with no match, filling the right side with NULL. COUNT(column) skips NULLs, so Germany correctly gets 0. COUNT(*) would count its one NULL-filled row.

Other databases: Identical everywhere. Some databases require every non-aggregated column in GROUP BY, so grouping by both id and name is the portable habit.

Expected output (any row order):

SELECT name, port
FROM informants
WHERE informant_id NOT IN (SELECT informant_id FROM seizures);

Why it works: NOT IN with a subquery keeps rows whose value never appears in the list. LEFT JOIN ... IS NULL is the other common way to find rows with no partner.

Other databases: Identical everywhere. Careful: if the subquery can return a NULL, NOT IN returns nothing at all. NOT EXISTS doesn't have that trap.

Expected output (any row order):

SELECT first_name, last_name
FROM suspects s
WHERE NOT EXISTS (
  SELECT 1 FROM case_suspects cs WHERE cs.suspect_id = s.suspect_id
);

Why it works: A correlated subquery depends on the outer row. NOT EXISTS is true when the inner query finds nothing for that suspect, and unlike NOT IN it handles NULLs safely.

Other databases: Identical everywhere.

Expected output (any row order):

SELECT i.name, SUM(si.quantity * c.street_value) AS total_value
FROM informants i
JOIN seizures s ON s.informant_id = i.informant_id
JOIN seizure_items si ON si.seizure_id = s.seizure_id
JOIN contraband c ON c.item_id = si.item_id
GROUP BY i.informant_id, i.name
HAVING SUM(si.quantity * c.street_value) > 500
ORDER BY total_value DESC;

Why it works: WHERE filters rows before grouping. HAVING filters groups after. Four tables joined in a chain give one row per item seized, and SUM rolls them up per informant.

Other databases: Identical everywhere. PostgreSQL and SQL Server don't let you use the alias total_value in HAVING; repeating the expression is portable.

Expected output (row order matters):

SELECT c.name
FROM contraband c
JOIN seizure_items si ON si.item_id = c.item_id
JOIN seizures s ON s.seizure_id = si.seizure_id
JOIN informants i ON i.informant_id = s.informant_id
WHERE i.port = 'Marseille'
EXCEPT
SELECT c.name
FROM contraband c
JOIN seizure_items si ON si.item_id = c.item_id
JOIN seizures s ON s.seizure_id = si.seizure_id
JOIN informants i ON i.informant_id = s.informant_id
WHERE i.port = 'Rotterdam';

Why it works: EXCEPT returns rows from the first query that don't appear in the second, and removes duplicates. UNION stacks results and INTERSECT keeps only the rows in both.

Other databases: Oracle calls it MINUS (EXCEPT works from 21c). MySQL added EXCEPT in 8.0.31. PostgreSQL and SQL Server use EXCEPT.

Expected output (any row order):

SELECT s.first_name AS suspect, s.bounty_usd,
       b.first_name AS boss, b.bounty_usd AS boss_bounty
FROM suspects s
JOIN suspects b ON b.suspect_id = s.boss_id
WHERE s.bounty_usd > b.bounty_usd;

Why it works: A self-join treats one table as two, with different aliases. Each row pairs a suspect with their boss, so you can compare the two bounties side by side.

Other databases: Identical everywhere.

Expected output (any row order):