Module 1 — JOINS

Why JOINs exist

A real database almost never stores everything in one giant table. Instead, information is split across many small tables, each holding one kind of thing. This is called normalization — it avoids repeating the same data over and over.

What a JOIN actually is

A JOIN is a way to combine rows from two (or more) tables based on a related column between them. That related column is usually a foreign key in one table pointing to a primary key in another.

When to use it

• Whenever the answer to your question needs data that lives in more than one table.
• Whenever you want to enrich raw IDs (like dept_id = 3) with human-readable data (like "Engineering").
• Whenever you need to check relationships — who reports to whom, which orders belong to which customer, etc.

When NOT to use it

• When all the data you need already lives in one table — joining unnecessarily just slows the query down.
• When you only need to check existence of a related row (does this customer have any order at all?) — often an EXISTS subquery is faster and clearer than a JOIN, because a JOIN can multiply rows while EXISTS just answers yes/no. We'll cover this in the Subqueries module.

The schema and data we'll use for this entire module

We'll reuse these two tables — employees and departments — for every join type, so you see how the exact same data behaves differently under each join. The data is deliberately messy: it has an employee with no department, a department with no employees, duplicate names, and a self-referencing manager column.

DDL

CREATE TABLE departments (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(50) NOT NULL,
    location    VARCHAR(50)
);

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(50) NOT NULL,
    dept_id     INT,              -- can be NULL: not every employee is assigned yet
    manager_id  INT,              -- self-referencing FK to emp_id, can be NULL (top of hierarchy)
    salary      DECIMAL(10,2),
    hire_date   DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

INSERT statements

INSERT INTO departments (dept_id, dept_name, location) VALUES
(1, 'Engineering', 'Bangalore'),
(2, 'Sales',       'Mumbai'),
(3, 'Marketing',   'Delhi'),
(4, 'HR',          'Bangalore'),
(5, 'Finance',     'Pune'),
(6, 'Legal',       'Delhi');   -- Legal has ZERO employees on purpose

INSERT INTO employees (emp_id, emp_name, dept_id, manager_id, salary, hire_date) VALUES
(101, 'Arjun Mehta',    1,    NULL, 185000, '2019-03-01'),  -- CTO, no manager (top of tree)
(102, 'Priya Nair',     1,    101,  145000, '2020-01-15'),
(103, 'Rohit Sharma',   1,    101,  145000, '2020-06-10'),  -- tie salary with Priya
(104, 'Sneha Kapoor',   1,    102,  98000,  '2021-02-20'),
(105, 'Karan Verma',    2,    NULL, 160000, '2018-11-05'),  -- Sales head
(106, 'Ananya Iyer',    2,    105,  92000,  '2021-07-01'),
(107, 'Vikram Singh',   2,    105,  92000,  '2022-01-10'),  -- tie salary with Ananya
(108, 'Rohit Sharma',   2,    105,  88000,  '2022-03-15'),  -- DUPLICATE NAME, different person
(109, 'Meera Pillai',   3,    NULL, 110000, '2019-08-01'),
(110, 'Aditya Rao',     3,    109,  75000,  '2023-01-05'),
(111, 'Divya Menon',    4,    NULL, 105000, '2020-04-01'),
(112, 'Farhan Khan',    4,    111,  60000,  '2023-05-20'),
(113, 'Ishita Bose',    5,    NULL, 120000, '2019-09-15'),
(114, 'Nikhil Joshi',   5,    113,  70000,  '2022-08-01'),
(115, 'Tanvi Desai',    NULL, NULL, 55000,  '2024-01-10'),  -- NOT assigned to any dept yet
(116, 'Sameer Gupta',   NULL, NULL, 52000,  '2024-02-14');  -- another unassigned employee

INNER JOIN

What it is: returns only the rows where the join condition matches on both sides. If an employee has no matching department, or a department has no matching employee, those rows are dropped.

Why it exists: most of the time you only care about complete, matched pairs — "give me employees together with their department name" only makes sense for employees who actually have a department.

Easy example

Find employee name and department name, for employees who have a department.

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.dept_id;

Line by line:

