Join Types Interview Questions

INNER JOIN keeps rows that match in both tables. LEFT JOIN keeps every row from the left table and fills right-side columns with NULL when there is no match. RIGHT JOIN is the mirror. FULL OUTER JOIN keeps all rows from both sides. CROSS JOIN is every combination, a cartesian product. SELF JOIN is a table joined to itself, like employee and manager in the same table. The interview follow-up I expect is 'customers with no orders'. That is LEFT JOIN orders ON customer_id and then WHERE orders.id IS NULL. If I write JOIN with no ON, that is a CROSS JOIN, and it is usually a mistake. I would draw two small tables on paper if they let me, because join questions go wrong when I only recite names.

If the right table has multiple matches, a left join duplicates the left row. I have seen revenue double because of a one-to-many join to payments. I would aggregate the right side first or join to a unique key. I always ask 'is this relationship one-to-one?' before I trust a join in a report. INNER JOIN dropping unmatched left rows is the other surprise. I pick LEFT when I must keep all customers even with no orders.

CROSS JOIN is a Cartesian product. I use it for generating a calendar times a list of stores, then LEFT JOIN facts, so I get zeros for missing days. I would not cross join two large fact tables by accident — that is how queries explode. If I forget an ON clause in old comma syntax I get a cross join. I mention that as a bug I look for in reviews.

Employees with manager_id: I alias e and m, join e.manager_id to m.id, and select e.name and m.name as manager. Same for finding customers who share an email domain. I always alias. Self-join is also how I compare a row to the previous date if I do not use LAG. I would prefer LAG for previous-row problems when the engine supports windows.

FULL OUTER JOIN keeps unmatched rows from both sides in one shot. If the engine lacks FULL OUTER, I UNION a LEFT JOIN with a RIGHT JOIN where the left key is null. I use full outer when I am reconciling two source systems — ids in A not in B and vice versa. I would not full-outer two huge unindexed tables without a plan. I would filter in a CTE first.