[Solved] What is the difference between OUTER APPLY and OUTER JOIN, and when to use each? [closed]

by


First, I do not really know which is the default OUTER JOIN in T-SQL (I’d bet FULL), but in any case, I think it is unclear – it is best to use LEFT, RIGHT, OR FULL JOIN explicitly when you need one.

JOIN joins tables. Already existent ones or subqueries.

APPLY applies (duh) a table-valued function-equivelant in every row of the (left) table. Some interesting cases are:

  1. Apply an existing actual function: CROSS/OUTER APPLY dbo.your_function_name
  2. You could use a SELECT (some fields) without a FROM in order to create aliases for the fields – that makes your code look neat
  3. You can use a SELECT with a FROM to make a subquery. This is the same as a join

CROSS APPLY is a superset of INNER JOIN, and OUTER APPLY is a superset of LEFT. You can’t have a function be applied to nothing, as is the case with RIGHT or FULL joins.

2

solved What is the difference between OUTER APPLY and OUTER JOIN, and when to use each? [closed]