SQL Joins and Sets

1. What Is It?

JOINs let you query data from multiple tables in a single statement by combining rows based on a matching condition. Set operations (UNION, INTERSECT, EXCEPT) combine the results of multiple SELECT queries as if they were rows in a single result set.

Without JOINs, you'd retrieve data from one table and then fire separate queries for each related record — the classic N+1 problem. JOINs push the combination work into the database, which can execute it efficiently using indexes.

2. How It Works

The four core join types, illustrated with orders and users:

-- INNER JOIN: only rows where the condition matches on BOTH sides
SELECT o.id, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id;
-- Result: only orders that have a matching user. Orphaned orders are excluded.

-- LEFT JOIN: all rows from the left table; NULLs for unmatched right rows
SELECT u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Result: every user, with order_id = NULL for users who have no orders.

-- RIGHT JOIN: all rows from the right table; NULLs for unmatched left rows
-- Rarely used; you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping tables.

-- FULL OUTER JOIN: all rows from both sides; NULLs where no match
SELECT u.email, o.id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;
-- Result: users with no orders AND orders with no user, all in one result.

CROSS JOIN produces the Cartesian product — every combination of rows from both tables. Used occasionally for generating test data or matrix queries; almost never used in production application queries.

Self-joins join a table to itself. Common for hierarchical data:

-- employees table has a manager_id that references the same employees table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Set operations:

-- UNION: combine two result sets, remove duplicates
SELECT email FROM newsletter_subscribers
UNION
SELECT email FROM customers;

-- UNION ALL: combine two result sets, keep duplicates (faster, no dedup pass)
SELECT email FROM newsletter_subscribers
UNION ALL
SELECT email FROM customers;

-- INTERSECT: only rows that appear in BOTH result sets
SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM customers;

-- EXCEPT: rows in the first set that are NOT in the second set
SELECT email FROM customers
EXCEPT
SELECT email FROM unsubscribed;

Requirements for set operations: both queries must return the same number of columns with compatible types.

Visualizing join types:

3. What SDEs Actually Need to Know

INNER JOIN silently drops rows. If orders.user_id has a NULL or references a user_id that doesn't exist in users, those orders simply won't appear in the result. This is a common source of "missing data" bugs. If you're seeing fewer results than expected, switch to LEFT JOIN and look for NULL columns on the right side.

LEFT JOIN + a WHERE filter on the right table that rejects NULLs (like o.status = 'paid') is equivalent to INNER JOIN. This is a common mistake:

-- Intended as LEFT JOIN, but the WHERE filters out the NULLs, making it INNER
SELECT u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';  -- This excludes users with no orders!

-- To filter only joined rows without losing unmatched left rows:
SELECT u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
-- Move the filter into the ON clause

JOIN order affects readability, not always performance. The query optimizer will often reorder joins. Write them in the order that's most readable (starting from the "anchor" entity you're primarily querying).

USING vs. ON: When both tables share an identically-named join column, JOIN orders USING (user_id) is shorthand for JOIN orders ON users.user_id = orders.user_id — only usable when the column has the same name in both tables.

Implicit joins (comma syntax) are legacy and should be avoided:

-- Old-style implicit join — avoid this
SELECT u.email, o.id FROM users u, orders o WHERE u.id = o.user_id;

-- Modern explicit JOIN — use this
SELECT u.email, o.id FROM users u JOIN orders o ON u.id = o.user_id;

The implicit form makes cross joins and inner joins look identical, which causes accidental Cartesian products.

4. Tradeoffs & Decisions

UNION vs. UNION ALL

UNION deduplicates results, which requires a sort or hash operation. UNION ALL skips dedup and is always faster. Use UNION ALL unless you explicitly need deduplication — and if you need dedup, consider whether a SELECT DISTINCT on the combined result might be more readable.

JOINs vs. subqueries vs. CTEs

All three can express the same logic. Rules of thumb:

• JOIN when you need columns from both tables in the result
• Subquery (correlated or uncorrelated) when you need a filtered or aggregated value from a secondary table but don't need its columns in the output
• CTE (WITH clause) when you need to reuse a derived result set in the same query, or when breaking a complex query into named steps improves readability

Modern optimizers often treat these the same, but CTEs in PostgreSQL before v12 were optimization fences (not inlined), which could cause performance surprises.

Many JOINs vs. multiple queries

Joining 8 tables in a single query is sometimes worse than two smaller queries — especially when some joined tables return many rows that get multiplied together. If you're seeing row count explosions (result has more rows than expected), a one-to-many or many-to-many join is likely producing duplicates. Use EXPLAIN to check.

5. Interview Cheat Sheet

Key sentences:

• "INNER JOIN returns only rows where the condition matches on both sides; LEFT JOIN returns all rows from the left table and NULLs for unmatched right rows."
• "A LEFT JOIN with a WHERE comparison on the right-side table (not IS NULL) behaves like an INNER JOIN. This is because the WHERE filters out the NULLs."
• "UNION ALL is faster than UNION because it skips deduplication — use UNION only when you need distinct rows."
• "When I'm missing rows I expect, I switch to LEFT JOIN to see which rows have NULLs on the joined side."

Common follow-ups:

Q: What's the difference between a JOIN condition and a WHERE clause? A: The JOIN condition (ON clause) determines which rows are combined from each table. The WHERE clause filters the combined result. For INNER JOINs they're logically equivalent, but for OUTER JOINs they're not: a condition in WHERE can eliminate NULLs from the outer side, effectively converting a LEFT JOIN into an INNER JOIN.

Q: What causes a row count explosion in a query with multiple joins? A: One-to-many (or many-to-many) joins without aggregation. If users joins orders (one-to-many) and orders joins order_items (one-to-many), the result has one row per order_item — not one row per user. You'll see users duplicated. Fix with aggregation, CTEs that aggregate before joining, or by understanding the intended grain of the result.

Q: When would you use EXCEPT? A: Finding records in one set that are absent from another — e.g., customers who have never placed an order, users on a list who haven't completed onboarding. It's often cleaner than a LEFT JOIN + WHERE IS NULL for pure membership tests.