OUTER APPLY does not work as INNER JOIN

Question

OUTER APPLY does not work as INNER JOIN

Navigation

#1 by (1 votes)

4

I know the OUTER APPLY command is analogous to INNER JOIN, but I'm confused by a situation where this does not happen.

The following query extracted from Training Kit 70-461:

SELECT c.custid 
FROM Sales.MyCustomers AS C
CROSS APPLY (SELECT TOP (1) O.shippostalcode
                FROM Sales.MyOrders AS O 
                WHERE O.custid = C.custid
                ORDER BY orderdate, orderid) as A;

89 rows are returned.

But when doing the query:

SELECT * from Sales.MyCustomers C
INNER JOIN Sales.MyOrders O
ON c.custid = o.custid

830 rows are returned.

Internal query returns only one row due to TOP:

SELECT TOP (1) O.shippostalcode
Sales.MyOrders AS O, Sales.MyCustomers C 
WHERE O.custid = C.custid
ORDER BY orderdate, orderid

Why does CROSS APPLY return 89 rows? What about INNER JOIN 830? If the internal query returns only 1 row this would not be the only value to be compared?

sql sql-server

asked by anonymous 11.11.2014 / 21:22

1 answer

Query by Linq and Lambda getting range of options by sub-query. How to get a result closer to the simple? Transaction XA does not commit changes in procedures (intermittent)

score 1 · Answer 1

Just to reinforce what Peter has already answered, CROSS APPLY is quite different from INNER JOIN.

In simple terms, INNER JOIN is a mathematical operation, a Cartesian product of the information between Table A and Table B in operations. The constraint and filter conditions of the selected set occur in a second step in the WHERE (although the JOIN already limits some of the information, the Cartesian product is still applied, bringing tons of more information.)

CROSS APPLY is now in a higher step for the table in which CROSS APPLY occurs. So we can do a TOP or bring less BEFORE records of the cross product actually happening.

In practical terms, instead of making a JOIN (Cartesian product) with the original table, APPLY makes this product with the RESULT of what was selected in the table where APPLY is being done. This also means that in practice it is much faster to perform a CROSS JOIN than an INNER JOIN when we know that there is a lot of data that will not be displayed in a query , since CROSS APPLY will only apply the product