SQL Joins Practice Questions

INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table, with NULLs where the right table has no match.

• INNER JOIN: use it when a match is required, like orders with their customers.
• LEFT JOIN: use it when the left table is what you are counting, like all customers including those with no orders.
• RIGHT JOIN is just a LEFT JOIN with the tables swapped.
• FULL OUTER JOIN keeps unmatched rows from both sides.

Use an anti-join: keep the rows from table A that have no partner in table B.

• LEFT JOIN B and keep rows where B's key IS NULL, or use NOT EXISTS (shown here).
• Both handle NULLs correctly and usually run as an efficient anti-join.
• Avoid NOT IN: one NULL in the subquery makes the whole result empty.

A filter on the right table in WHERE removes the NULL rows a LEFT JOIN creates, so it behaves like an INNER JOIN.

• Why: NULL > 100 is not true, so unmatched rows are filtered out.
• Fix: move the condition into the ON clause (shown here).
• Too many rows instead? That is usually a one-to-many fan-out. Aggregate before you join.