• FROM employees e — start reading from the employees table, alias it e so we can refer to it shorter.
• INNER JOIN departments d — bring in the departments table, alias d.
• ON e.dept_id = d.dept_id — this is the matching rule: only keep pairs where the department id lines up.
• Result: 14 rows (Tanvi and Sameer are dropped because their dept_id is NULL and can't match anything; Legal is dropped because no employee points to dept 6).

How the database executes this internally (conceptually)

Logically, the engine considers the Cartesian product first — every employee row paired with every department row (16 × 6 = 96 combinations) — and then keeps only the pairs where e.dept_id = d.dept_id is TRUE. In practice, no real database is dumb enough to actually build all 96 rows; the query optimizer picks a smarter physical strategy (nested loop, hash join, or merge join — covered in Level 3). But the logical result is always as if it did the full cross product first and filtered after.

LEFT JOIN (LEFT OUTER JOIN)

What it is: returns all rows from the left table, plus matching rows from the right table. If there's no match on the right, the right side's columns come back as NULL — but the left row is never dropped.

Why it exists: sometimes "no match" is itself important information. You want to know which employees don't have a department yet, not just hide them.

Easy example

SELECT e.emp_name, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d
    ON e.dept_id = d.dept_id;

Result: all 16 employees appear. Tanvi and Sameer show dept_name = NULL because they have no matching department — but they're still in the output.

Medium example — finding "orphans"

The most powerful real use of LEFT JOIN: find rows in the left table that have no match at all.

SELECT e.emp_name
FROM employees e
LEFT JOIN departments d
    ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;   -- keep only the rows where the join failed to find a match

Result: Tanvi Desai, Sameer Gupta. This "LEFT JOIN + WHERE right.key IS NULL" pattern is called an anti-join pattern and shows up constantly in production data-quality checks (e.g. "find orders with no matching customer").

RIGHT JOIN & FULL OUTER JOIN

RIGHT JOIN

What it is: the mirror image of LEFT JOIN — keeps all rows from the right table, and NULLs out the left side where there's no match.

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d
    ON e.dept_id = d.dept_id;

Result: Legal (dept 6) now appears with emp_name = NULL, because RIGHT JOIN keeps every department even if no employee belongs to it.

FULL OUTER JOIN

What it is: keeps everything from both sides. If a row matches, you get the combined row. If it doesn't match on either side, that side is padded with NULLs.

SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.dept_id = d.dept_id;

Result: 18 rows — all 16 employees (including Tanvi and Sameer with NULL dept_name) PLUS Legal appearing once with NULL emp_name.

CROSS JOIN & SELF JOIN

CROSS JOIN

What it is: every row from table A paired with every row from table B. No ON condition at all. If A has m rows and B has n rows, the result has m × n rows.

SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- 16 employees x 6 departments = 96 rows

When to use it: generating all combinations — e.g. every product × every size × every color, or every date in a calendar × every store (to build a report scaffold with zero-filled rows for days with no sales).

SELF JOIN

What it is: a table joined to itself, using two different aliases to treat it as if it were two tables. Used whenever a row references another row in the same table — like an employee referencing their manager, who is also an employee.

SELECT
    e.emp_name  AS employee,
    m.emp_name  AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.emp_id;

Line by line: we treat the employees table as two roles at once — e plays "the employee", m plays "the manager". We LEFT JOIN so that top-level people (like Arjun, whose manager_id is NULL) still show up, with manager = NULL.

Multi-table joins

Real queries rarely join just two tables. You chain joins, left to right, and each one narrows or widens the working row set.

SELECT
    e.emp_name,
    d.dept_name,
    m.emp_name AS manager_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN employees m   ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

Hard example — three-way join with aggregation

Find, for each department, the average salary and the department's own manager count (how many distinct managers exist among its employees).

SELECT
    d.dept_name,
    ROUND(AVG(e.salary), 2)      AS avg_salary,
    COUNT(DISTINCT e.manager_id) AS distinct_managers
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

Notice we started FROM departments this time (not employees) so that Legal still shows up in the output, with avg_salary = NULL and distinct_managers = 0.

Advanced Join Syntax: USING, NATURAL, LATERAL / APPLY

ON vs USING

USING(column_name) is shorthand for an equi-join where both sides have a column with the identical name.

-- ON version
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- USING version (only works because both tables literally name the column "dept_id")
SELECT emp_name, dept_name
FROM employees e
JOIN departments d USING (dept_id);

Output column behavior: with USING, the join key appears in the result once, merged — you can select bare dept_id instead of having to disambiguate e.dept_id vs d.dept_id. With ON, both sides' columns still exist separately and you must qualify which one you mean if you select it.

NATURAL JOIN

What it does: automatically joins on every column pair that shares the same name across both tables — no ON or USING needed at all.

SELECT *
FROM employees e
NATURAL JOIN departments d;
-- Automatically joins on every identically-named column between the two tables

LATERAL JOIN / APPLY

What it is: a join where the right-hand subquery is allowed to reference columns from the left-hand table — something a normal subquery in FROM can't do. Each row of the left table effectively "reruns" the right-hand subquery with that row's values plugged in.

• PostgreSQL: LATERAL
• SQL Server: CROSS APPLY, OUTER APPLY
• Oracle: CROSS APPLY, OUTER APPLY (also supports LATERAL)

Use case — top N rows per parent (latest order per customer):

-- PostgreSQL-style LATERAL
SELECT c.customer_id, recent.order_id, recent.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- references the outer row: only a normal subquery can't do this
    ORDER BY o.order_date DESC
    LIMIT 1
) recent;

-- SQL Server-style CROSS APPLY (same idea)
SELECT c.customer_id, recent.order_id, recent.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP 1 o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) recent;

Use case — correlated subquery in FROM: anywhere you'd want to write a correlated subquery but need more than one output column, LATERAL/APPLY lets you do it as a proper join instead of cramming everything into a scalar subquery.

Use case — explode JSON/arrays per row: LATERAL is also the standard way to join each row to the exploded elements of its own JSON array or array column (e.g. CROSS JOIN LATERAL jsonb_array_elements(...) in Postgres).

CROSS APPLY vs OUTER APPLY

Same relationship as CROSS JOIN vs LEFT JOIN: CROSS APPLY drops the outer row entirely if the correlated subquery returns nothing (like an inner join), while OUTER APPLY keeps the outer row with NULLs when the subquery returns nothing (like a left join). PostgreSQL's LEFT JOIN LATERAL ... ON true is the equivalent of OUTER APPLY.

Null-Safe and Composite-Key Joins

Null-safe joins

Why NULL = NULL does not match: as covered earlier, any comparison against NULL evaluates to UNKNOWN, not TRUE — so a plain ON a.key = b.key never matches two NULL keys, even when that's actually the behavior you want (e.g. treating "no manager" as a valid matching state).

• PostgreSQL: ON a.key IS NOT DISTINCT FROM b.key
• MySQL: ON a.key <=> b.key (the null-safe equal operator)
• BigQuery: ON a.key IS NOT DISTINCT FROM b.key
• Manual/portable pattern (works everywhere): ON (a.key = b.key OR (a.key IS NULL AND b.key IS NULL))

-- Portable null-safe join pattern
SELECT e.emp_name, m.emp_name AS manager_name
FROM employees e
JOIN employees m
    ON (e.manager_id = m.emp_id OR (e.manager_id IS NULL AND m.emp_id IS NULL));

Composite-key joins

What it is: joining on more than one column at once, because no single column uniquely identifies a row on its own.

SELECT *
FROM order_line_history a
JOIN order_line_history b
    ON a.customer_id = b.customer_id
   AND a.order_date  = b.order_date;

Common production examples: tenant_id + id in multi-tenant SaaS systems (an id alone is only unique within a tenant), country + state for geographic lookups, order_id + line_id for order line items.

Data Type Mismatch & Function-Based Joins

Data type mismatch joins

What it is: joining a column typed as one thing (say, INT) to a column typed as another (say, VARCHAR) — common after schema drift, sloppy ETL, or two systems that modeled the "same" id differently.

-- a.user_id is INT, b.user_id is VARCHAR
SELECT *
FROM a
JOIN b ON CAST(a.user_id AS VARCHAR) = b.user_id;

Most engines will also perform an implicit cast for you if you just write ON a.user_id = b.user_id across mismatched types — the query still "works", but silently, and not always the way you'd expect (e.g. string-to-number implicit casts can throw errors or truncate on non-numeric strings, depending on engine).

Function-based joins

Case-insensitive join:

SELECT *
FROM users_a a
JOIN users_b b ON LOWER(a.email) = LOWER(b.email);

