Q40: What is the difference between CROSS APPLY and OUTER APPLY?
APPLY operators (available in SQL Server and Oracle, or implemented via lateral joins in PostgreSQL) join a table to a table-valued function or correlated inline subquery evaluated for every outer row.
- CROSS APPLY: Functions like an
INNER JOIN. Returns outer rows only if the invoked inline function produces output rows. - OUTER APPLY: Functions like a
LEFT JOIN. Preserves outer rows even if the inline function returns no rows, filling missing fields withNULL.
Key Takeaways
- APPLY operators pass outer row values into table-valued functions iteratively.
- CROSS APPLY acts like an INNER JOIN; OUTER APPLY acts like a LEFT JOIN.