CODEWITHSUNDEEP
sql-servert-sqljoin
J. Doe
J. Doe

Reputation: 1327

Benefits of INNER JOIN over CROSS APPLY

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

Upvotes: 5

Views: 5407

Answers (3)

usr
usr

Reputation: 171246

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.

Upvotes: 6

Stefanos Zilellis
Stefanos Zilellis

Reputation: 611

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.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

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".

Upvotes: 4

Related Questions