Example code
Select S.Supplierid, S.CompanyName as supplier, A.*
From Production.Suppliers s
CROSS APPLY ( Select productid, productname, unitprice
from Production.Products p
where p.supplierid = s.supplierid
order by unitprice, productid
offset 0 rows fetch first 2 rows only ) as A
APPLY operator
(1) It applies a table expression in the right side to each row from a table expression in the left side.
(correspondingly color-coded in the example code)
(2) And usually a table expression in the right side references to an element from the left table. In other words, the right table expression is correlated to the left table expression.
(3) So APPLY operator seems to have a mixed concept of a correlated subquery and JOIN.
APPLY operator
- evaluates the right table expression for each row of the left table and yields a result set
- and then, joins the evaluated result set back to the row in the left table.
(4) Two different types: Cross Apply and Outer Apply
Re-explained in the context of the provided example code
The left table contains supplier records, and each record has a supplierid.
The right table contains product records.
(1) The Apply operator takes each supplierid from the left table and evaluates the right table for that supplierid, which yields a evaluated result set. The operator now JOIN the result set back to the record of the supplierid in the left table.
(2) The above process is repeated for each supplierid(each row) of the left table(cursor-based operation).
(3) When the whole operation is completed, the result of the example query will show the two products with the lowest unit prices for each suppliers. See the query result below.
(4) **IF a supplier doesn't have any product record in the product table, that supplier will be omitted from the query result. It is because the example query uses CROSS Apply. OUTER Apply will, however, include such a supplier with NULLs in the columns from the right table
** CROSS APPLY vs. OUTER APPLY is similar to INNER JOIN vs. LEFT OUTER JOIN.
* Note : If the right table doesn't reference any element in the left table, the query result will be exactly same as that of CROSS JOIN between the two tables(Cartesian product).
Result of the example query (order by supplierid)
APPLY operator challenged.
Before I got a good grasp of APPLY operator, I was just wondering if I could find a different way to generate the same query result without using APPLY operator. And I tried and came up with the following query. It yields the same result. To be honest, the following query did make more sense to me before I become comfortable with APPLY operator. But I now see the benefits of using APPLY operator, so I will probably use the operator when it is necessary.
Select S.supplierid, S.companyName as supplier, P.productid, p.productname, p.unitprice
From Production.suppliers s
INNER JOIN ( select ROW_NUMBER() over ( partition by supplierid
order by unitprice, productid) as rownum
,supplierid, productid, productname, unitprice
from Production.Products ) P ON s.supplierid = p.supplierid
where ISNULL(p.rownum,0) <=2
APPLY w/ Inline Table-valued Function
Apply operator also allows us to use an inline table-valued function as a table expression in the right side. So if we put the right table expression into a table-valued function, we could make our CROSS APPLY query a lot simpler.
(1) Create a table-valued function
IF OBJECT_ID(N'Production.', N'IF') IS NOT NULL
DROP FUNCTION Production.GetTop2Product;
GO
CREATE FUNCTION Production.GetTop2Product(@supplierid AS INT)
RETURNS TABLE
AS
RETURN
Select productid, productname, unitprice
from Production.Products p
where p.supplierid = @supplierid
order by unitprice, productid
offset 0 rows fetch first 2 rows only
GO
(2) Re-write the query
Select S.Supplierid, S.CompanyName as supplier, A.*
From Production.Suppliers s
CROSS APPLY Production.GetTop2Product(s.Supplierid) as A
-------------------------------------------------------------------------------------------------------------
Reference: Querying Microsoft SQL Server 2012 Training Kit by Itzik Ben-Gan, Dejan Sarka, Ron Talmage
