Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()

Joins: INNER = intersection only; LEFT = all left + matching right (NULL fill); RIGHT = mirror of LEFT; FULL OUTER = union of both. Why it matters: Join choice affects result cardinality and NULL handling—wrong join = wrong business logic (e.g., LEFT to preserve all customers even without orders). Window functions: ROW_NUMBER() = unique rank 1,2,3; RANK() = ties same rank, gaps after; DENSE_RANK() = ties same rank, no gaps. Why: ROW_NUMBER for dedup; RANK for 'top N per group with ties'; DENSE_RANK when rank gaps are meaningless. Scalability: JOIN order and size drive shuffle in distributed engines; put smaller table on broadcast side when possible. Cost: Window with PARTITION BY causes shuffle; ORDER BY alone can be cheaper. Example: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;