Question 1

What happens to Bob with no department?

Accepted Answer

LEFT JOIN keeps every row from the left table (employees) regardless of whether a match exists in the right table. Alice matches Sales. Bob has no dept_id, so no department row matches - the dept column is NULL in his result row. Use LEFT JOIN when you need all left-table rows even if the right side has no data.

Question 2

Which rows survive the join?

Accepted Answer

INNER JOIN returns only rows where the join condition matches in both tables. Carol has NULL dept_id which matches no department id, so she is excluded. HR (id 30) has no matching employee, so it is also excluded. Only Alice and Bob have matching dept_ids.

Question 3

The department with no employee

Accepted Answer

RIGHT JOIN keeps every row from the right table (departments) regardless of whether a match exists on the left. Sales matches Alice. Eng has no matching employee, so e.name is NULL in that row. RIGHT JOIN is less common in practice - most developers rewrite it as a LEFT JOIN by swapping table order.

Question 4

Reading a shrinking row count

Accepted Answer

INNER JOIN only keeps rows where a match exists on both sides. When 5 and 4 produce 3, that means some rows from each table were dropped for having no counterpart. There is nothing wrong with the query - unmatched rows are simply excluded by design. If you need to preserve unmatched rows, use a LEFT, RIGHT, or FULL OUTER JOIN instead.

Question 5

What is a FOREIGN KEY?

Accepted Answer

A FOREIGN KEY enforces referential integrity - you cannot insert a value that has no matching row in the parent table. For example, an orders.customer_id foreign key means you cannot create an order for a customer_id that does not exist in the customers table. Foreign key columns can hold NULLs (meaning 'no relationship'), and they do not need to be unique.

Question 6

What does LEFT JOIN return?

Accepted Answer

A LEFT JOIN (or LEFT OUTER JOIN) always returns every row from the left table. When a left-table row has no match in the right table, the right-side columns are filled with NULL. This is useful for finding unmatched records: add WHERE right_table.id IS NULL to get left-table rows that have no corresponding right-table row.

Question 7

What does INNER JOIN return?

Accepted Answer

INNER JOIN is the default join type - if you write JOIN without a qualifier, you get an INNER JOIN. Rows that exist in one table but have no match in the other table are excluded from the result entirely. This is often the right choice when you only care about records that have a relationship on both sides.

Question 8

ON vs WHERE in a LEFT JOIN

Accepted Answer

Putting a filter in ON vs WHERE has completely different semantics for outer joins. In Query A, the condition d.name = 'Sales' is part of the join - employees not in Sales still appear but get NULL for the dept columns. In Query B, WHERE filters after the join, so only rows where d.name = 'Sales' survive - effectively turning the LEFT JOIN into an INNER JOIN. This is one of the most common outer join mistakes.

Question 9

Who gets filtered by WHERE d.id IS NULL?

Accepted Answer

LEFT JOIN + WHERE right_table.id IS NULL is the classic pattern for finding rows in the left table with no match in the right table. Alice matches Sales, so d.id = 10 and she is excluded by WHERE. Bob (NULL dept_id) and Carol (dept_id 99, no matching dept) both produce d.id = NULL and pass the filter.

Question 10

How many rows does CROSS JOIN produce?

Accepted Answer

CROSS JOIN pairs every row in the left table with every row in the right table - no ON condition is needed or allowed. 2 colors x 2 sizes = 4 combinations: (Red, S), (Red, M), (Blue, S), (Blue, M). This is useful for generating all combinations, but can be catastrophic on large tables.

Question 11

Chaining two JOINs across three tables

Accepted Answer

Chaining JOINs is how you traverse relationships across multiple tables. The first JOIN links orders to customers via customer_id = c.id. The second JOIN links the same orders row to products via product_id = p.id. The result for the single order is Alice | Widget.

Question 12

Matching rows with BETWEEN instead of =

Accepted Answer

A non-equi join uses a condition other than equality - here BETWEEN instead of =. Alice's salary 85000 falls in the Sr band (70000-999999); Bob's 55000 falls in Jr (0-69999). Non-equi joins work exactly like regular joins: every pair of rows where the ON condition is true produces an output row. Common uses include mapping salaries to pay grades, prices to discount tiers, or dates to fiscal periods.

Question 13

When one join column isn't enough

Accepted Answer

Some relationships use composite keys - the unique identifier spans multiple columns. For example, a regional_prices table keyed on (product_id, region_id) requires ON o.product_id = rp.product_id AND o.region_id = rp.region_id. Joining on only one column would produce incorrect fan-out (extra rows). Multiple ON conditions are connected with AND. You can also mix equality and inequality conditions in the same ON clause.

