SQL Server 2005 introduced the APPLY operator, which is very much like a join clause. APPLY allows the joining of two table expressions. The difference between join and the APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
Perhaps the best way to explain it is by example. In this case the example we will use is the one-to-many relationship example of Customers and their Orders. A Customer can have none, one or many Orders. An Order always has exactly one Customer attached to it. Our APPLY example here will be applicable to any valid one-to-many relationship, not necessarily a Customers/Orders example.
For this example, I will use the data as published in the book T-SQL Querying by Itzik Ben-Gan et al by Microsoft Press in 2015. Here is the Customers table.
Here is the Orders table.
Below is the CROSS APPLY query.
SELECT C.custid, C.city, A.orderid FROM dbo.Customers AS C CROSS APPLY ( SELECT TOP (2) O.orderid, O.custid FROM dbo.Orders AS O WHERE O.custid = C.custid ORDER BY orderid DESC ) AS A;
Below is what the above query produces.
Apply is similar to a Join
Both of the queries below produce the same results. APPLY requires TOP
SELECT C.custid, C.city, A.orderid FROM dbo.Customers AS C CROSS APPLY ( SELECT TOP (1000) O.orderid, O.custid FROM dbo.Orders AS O WHERE O.custid = C.custid ORDER BY orderid ASC ) AS A; SELECT C.custid, C.city, O.orderid FROM dbo.Customers AS C inner join dbo.Orders AS O on O.custid = C.custid ORDER BY orderid ASC
Both queries produce the same results as seen below.