Date truncation join (matching a precise timestamp to a coarser calendar grain):

SELECT *
FROM orders o
JOIN calendar_dim d ON DATE(o.created_at) = d.calendar_date;

Join Filtering: ON vs WHERE Deep Dive

You met the shallow version of this in the LEFT JOIN section. Here's the full picture for every join type.

For INNER JOIN

It doesn't matter — putting a filter in ON or in WHERE produces the same result for an INNER JOIN, because INNER JOIN only keeps rows that already survive both the join condition and any additional filter, regardless of which clause holds it. Style guides differ on which is more "correct" to write, but the output is identical.

For LEFT JOIN — filtering left table vs filtering right table

Filtering the left (preserved) table works the same in either clause — the left table's rows still get filtered before or after the join either way.

Filtering the right table is where it matters. A condition on the right table in WHERE is evaluated after the LEFT JOIN has already padded unmatched rows with NULLs — and a NULL right-side column almost never satisfies a WHERE condition, so those "no match" rows get thrown away. That turns your LEFT JOIN back into effectively an INNER JOIN.

-- BUG: this silently behaves like an INNER JOIN
SELECT f.*, d.status
FROM fact f
LEFT JOIN dim d ON f.id = d.id
WHERE d.status = 'active';
-- Rows where f has no matching d row get d.status = NULL,
-- and 'NULL = ''active''' is UNKNOWN, so those rows are dropped by WHERE.

-- CORRECT: keep the filter inside ON so unmatched left rows survive
SELECT f.*, d.status
FROM fact f
LEFT JOIN dim d
    ON f.id = d.id
   AND d.status = 'active';
-- Now: matched rows with status='active' keep d's columns,
-- unmatched rows (or matched rows where status != 'active') still appear, with d.* as NULL.

Non-equi joins

What it is: a join where the condition isn't a plain equality (=) — instead it uses <, >, BETWEEN, etc.

When to use it: salary-band lookups, date-range matching, geographic containment — anything where "belongs to a range" replaces "equals an id".

Setup: a salary bands table

CREATE TABLE salary_bands (
    band_name VARCHAR(20),
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2)
);

INSERT INTO salary_bands VALUES
('Junior',  50000,  89999),
('Mid',     90000,  129999),
('Senior',  130000, 169999),
('Staff',   170000, 999999);

SELECT e.emp_name, e.salary, b.band_name
FROM employees e
JOIN salary_bands b
    ON e.salary BETWEEN b.min_salary AND b.max_salary;

SEMI JOIN & ANTI JOIN

These aren't separate SQL keywords in standard SQL — they're patterns, usually written with EXISTS/NOT EXISTS or IN/NOT IN, or sometimes simulated with a LEFT JOIN.

SEMI JOIN — "does a match exist?"

What it is: return rows from table A where at least one match exists in table B — but never duplicate A's rows even if B has multiple matches, and never pull any columns from B.

-- Departments that have at least one employee
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

Compare this to a naive INNER JOIN version:

-- BAD for this purpose: returns Engineering 4 times (once per employee)
SELECT DISTINCT d.dept_name
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id;

The INNER JOIN version needs a DISTINCT bolted on to fix the duplication it caused. The EXISTS version never had that problem, because it only checks "does a match exist", it never actually multiplies rows.

ANTI JOIN — "no match exists"

-- Departments with ZERO employees (Legal)
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

How joins execute internally

The database's query optimizer picks one of three physical strategies to actually run a logical join. Knowing these separates people who memorized syntax from people who understand databases.

1. Nested Loop Join

For every row in the outer (usually smaller) table, scan the inner table looking for matches. Simple, but O(n × m) in the worst case — unless the inner table has an index on the join column, which turns the inner scan into a fast lookup (O(n × log m) or better).

2. Hash Join

Build a hash table in memory from the smaller table, keyed on the join column. Then scan the bigger table once, and for each row, do an O(1) hash lookup into that hash table. Overall roughly O(n + m).

3. Merge Join (Sort-Merge Join)

If both tables are already sorted on the join key (or the optimizer sorts them first), the engine can walk both sorted lists with two pointers simultaneously, like merging two sorted decks of cards. Very efficient — O(n + m) after sorting.

Cardinality & the duplicate explosion trap

"Cardinality" of a join describes the relationship between matching rows: one-to-one, one-to-many, or many-to-many.

• One-to-one: each row on both sides matches exactly one row on the other side. Row count stays the same after the join.
• One-to-many: one row on the "one" side matches multiple rows on the "many" side. Example: one department matches multiple employees. The department's row gets repeated once per matching employee.
• Many-to-many: multiple rows on both sides can match each other. Row count can explode multiplicatively.

Concrete demo with our data

Departments-to-employees is one-to-many. Watch what happens if we naively try to compute "total company salary" while joined to employees without pre-aggregating:

-- WRONG idea if departments itself had a "budget" column and we joined + summed budget
-- naive: SELECT SUM(d.budget) FROM departments d JOIN employees e ON d.dept_id = e.dept_id;
-- This would count each department's budget once PER EMPLOYEE in that department — inflated!

A. Join + Aggregation traps (the #1 production SQL bug)

You already met this idea in the cardinality section. Now let's make it concrete with the schema every data engineer eventually deals with: orders (one row per order) and order_items (many rows per order — one per line item).

DDL & sample data

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    order_date   DATE,
    total_amount DECIMAL(10,2)   -- the order's true total, computed once at checkout
);

CREATE TABLE order_items (
    item_id     INT PRIMARY KEY,
    order_id    INT,
    product_id  VARCHAR(10),   -- e.g. 'P1', 'P2' — not a numeric id
    quantity    INT,
    unit_price  DECIMAL(10,2)
);

INSERT INTO orders VALUES
(5001, 201, '2024-01-10', 2500.00),
(5002, 202, '2024-01-11', 1200.00),
(5003, 201, '2024-01-15',  800.00);

INSERT INTO order_items VALUES
(1, 5001, 'P1', 2, 1000.00),   -- order 5001 has 3 line items
(2, 5001, 'P2', 1,  500.00),
(3, 5001, 'P3', 1,  500.00),
(4, 5002, 'P1', 1, 1200.00),   -- order 5002 has 1 line item
(5, 5003, 'P4', 2,  400.00);   -- order 5003 has 1 line item

Real total revenue = 2500 + 1200 + 800 = 4500.00

