SQL Server 2008 R2 : Cross join with Where Vs Inner Join Performance

Question

As per this link :

MSDN link

It says that if a Cross join has where clause it behaves as a Inner join and returns the same result as a Inner join.

I understand that part. But my question is when I am using a query cross join with a where condition and another query with Inner join with ON clause, both using the same relations and returning same data, will there be any difference in performance ?

Will the performance be affected if a third/fourth table is joined with these two tables on INNER or LEFT OUTER JOIN ?

I could not find a direct answer on the web addressing the same for SQL Server.

• Girija

• Girija

Answer 1

If you are talking about cross joins with where conditions as implicit joins in cpmarision to explicit joins, they often have the same execution plan. However, this could change as the query gets more complex (it can be hard to predict how the engine will choose to execute complex queries), but probably not unless it gets seriously complicated. You can always check the execution plans to see whether this is happening.

In any event you should never use implicit joins, they are a sql antipattern for several reasons, the first of which is that they can all too easily create an accidental cross join which is expensive for performance and often results in incorrect query results or the requirement to add DIstinct which is also expensive. Further, you should not mix implicit joins and explicit joins (you can get the wrong results), if you have to go to a left join later, you will need to re-write the whole query. So implicit joins are harder to maintain. Finally if you need a real cross join, it is not evident from the implicit synzat whether you intended a cross join or had an accidental one. THis can make amintenance especially difficult.