OUTER APPLY does not work like INNER JOIN

Asked

Viewed 743 times

4

I know that the OUTER APPLY command is analogous to INNER JOIN, but I am confused about a situation where this does not happen.

The query below extracted from the 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 lines are returned.

But when carrying out the consultation:

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

830 lines 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 lines? And INNER JOIN 830? If the internal query returns only 1 line this would not be the only value to be compared?

sql sql-server

  • In the first case, you make a SELECT that limits the number of data and then a CROSS APPLY using the SELECT result, when in the second case, you make only an INNER JOIN. So it seems logical that in the first case, you have less result than in the second. Try only CROSS APPLY in the first case, without the sub-SELECT to see the result. A dethale: in your question you speak of OUTER APPLY. Actually, I think OUTER APPLY = LEFT OUTER JOIN and CROSS APPLY = INNER JOIN.

  • I wanted to refer to CROSS APPLY, but I ended up changing the names. @Peter thanks a lot for the help! I continued testing here and ended up simulating the same result with INNER JOIN by selecting only the id’s and making a DISTINCT in the following way: SELECT DISTINCT c.custid, d.custid from Sales.Mycustomers AS C Inner Join Sales.Myorders AS D ON C.custid = D.custid In the first query the use of TOP in the right query would act as a DISTINCT limiting the combination to only one line?

  • TOP() serves to limit the number of data you will receive. Putting a TOP(1) means you will receive only one result each time. As you do O.custid = C.custid, if you only have 89 different C.custid, you will only receive 89 results after SELECT. So in this case, I think TOP() will have the same effect as DISTINCT from the second query. The question is whether 89 is the result you expect (and in this case you need to let TOP do DISTINCT) the if 830 and the good result. Forehead without TOP() to see.

  • @Alexandresantos, you can make the corrections by editing your question?

1 answer

1

Just to reinforce what Peter already answered, CROSS APPLY is very 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 limitation and filter conditions of the selected set occur in a second step, in WHERE (although JOIN already limit some of the information, the Cartesian product is still applied, bringing tons of information to more).

CROSS APPLY happens in a higher step for the table where CROSS APPLY occurs. So we can perform a TOP or bring less records BEFORE of the cross product actually happen.

In practical terms, instead of performing a JOIN (Cartesian product) with the original table, APPLY makes this product with the RESULT than was selected in the table where APPLY is being made. This also means that in practical terms, 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, taking into account that CROSS APPLY will only apply the Cartesian product on the lines that if apply, already the JOIN will be in all first, then will be filtered.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.