4

From I've read about CROSS APPLY it just seems better than INNER JOIN, it can be used on table valued expressions, it is faster, etc.

So why use INNER JOIN at all?

sources: 1, 2

usr
  • 168,620
  • 35
  • 240
  • 369
J. Doe
  • 1,147
  • 1
  • 13
  • 21
  • 1
    I think the question is the opposite of the duplicate it was linked to, which was giving reasons why you might want to use `CROSS APPLY`. This question seems to be asking "If I have `CROSS APPLY` at my disposal, why would I ever use `INNER JOIN` again?" – Damien_The_Unbeliever Sep 13 '18 at 13:51
  • I edited the tags because from the sources you linked to it seems that this is about SQL Server. – usr Sep 13 '18 at 13:57
  • @usr, Ah, yes, thank you! My mistake! :) – J. Doe Sep 13 '18 at 13:58
  • You might read [this answer](https://stackoverflow.com/a/41789975/5089204). In easy cases the engine is smart enough to use the one or the other. But when it comes down to *row-wise* computation the `APPLY` is better. In many cases the query plan will be the same, in cases where you use `APPLY` instead of a `JOIN`, just because the engines "sees" this. And for sure there are cases, especially with many rows, where the engine will be better in joining sets. – Shnugo Sep 13 '18 at 14:08

3 Answers3

5

You can always use a CROSS APPLY where you'd use an INNER JOIN. But there are reasons you might (and often will) prefer INNER JOIN.

In case the two are equivalent the SQL Server optimizer does not treat them differently in my experience. Therefore, I do not follow the suggestion that a CROSS APPLY is faster. If apples are compared to apples the performance is, in all the query plans I have seen, identical.

INNER JOIN is more convenient to write. Also, it is idiomatic. Therefore, it is most legible and maintainable. INNER JOIN also is more widely supported although that probably does not matter on SQL Server. I also estimate that many developers simply do not know CROSS APPLY.

usr
  • 168,620
  • 35
  • 240
  • 369
  • 1
    Not to be argumentative bur this does not really answer the question. You are confusing CROSS APPLY with CROSS JOIN. Though it's true that you can use CROSS APPLY in place of an INNER JOIN, the same is true about CROSS JOIN. JOIN and APPLY are two of the four types of table operators in T-SQL (PIVOT and UNPIVOT are the other two.) Each one has advantages and disadvantages. – Alan Burstein Sep 13 '18 at 15:58
  • @AlanBurstein did he not ask "why not always use apply over join"? I seem to deal with that issue here. Not confusing with CROSS JOIN. I just tried to clarify the answer. Since this was downvoted twice my answer if probably stated in a misleading way though I do not understand why that is the case. – usr Dec 30 '19 at 15:14
4

I would definitely favour INNER JOIN when it's enough for your purposes - where you don't want/need to (possibly) control per-row behaviour. It's implemented across all SQL databases (where CROSS APPLY isn't necessarily available everywhere) and it avoids adding cognitive overhead where it's not needed.

E.g. you don't get a future reader trying to understand why you've used CROSS APPLY only for them 1, 5 or 30 minutes later to conclude "I think it should just be INNER JOIN". Now they're doubting themselves and you've made your query more difficult to quickly understand. Save it for where it's necessary, and it'll then serve to highlight "pay more attention here".

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    As someone who works a lot in Oracle and SQL Server this idea that queries should work in multi-tenant environments is a whole lot of horse manure. Most queries end up being different so just use what ever features are appropriate and produce efficient query plans. The second paragraph is where Damien is correct. – benjamin moskovits Sep 13 '18 at 14:25
  • @benjaminmoskovits - it wasn't for portability reasons, per se. It was for "this week I'm writing for SQL Server, next week I'll be working in MySQL. If my go-to pick is `INNER JOIN` I don't have to switch gears as much" – Damien_The_Unbeliever Sep 13 '18 at 14:31
  • Your right. But I have found that when I am writing OSQL (Oracle code) its best to just write OSQL and not concern myself with how this would run in TSQL (and vice versa). Its just too disconcerting. – benjamin moskovits Sep 13 '18 at 14:36
0

I doubt it is faster even joining just 2 tables on a single and simple column-to-column condition. SQL optimizer internally converts cross apply to inner join whenever possible - but the opposite does not happen. When cross apply cannot be converted to inner join then it had to be executed at every row. There the only thing matters is how frequently the supply values change so the expression needs to be recalculated. Personally i do not use cross apply unless i have no choice e.g. table valued functions.

Stefanos Zilellis
  • 571
  • 3
  • 8