Trap 1 — the naive join-then-sum

-- WRONG
SELECT SUM(o.total_amount) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;
-- Result: 8300.00  (should be 4500.00)

Why it's wrong: order 5001 has 3 line items, so the JOIN produces 3 output rows for it — and each of those 3 rows still carries total_amount = 2500.00. Summing that column now adds 2500 three times instead of once. Order 5001 alone contributes 2500 × 3 = 7500 to the wrong sum instead of 2500. This is the single most common "why is our dashboard revenue number wrong" bug in real companies.

Trap 2 — "just add DISTINCT" (still wrong, or fragile)

-- STILL WRONG, and fragile
SELECT SUM(DISTINCT o.total_amount) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;
-- Works here by accident, but breaks the moment two DIFFERENT orders share the same total_amount

If order 5002 and a hypothetical order 5004 both happened to total exactly 1200.00, SUM(DISTINCT ...) would collapse them into one and undercount. DISTINCT deduplicates by value, not by which row it came from — never use it as a duplication fix for aggregates.

Trap 3 — the correct fix: pre-aggregate before joining

-- CORRECT: don't even join to order_items if you only need the order total
SELECT SUM(o.total_amount) AS revenue
FROM orders o;
-- Result: 4500.00 ✓

If you actually need something from order_items (say, total quantity sold) alongside the order total, aggregate order_items down to one row per order first, in a subquery/CTE, then join — never join first and aggregate the "one" side's column after.

-- CORRECT: pre-aggregate the "many" side to order-grain BEFORE joining
SELECT
    o.order_id,
    o.total_amount,
    item_totals.total_qty
FROM orders o
JOIN (
    SELECT order_id, SUM(quantity) AS total_qty
    FROM order_items
    GROUP BY order_id
) item_totals ON o.order_id = item_totals.order_id;

Now the join is one-to-one (order to its own pre-aggregated summary), so o.total_amount is never duplicated.

Trap 4 — the same bug hiding inside AVG()

-- WRONG: looks innocent, still broken
SELECT o.customer_id, AVG(o.total_amount) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.customer_id;
-- Customer 201's order 5001 (weight x3 due to 3 items) skews the average toward 2500

AVG isn't safe just because it "averages things out" — it's still averaging over duplicated rows, so orders with more line items get more influence on the average than orders with fewer line items. The fix is identical: pre-aggregate order_items to order-grain first, or simply compute the average directly from the un-joined orders table.

B. Slowly Changing Dimension (SCD Type 2) joins

What it is: in a data warehouse, dimension attributes change over time (an employee's salary band changes, a product's price changes, a customer moves cities). SCD Type 2 keeps every historical version of a dimension row, each tagged with an effective_start and effective_end date, instead of overwriting the old value.

Why it exists: if you overwrote the dimension row in place (SCD Type 1), historical fact rows would silently be re-attributed to the current value — a sale made in 2022 at an old price would suddenly look like it used today's price when you re-run a report. SCD2 preserves "what was true at the time the fact happened."

DDL & sample data

CREATE TABLE dim_customer_scd2 (
    customer_sk     INT PRIMARY KEY,   -- surrogate key: unique per VERSION of the customer
    customer_id     INT,               -- natural/business key: same across all versions
    customer_name   VARCHAR(50),
    city            VARCHAR(50),
    effective_start DATE,
    effective_end   DATE,              -- '9999-12-31' means "currently active"
    is_current      BOOLEAN
);

INSERT INTO dim_customer_scd2 VALUES
(1, 301, 'Rahul Bansal', 'Delhi',     '2022-01-01', '2023-06-30', FALSE),  -- old version
(2, 301, 'Rahul Bansal', 'Bangalore', '2023-07-01', '9999-12-31', TRUE),   -- current version
(3, 302, 'Neha Kulkarni','Pune',      '2021-05-01', '9999-12-31', TRUE);

CREATE TABLE fact_sales (
    sale_id     INT PRIMARY KEY,
    customer_id INT,
    sale_date   DATE,
    amount      DECIMAL(10,2)
);

INSERT INTO fact_sales VALUES
(9001, 301, '2023-02-14', 5000.00),  -- happened while Rahul was in Delhi
(9002, 301, '2023-09-01', 3000.00),  -- happened while Rahul was in Bangalore
(9003, 302, '2022-03-10', 1500.00);

The SCD2 join — matching a fact to the dimension version active on that date

SELECT
    f.sale_id,
    f.sale_date,
    f.amount,
    d.city AS city_at_time_of_sale
FROM fact_sales f
JOIN dim_customer_scd2 d
    ON f.customer_id = d.customer_id
   AND f.sale_date BETWEEN d.effective_start AND d.effective_end;

Result: sale 9001 (Feb 2023) correctly joins to Rahul's Delhi version; sale 9002 (Sep 2023) correctly joins to his Bangalore version — even though both facts share the same customer_id. This is a non-equi join (from Level 3) applied to real dimensional modeling.

C. Range joins with dates — price validity example

The same non-equi range-join pattern from salary bands and SCD2 shows up constantly for "what was the price/rate/rule in effect on date X" problems — a product_price_history table is the classic example.

CREATE TABLE product_price_history (
    price_id    INT PRIMARY KEY,
    product_id  VARCHAR(10),
    price       DECIMAL(10,2),
    valid_from  DATE,
    valid_to    DATE   -- '9999-12-31' if still the current price
);

INSERT INTO product_price_history VALUES
(1, 'P1', 1000.00, '2023-01-01', '2023-12-31'),
(2, 'P1', 1100.00, '2024-01-01', '9999-12-31'),   -- price increase
(3, 'P4',  380.00, '2023-06-01', '2024-02-28'),
(4, 'P4',  400.00, '2024-03-01', '9999-12-31');

-- Attach the price that was valid on each order_item's order date
SELECT
    oi.item_id,
    oi.product_id,
    o.order_date,
    pph.price AS price_at_order_time
FROM order_items oi
JOIN orders o              ON oi.order_id = o.order_id
JOIN product_price_history pph
    ON oi.product_id = pph.product_id
   AND o.order_date BETWEEN pph.valid_from AND pph.valid_to;

C.1 ASOF / Nearest-Time Joins

What it is: joining each event to the most recent previous row in another table — not an exact timestamp match, and not a range with a fixed end like the price-history example above, just "whatever was true immediately before this moment."

