Page 52 - SQL

Chapter 12: cross apply, outer apply

Examples

CROSS APPLY and OUTER APPLY basics

Apply will be used when when table valued function in the right expression.

create a Department table to hold information about departments. Then create an Employee table
which hold information about the employees. Please note, each employee belongs to a
department, hence the Employee table has referential integrity with the Department table.

First query selects data from Department table and uses CROSS APPLY to evaluate the
Employee table for each record of the Department table. Second query simply joins the
Department table with the Employee table and all the matching records are produced.

SELECT *
FROM Department D
CROSS APPLY (
SELECT *
FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
ON D.DepartmentID = E.DepartmentID

If you look at the results they produced, it is the exact same result-set; How does it differ from a
JOIN and how does it help in writing more efficient queries.

The first query in Script #2 selects data from Department table and uses OUTER APPLY to
evaluate the Employee table for each record of the Department table. For those rows for which
there is not a match in Employee table, those rows contains NULL values as you can see in case
of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department
table and the Employee table. As expected the query returns all rows from Department table; even
for those rows for which there is no match in the Employee table.

SELECT *
FROM Department D
OUTER APPLY (
SELECT *
FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
ON D.DepartmentID = E.DepartmentID

https://riptutorial.com/ 34