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.