Difference from a normal date range join: a range join (like the price-history one) matches against an explicit valid_from/valid_to window that was pre-computed. An ASOF join has no explicit "valid_to" — it just wants the single most recent row at or before the event time, computed on the fly.

Pattern using LATERAL / window functions

-- LATERAL approach: for each trade, find the latest quote at or before trade_time
SELECT
    t.trade_id,
    t.trade_time,
    t.symbol,
    q.quote_time,
    q.price AS quote_price
FROM trades t
CROSS JOIN LATERAL (
    SELECT q.quote_time, q.price
    FROM quotes q
    WHERE q.symbol = t.symbol
      AND q.quote_time <= t.trade_time
    ORDER BY q.quote_time DESC
    LIMIT 1
) q;

-- Window-function approach (no LATERAL): tag each quote with its "next quote time",
-- turning it into a range join against an explicit window
SELECT
    t.trade_id, t.trade_time, q.price AS quote_price
FROM trades t
JOIN (
    SELECT
        symbol, price, quote_time,
        LEAD(quote_time) OVER (PARTITION BY symbol ORDER BY quote_time) AS next_quote_time
    FROM quotes
) q
    ON t.symbol = q.symbol
   AND t.trade_time >= q.quote_time
   AND (t.trade_time < q.next_quote_time OR q.next_quote_time IS NULL);

D. Many-to-many bridge tables

