CROSS JOIN with filter equals INNER JOIN on Sql Server

Cross join with filter equals inner join with filter. A little experiment to test what Sql Server with it's query optimisation and with how linq does it's magic.
May 22 2012

When querying with Linq you can either use the natural relationships between your entities or you can make your joins explicit.  Take the following example from the three entities, Debt, DebtAddress, and Address.  Debt's have multiple DebtAddresses.  Each DebtAddress has one Address.  A query to extract all addresses for a debt could look like either of the following.

1) Using existing relationships

from d in Debt
from da in d.DebtAddresses
select da.Address

2) or you can you explicit joins

from d in Debt
join da in DebtAddressses on d.DebtId equals da.DebtId
join a in Address on da.AddressId equals a.AddressId
select a

The resultant SQL of each query uses CROSS JOIN for the existing relationships and INNER JOIN for the explicit joins.  CROSS JOIN is a cartesian product of the two tables either side of the join, which at first seems like it would produce more rows than is necessary (database theory 101), so surely it's a mad alternative.  However, Sql Server doens't suck and when it builds its execution plan it factors in the WHERE clause to get the same result as if using INNER JOIN.  Obvious yeh!?

Cross Join 1


Inner Join

Post a comment

comments powered by Disqus