INNER, LEFT, and derived tables work as in ANSI SQL—ensure join columns are indexed on the filtered side.
Inner and left join
SELECT o.id, o.total, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open';
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'open'
WHERE o.id IS NULL;Practice: Run on practice.
Orphans pattern
LEFT JOIN + IS NULL finds customers with no open orders—common interview pattern.
Important interview questions and answers
- Q: INNER vs LEFT?
A: INNER drops non-matching rows; LEFT keeps left table rows with NULLs on right. - Q: Join column index?
A: Index customer_id on orders for nested loop efficiency.
Self-check
- Find customers with no open orders.
- Which join keeps all customers?
Tip: LEFT JOIN + IS NULL finds rows with no match—memorize pattern.
Interview prep
- LEFT JOIN orphans?
LEFT JOIN + IS NULL finds rows without match.
- Index join cols?
Index foreign key columns on child table.