What it is: a many-to-many relationship (one student can take many courses, one course can have many students) can't be modeled with a single foreign key on either side. The fix is a third table — a bridge (a.k.a. junction/associative) table — holding one row per (student, course) pairing.

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id   INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (       -- the bridge table
    student_id INT,
    course_id  INT,
    enrolled_on DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

INSERT INTO students VALUES (1,'Aarav'), (2,'Diya'), (3,'Kabir');
INSERT INTO courses  VALUES (10,'Databases'), (20,'Algorithms'), (30,'Statistics');

INSERT INTO student_courses VALUES
(1, 10, '2024-01-05'),   -- Aarav takes Databases
(1, 20, '2024-01-05'),   -- Aarav also takes Algorithms
(2, 10, '2024-01-06'),   -- Diya takes Databases
(3, 30, '2024-01-07');   -- Kabir takes Statistics only
-- Notice: Algorithms(20) has only 1 student, Statistics(30) has only 1, nobody takes nothing (Kabir isn't in Algorithms)

Easy example — list each student with each of their course names

SELECT s.student_name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c          ON sc.course_id = c.course_id;

Two joins in a row: student → bridge → course. This double-hop is the defining shape of every bridge-table query.

Medium example — students enrolled in NEITHER Databases nor Algorithms

SELECT s.student_name
FROM students s
WHERE s.student_id NOT IN (
    SELECT sc.student_id
    FROM student_courses sc
    JOIN courses c ON sc.course_id = c.course_id
    WHERE c.course_name IN ('Databases', 'Algorithms')
);
-- Result: Kabir (he's only in Statistics)

Hard example — courses with zero students (need a LEFT JOIN through the bridge)

SELECT c.course_name
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.student_id IS NULL;
-- With this data, every course has at least one student, so this returns empty — 
-- add a course with no enrollments to see it trigger.

E. Join elimination (optimizer internals)

What it is: a query optimization where the database engine detects that a join in your query is unnecessary to produce the requested output, and silently removes it from the execution plan — without changing the result.

When it happens: most commonly when you join to a table only to filter or just to prove a row exists, but never actually select any column from it, AND the join is provably safe to skip — for example, joining on a foreign key that's guaranteed unique and non-null (so the join can't duplicate or drop rows).

-- You wrote this:
SELECT e.emp_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- If dept_id has a NOT NULL foreign key constraint referencing departments.dept_id (guaranteed to exist and be unique),
-- a smart optimizer can recognize that the join to `departments` can never filter out
-- or duplicate any row of `employees` -- since every non-null dept_id is guaranteed to match exactly one row --
-- so it eliminates the join entirely and just scans employees.

F. Broadcast joins (distributed warehouse concept)

What it is: in distributed engines like Apache Spark, Snowflake, or BigQuery, data is split across many worker nodes. A normal join between two large distributed tables requires shuffling — physically moving rows across the network so matching keys land on the same node. Shuffling is expensive.

The trick: if one side of the join is small (a dimension table that fits comfortably in memory — say, a few MB to a few hundred MB), the engine can instead copy ("broadcast") that entire small table to every worker node. Then each node does a local, in-memory join against its slice of the big fact table — no shuffling of the huge table required at all.

-- Conceptual Spark SQL hint (syntax varies by engine):
SELECT /*+ BROADCAST(d) */
    f.sale_id, f.amount, d.category
FROM fact_sales f
JOIN dim_product d ON f.product_id = d.product_id;
-- dim_product is small (e.g. a few thousand rows) -> broadcast it to every node
-- fact_sales is huge (billions of rows) -> stays put, never shuffled

G. Skewed joins & salting

What it is: in a distributed shuffle join, rows are partitioned across worker nodes based on a hash of the join key. If one key value is wildly overrepresented (say, 90% of your fact_sales rows have customer_id = 999 — a bulk "guest checkout" account), every one of those rows hashes to the same worker node. That one node ends up doing 90% of the work while every other node sits idle — a classic data skew bottleneck.

Recognizing skew

-- Diagnostic: check for skew before blaming "the join is just slow"
SELECT customer_id, COUNT(*) AS row_count
FROM fact_sales
GROUP BY customer_id
ORDER BY row_count DESC
LIMIT 5;
-- If the #1 row dwarfs the rest (e.g. 90M rows vs a normal customer's 200 rows), that's skew.

The fix: salting

Salting artificially splits an overloaded key into several fake sub-keys, spreading its rows across multiple worker nodes, then joins against a correspondingly "exploded" copy of the small side, and finally removes the salt.

-- Step 1: add a random salt (0-9) to the skewed fact table's join key
SELECT
    f.*,
    CONCAT(f.customer_id, '_', CAST(FLOOR(RAND() * 10) AS INT)) AS salted_customer_id
FROM fact_sales f;

-- Step 2: explode the small dimension side so every real customer_id
-- exists once for each possible salt value (0-9), matching the scheme above
SELECT
    d.*,
    CONCAT(d.customer_id, '_', salt.n) AS salted_customer_id
FROM dim_customer d
CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
            UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
            UNION ALL SELECT 8 UNION ALL SELECT 9) salt;

-- Step 3: join on the salted key instead of the raw key.
-- The skewed customer's rows are now spread across 10 different salted keys,
-- landing on up to 10 different worker nodes instead of piling onto one.

H. Distributed Join Types

Broadcast joins and skewed/salted joins (above) are two specific tools. Here's the full menu of physical join strategies a distributed engine (Spark, Snowflake, Trino, BigQuery) picks between:

• Shuffle join: both sides are re-partitioned across the cluster by the join key so matching keys land on the same node, then joined locally. The default, general-purpose strategy — but the shuffle itself (moving data across the network) is expensive.
• Broadcast/hash join: covered above — copy a small side to every node, avoid shuffling the large side entirely.
• Sort-merge join: both sides are sorted by the join key (locally or via shuffle-then-sort) and merged with two pointers, like the single-node merge join but distributed. Common default for large-to-large equi-joins where both sides are already sorted/bucketed.
• Co-located/bucketed join: if both tables were pre-bucketed/partitioned by the same key at write time (e.g. Spark bucketing, Hive bucketing), rows that would match are already sitting on the same node — no shuffle is needed at join time at all. This is the cheapest possible distributed join, but requires the tables to be physically laid out that way in advance.
• Skew join: a shuffle join with special handling for one or more overloaded keys — covered above via salting or engine-native adaptive handling.
• Salting: the specific technique for manually fixing skew — covered above.
• Adaptive query execution (AQE): a runtime feature (Spark 3+, and equivalents elsewhere) where the engine re-optimizes the join strategy during execution based on actual observed data sizes and skew — for example, automatically converting a shuffle join to a broadcast join if runtime statistics show one side turned out smaller than the static plan assumed, or automatically splitting skewed partitions.

I. Partition- and Cluster-Aware Joins

Partition pruning: if a table is physically partitioned by a column (commonly a date), and your join or filter includes a condition on that column, the engine can skip reading entire partitions that can't possibly match — dramatically cutting the amount of data scanned before the join even starts.

-- fact_sales is partitioned by sale_date
SELECT f.sale_id, d.category
FROM fact_sales f
JOIN dim_product d ON f.product_id = d.product_id
WHERE f.sale_date >= '2026-07-01';
-- The engine only reads partitions for sale_date >= 2026-07-01,
-- skipping every earlier partition entirely, before doing any join work.

Clustering keys: in warehouses that support them (Snowflake clustering keys, BigQuery clustering columns), data within each micro-partition is kept roughly sorted/grouped by the clustering key, which lets the engine skip micro-partitions the same way partition pruning skips whole partitions — useful when joining or filtering on a high-cardinality column that isn't the partition column itself.

Co-located joins & bucketing in Spark/Hive: if two tables are bucketed into the same number of buckets by the same join key, rows that would match are guaranteed to live in the same bucket/node — the engine can join bucket-by-bucket with zero shuffle. This is the same idea introduced as "co-located/bucketed join" above, but from the storage-design side: you have to explicitly create the tables this way (CLUSTERED BY (key) INTO n BUCKETS in Hive/Spark) for it to apply.

Distribution keys in Redshift/Synapse: similar concept under a different name — declaring a table's DISTKEY (Redshift) or distribution column (Synapse) as the common join key means matching rows are stored on the same compute node, avoiding a shuffle at query time.

Join Indexing Strategy

• Index foreign keys. Most databases index primary keys automatically, but not foreign keys — you almost always want to add one yourself on any column used as a join key, especially the "many" side of a one-to-many relationship (e.g. employees.dept_id).
• Index join keys on both sides when useful. A hash join benefits from neither side needing an index, but nested loop and merge join strategies benefit heavily from an index on the inner/probed side — and sometimes both sides if the optimizer might flip which side drives the scan.
• Composite indexes for composite joins. Match the index column order to how the join condition (and any additional filters) actually use the columns — see the composite-key join section above.
• Covering indexes. An index that includes every column the query needs (join key plus selected columns) lets the engine answer entirely from the index without a separate lookup into the full table row — a significant speedup for hot join paths.
• Index selectivity. Selectivity measures how many distinct values a column has relative to row count. A low-cardinality column (e.g. a boolean is_active flag with only 2 values) makes a poor index on its own — an index scan across half the table isn't much better than a full scan. Join keys tend to be highly selective (each customer_id matches relatively few rows) and are exactly where indexes pay off most.
• Clustered vs non-clustered index effects. A clustered index physically orders the table's rows on disk by that key — a join on the clustered key can be extremely fast (sequential access) but a table only gets one clustered index. A non-clustered index is a separate lookup structure pointing back to the row, faster to add but with an extra lookup hop for columns not included in the index itself.
• Warehouse clustering/partitioning keys serve a similar role to indexes in columnar warehouses (Snowflake, BigQuery) that don't use traditional B-tree indexes — see the Partition- and Cluster-Aware Joins section above.

Fact-to-Dimension Joins (Star Schema)

In a star schema, a large fact table (one row per event — a sale, a click, a trade) sits at the center, joined out to several smaller dimension tables (product, customer, date, store) that describe the "who/what/where" of each event.

• Fact table grain: the precise meaning of "one row" in the fact table (e.g. one row = one line item on one order) — every join and aggregation decision should start from stating the grain explicitly.
• Dimension table uniqueness: a well-formed dimension table has exactly one row per natural key (one row per product_id) — if it doesn't, joining the fact table to it causes fanout (see Handling Duplicate Dimension Rows below).
• Late-arriving dimensions: a fact row referencing a dimension key that hasn't loaded yet — see Late-Arriving Facts and Dimensions below.
• Unknown/default dimension row: best practice is to insert one explicit placeholder row per dimension table (e.g. product_id = -1, category = 'UNKNOWN') and point orphaned fact rows at it via a default, instead of leaving NULLs scattered through downstream reports.

Referential Integrity and Constraints

• Primary key uniqueness: guarantees a table has at most one row per key — the foundation that makes a join to that table "safe" (non-duplicating) on that key.
• Foreign key constraints: guarantee every value in the child column actually exists in the referenced parent table (or is NULL, if nullable) — this is what prevents "orphan" rows at the database level, rather than relying on application code to enforce it.
• Missing constraints causing duplicate joins: without a real UNIQUE/PRIMARY KEY constraint on the dimension side, nothing stops a duplicate row from silently sneaking in and fanning out every fact join against it — the database won't reject the bad insert, and the bug only surfaces later as inflated report numbers.
• Optimizer benefits of trusted constraints: a query optimizer that trusts your declared constraints can make stronger guarantees — e.g. "this join can't duplicate rows because the right side has a unique constraint on the join column" — which unlocks better plans and even join elimination (below).
• Join elimination depends on constraints: covered earlier in this module — the optimizer can only safely skip a join it can prove is unnecessary, and that proof usually comes from a declared, trusted foreign key + uniqueness constraint, not just "the data happens to always be clean."

Row-Count Testing After Joins

The single cheapest, highest-value habit for catching join bugs before they reach a dashboard: compare row counts before and after every non-trivial join.

-- Before/after row counts — expected cardinality check
SELECT COUNT(*) AS before_join FROM fact_sales;

SELECT COUNT(*) AS after_join
FROM fact_sales f
LEFT JOIN dim_product d ON f.product_id = d.product_id;
-- If after_join > before_join, the join fanned out -- the "one" side isn't actually unique.
-- If after_join < before_join, you probably meant LEFT JOIN and wrote INNER JOIN, or a
-- filter is silently discarding unmatched rows.

Detecting accidental fanout at the source, before it even reaches your join — check for duplicate keys in the dimension you're about to join to:

SELECT product_id, COUNT(*)
FROM dim_product
GROUP BY product_id
HAVING COUNT(*) > 1;
-- Any row returned here means dim_product is NOT unique per product_id,
-- and any fact join against it on product_id WILL fan out.

Handling Duplicate Dimension Rows

Why duplicate dimension keys cause fact fanout: if dim_product has two rows for product_id = 'P1' (say, an old and a corrected record that both made it into the table), every fact row for P1 now matches both dimension rows — doubling that product's rows in the joined result, which then doubles its contribution to any downstream SUM/COUNT.

-- Validate uniqueness before trusting a join to this table
SELECT product_id, COUNT(*)
FROM dim_product
GROUP BY product_id
HAVING COUNT(*) > 1;

Dedup the dimension before joining — pick the latest (or otherwise canonical) row per key with ROW_NUMBER():

SELECT *
FROM (
    SELECT
        d.*,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) AS rn
    FROM dim_product d
) ranked
WHERE rn = 1;
-- Join fact tables against this deduplicated result instead of the raw dim_product table

