Is the performance of SQL Server cross joins with conditionals in the where statement the same as inner joins with conditionals in the join's on statement?

Question

I am trying to determine the performance of a piece of generated SQL that runs in SQL Server 2005.

It uses CROSS JOINS, but the conditionals tying the cross joined tables are in the where statement.

I previously thought that all cross joins that have where statements would first pull the full cartesian product and then apply the filter.

However, this following link on MSDN suggests differently.

http://msdn.microsoft.com/en-us/library/ms190690.aspx

It specifically states that if there is a conditional on a cross joined table, it will "behave" like an inner join. It goes on to show an example of the similar results of an inner join and a cross join w/ where conditional.

It does not state what the performance difference is though, only that they behave in a similar fashion.

Answer 1

Probably the easiest thing to do is to display the execution plan for your statement and take a look at what it's doing. You can do it from management studio - there's some more info here:

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

Answer 2

The example given is correct, atleast in a basic query.

The query below will yield the same execution plan and execution times for both queries. The Sql Optimiser is typically free to move restrictions between where clauses and 'on' clauses during join operations, and i imagine the same is for cross joins.

    select companies.id, contacts.id
from 
companies
cross join contacts
where
contacts.companyid = companies.id


select companies.id, contacts.id
from 
companies
inner join contacts on contacts.companyid = companies.id