Question 14

Order of chained JOINs

Accepted Answer

The SQL standard guarantees that JOIN is logically commutative and associative for inner joins - the optimizer is free to reorder them for efficiency. What determines correctness is the ON condition, not the physical order of JOIN clauses in your SQL. That said, LEFT JOINs are order-sensitive in terms of which rows are preserved, so be deliberate when mixing inner and outer joins in the same chain.

Question 15

Joining on product_id alone - how many rows?

Accepted Answer

Joining only on product_id ignores the region column, so each order matches both price rows (US and EU). Order 1 matches prices for US and EU (2 rows), and order 2 does the same (2 more rows) - 4 total, which is wrong. The fix is to also join on region: ON o.product_id = p.product_id AND o.region = p.region, which correctly returns 2 rows with the right prices.

Question 16

What does FULL OUTER JOIN return?

Accepted Answer

A FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN. Every row from both tables appears in the result - rows with a match are joined together, and rows without a match appear with NULLs on the side that had no partner. Note that MySQL does not support FULL OUTER JOIN natively - you have to emulate it with UNION of a LEFT JOIN and a RIGHT JOIN.

Question 17

Counting rows when neither side fully matches

Accepted Answer

FULL OUTER JOIN keeps all rows from both tables. Matched: Alice + Sales (1 row). Left-only: Bob (NULL dept_id, no match) - d.name is NULL. Right-only: Eng (no employee) - e.name is NULL. Total: 3 rows. FULL OUTER JOIN is the union of LEFT JOIN and RIGHT JOIN.

Question 18

The WHERE clause that quietly changes your join type

Accepted Answer

LEFT JOIN produces NULL in all right-table columns for unmatched left rows. Any WHERE condition that requires a right-table column to be non-NULL filters those rows out - effectively turning the LEFT JOIN into an INNER JOIN. WHERE customers.name IS NOT NULL removes every row where no customer was found, which are exactly the unmatched rows LEFT JOIN was supposed to keep. The fix: move the condition into the ON clause, or use WHERE customers.name IS NULL / IS NOT NULL intentionally for 'find unmatched' queries.

Question 19

NULL in a join key

Accepted Answer

SQL uses three-valued logic: NULL compared to anything - including another NULL - evaluates to UNKNOWN, not TRUE. So ON a.id = b.id will not match when both a.id and b.id are NULL. This is why LEFT JOIN + WHERE right.id IS NULL finds unmatched rows rather than accidentally matching two NULLs. If you need to treat NULLs as equal, use ON (a.id = b.id OR (a.id IS NULL AND b.id IS NULL)) or the IS NOT DISTINCT FROM operator in PostgreSQL.

Question 20

3 orders, 1 customer - how many result rows?

Accepted Answer

When the right side of a join has one row and the left side has N rows, the join produces N rows - one for each left row matched. This is called fan-out. 3 orders all match the single customer row, so you get 3 result rows. Fan-out becomes a bug when you accidentally join to a non-unique table - for example, joining to a prices table that has multiple effective-dated rows and forgetting to filter to the current price, which inflates your row count. Always check cardinality before joining.

Question 21

JOIN vs subquery — when to use each

Accepted Answer

When you need both individual row data and a group-level aggregate together, a window function is the cleanest option. PARTITION BY dept computes the dept average without collapsing rows. A correlated subquery works but re-executes for every row, which is slow on large tables. A self-join also works but requires explicit grouping before joining. GROUP BY alone collapses to one row per dept, losing individual employee detail. The window function expresses exactly what you want in one pass.

Question 22

FULL OUTER JOIN vs LEFT JOIN

Accepted Answer

LEFT JOIN preserves all left-table rows and drops unmatched right-table rows. FULL OUTER JOIN goes further: it keeps unmatched rows from both sides, filling in NULLs wherever there is no match. A classic use case is reconciling two data sources - for example, finding customers in system A who are missing from system B and vice versa in one pass. Performance is not a distinguishing factor; FULL OUTER JOIN is simply the only join type that guarantees no row from either table is silently dropped.

Question 23

Detecting unexpected fan-out

Accepted Answer

A doubled row count is the hallmark of a many-to-one join where you expected one-to-one. Each row in the driving table matched two rows in the joined table, producing two output rows for every one input row. The fix depends on intent: if you only need one row per match, deduplicate the joined table first (subquery with DISTINCT or ROW_NUMBER), or aggregate before joining. Always verify the cardinality of both sides before writing a JOIN - COUNT(DISTINCT key) vs COUNT(*) in the joined table quickly reveals duplicates.

Joins