Late-Arriving Facts and Dimensions

• Facts arriving before dimension rows: the pattern already covered in the Production Scenario later in this module — a sale references a product_id that the product dimension load hasn't inserted yet. Fix: LEFT JOIN with an explicit 'UNKNOWN' default, plus a reprocessing step once the dimension catches up.
• Dimensions changing after fact load: the opposite direction — a dimension attribute (e.g. a customer's region) changes after facts already referencing that customer were loaded and reported on. Whether historical facts should reflect the old or new value is a modeling decision (this is exactly what SCD Type 2 versioning, covered earlier in this module, is designed to answer precisely).
• Unknown surrogate key: the standard fix for late-arriving facts — maintain one reserved "unknown member" row per dimension (often surrogate key -1 or 0) so fact rows always have something valid to point at, never a dangling reference or a raw NULL.
• Backfill/reprocessing strategy: once the late dimension row finally arrives, a scheduled job re-resolves any fact rows still pointing at the unknown-member placeholder for that key, updating them to point at the real dimension row — commonly done as a daily or hourly "orphan reconciliation" pass.

Join Debugging Playbook

When a join produces a wrong number, wrong row count, or missing rows, work through this checklist in order:

1. Are join keys unique on the expected side? (run the GROUP BY key HAVING COUNT(*) > 1 check from above)
2. Are nulls expected in the join key on either side? Would a plain = silently drop them?
3. Did the row count increase after the join? (possible fanout — check for duplicate keys on the "many" side)
4. Did the row count decrease after the join? (possible missing matches — check whether you meant LEFT JOIN instead of INNER JOIN)
5. Is the join condition complete — every part of a composite key included, no accidentally-dropped clause?
6. Is a filter in WHERE accidentally removing unmatched rows from what should be a LEFT JOIN? (check every WHERE condition that touches the right-hand table)
7. Are the data types the same on both sides of the join condition, or is an implicit cast silently happening?
8. Is there a whitespace or case mismatch between the two sides (e.g. 'ACME Corp' vs 'acme corp ')?
9. For date/range joins: are the date ranges actually overlapping the way you expect, and are there overlapping ranges in the source data that shouldn't exist?

How interviewers trick people on JOINs

1. The NULL trap: they slip a NULL foreign key into the sample data (like Tanvi/Sameer here) and ask for "all employees and their department" — if you use INNER JOIN, you silently drop people, and most candidates don't even notice the row count is wrong.
2. The WHERE-vs-ON trap: they ask you to LEFT JOIN and then filter on the right table in WHERE, secretly turning it back into an INNER JOIN, and ask "why did dept X disappear?"
3. The duplicate row count trap: they ask "how many employees are there?" after a multi-table join and expect you to realize a one-to-many join inflated COUNT(*) — the correct answer needs COUNT(DISTINCT e.emp_id).
4. The self-join off-by-one trap: asking for "employee and manager's manager" (two levels up) forces you to self-join the same table twice with three aliases — many candidates fumble the aliasing.
5. The NOT IN + NULL trap: covered above — a favorite "gotcha" question is simply "what's wrong with this NOT IN query?" where the subquery column contains NULLs.
6. The implicit cross join trap: old-style comma joins with a forgotten WHERE clause. They show you a query with an unexpectedly huge row count and ask you to spot the bug.

3 Hard Interview Questions

SELECT
    e.emp_name  AS employee,
    e.salary    AS employee_salary,
    m.emp_name  AS manager,
    m.salary    AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

SELECT e.emp_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT m.salary FROM employees m WHERE m.emp_id = e.manager_id
);

SELECT
    d.dept_name,
    COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

SELECT
    e1.emp_name AS employee_1,
    e2.emp_name AS employee_2,
    e1.dept_id,
    e1.salary
FROM employees e1
JOIN employees e2
    ON e1.dept_id = e2.dept_id
   AND e1.salary  = e2.salary
   AND e1.emp_id  < e2.emp_id;

Set Comparison Using Joins

A whole family of interview questions boils down to treating two tables as sets and asking for intersection, difference, or symmetric difference — all answerable with joins alone.

• INNER JOIN for intersection: rows present in both A and B.
• LEFT JOIN ... IS NULL for "A minus B": rows in A with no counterpart in B (the anti-join pattern from the LEFT JOIN section).
• FULL OUTER JOIN for differences: everything from both sides, with NULLs marking whichever side is missing — the tool for a full reconciliation report.

Reconciliation report — rows changed between old and new snapshots

SELECT
    COALESCE(o.id, n.id)  AS id,
    o.value                AS old_value,
    n.value                AS new_value,
    CASE
        WHEN o.id IS NULL THEN 'ADDED'
        WHEN n.id IS NULL THEN 'REMOVED'
        ELSE 'CHANGED'
    END AS change_type
FROM old_table o
FULL OUTER JOIN new_table n
    ON o.id = n.id
WHERE o.id IS NULL OR n.id IS NULL OR o.value <> n.value;

Line by line: the FULL OUTER JOIN guarantees every id from both snapshots is present exactly once. o.id IS NULL means the row is new (only in the new snapshot); n.id IS NULL means it was removed (only in the old snapshot); o.value <> n.value catches rows present in both but changed. This single query answers "find rows changed between old and new snapshots", "find rows in A but not B", "find rows in both A and B", and "find matching/unmatched rows between two tables" all at once.

Top-N Per Group With Joins

"Find the latest/highest/top N rows per group" is one of the most frequently asked SQL interview patterns. There are three join-based ways to solve it, each with different tradeoffs.

1. Self-join approach

-- Top 1 (highest salary) employee per department, self-join style
SELECT e1.dept_id, e1.emp_name, e1.salary
FROM employees e1
LEFT JOIN employees e2
    ON e1.dept_id = e2.dept_id
   AND e2.salary > e1.salary
WHERE e2.emp_id IS NULL;
-- Keep only rows where NO other employee in the same department has a strictly higher salary

Pros: works in any engine, no window function support required. Cons: gets awkward fast for N > 1, and ties need extra handling (as written, this keeps all top earners if there's a tie, which may or may not be what you want).

2. Window-function approach

SELECT dept_id, emp_name, salary
FROM (
    SELECT
        emp_id, dept_id, emp_name, salary,
        RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk <= 1;   -- change to <= N for top-N

Pros: cleanly generalizes to any N, and the choice between RANK(), DENSE_RANK(), and ROW_NUMBER() gives explicit, readable control over tie behavior. Cons: requires window function support (most modern engines have it — this is the generally preferred approach today).

3. LATERAL / APPLY approach

SELECT d.dept_id, top_emp.emp_name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
    SELECT emp_name, salary
    FROM employees e
    WHERE e.dept_id = d.dept_id
    ORDER BY salary DESC
    LIMIT 1   -- LIMIT N for top-N
) top_emp;

Pros: reads very naturally ("for each department, get its top row"), and in some engines is more efficient than a window function because it can push the LIMIT down per group instead of ranking the entire table. Cons: not supported in every engine (see the LATERAL/APPLY section above for support by vendor).

Hierarchy With Joins

Fixed-depth self joins (covered earlier — manager, then manager's manager) work fine when you know exactly how many levels deep you need to go, because each level is just one more explicit self-join with its own alias.

-- Fixed 2-level lookup: works, but only for exactly 2 levels
SELECT e.emp_name, m.emp_name AS manager, gm.emp_name AS skip_level_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.emp_id
LEFT JOIN employees gm ON m.manager_id = gm.emp_id;

Trick Questions

A rapid-fire list of the "gotcha" questions interviewers reach for most often on JOINs — you should be able to answer each of these out loud, in one or two sentences, without hesitating.

2 FAANG-Level Questions

DELETE e1 FROM employees e1
JOIN employees e2
    ON e1.emp_name = e2.emp_name
   AND e1.dept_id  = e2.dept_id
   AND e1.salary   = e2.salary
   AND e1.emp_id  > e2.emp_id;   -- delete the "later" duplicate, keep the smaller id

DELETE FROM employees
WHERE emp_id IN (
    SELECT emp_id FROM (
        SELECT
            emp_id,
            ROW_NUMBER() OVER (
                PARTITION BY emp_name, dept_id, salary
                ORDER BY emp_id
            ) AS rn
        FROM employees
    ) ranked
    WHERE rn > 1
);

SELECT
    e.emp_name          AS employee,
    m.emp_name          AS manager,
    gm.emp_name         AS skip_level_manager
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.emp_id
LEFT JOIN employees gm ON m.manager_id = gm.emp_id;

SELECT e.emp_name, gm.emp_name AS skip_level_manager
FROM employees e
JOIN employees m  ON e.manager_id = m.emp_id
JOIN employees gm ON m.manager_id = gm.emp_id;

1 Production Scenario

SELECT
    s.sale_id,
    s.sale_amount,
    COALESCE(p.category, 'UNKNOWN') AS category,
    COALESCE(p.brand, 'UNKNOWN')    AS brand
FROM daily_sales s
LEFT JOIN products p ON s.product_id = p.product_id;

10 Practice Questions

Write the SQL yourself before expanding each answer. Use the employees / departments schema above.

SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

SELECT e.emp_name
FROM employees e
WHERE e.dept_id IS NULL;
-- (a join isn't even required here, but with a LEFT JOIN + IS NULL pattern also works)

SELECT e.emp_name, m.emp_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

SELECT m.emp_name AS manager, COUNT(e.emp_id) AS direct_reports
FROM employees m
JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_name;

SELECT d.dept_name, b.band_name
FROM departments d
CROSS JOIN salary_bands b;

SELECT DISTINCT d1.dept_name, d1.location
FROM departments d1
JOIN departments d2
    ON d1.location = d2.location
   AND d1.dept_id != d2.dept_id;
-- Engineering & HR share Bangalore; Marketing & Legal share Delhi

SELECT e.emp_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT MAX(m.salary)
    FROM employees m
    WHERE m.dept_id = 3
);
-- Pure-join version is awkward here; this is a preview of why subqueries exist.

SELECT
    d.dept_name,
    COUNT(e.emp_id)        AS headcount,
    ROUND(AVG(e.salary),2) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;