Cross join behaviour (SQLServer 2008)

Question

I have been trying to track down a problem with a query I have. The query is actually generated by hibernate from HQL but the resulting SQL doesn't do what I expect. Modifying the SQL slightly produces the correct result but I'm not sure why the modification should make any difference.

Original query (returns no rows)

select sched.id, max(txn.dttm), acc.id
from PaymentSchedulePeriod sched 
cross join PaymentSchedulePayment pay
right outer join AccountTransaction txn on pay.accountTransactionFk=txn.id 
right outer join Account acc on txn.accountFk=acc.id 
where sched.accountFk=acc.id 
group by sched.id, acc.id

Modified query - cross join replaced by a comma (implicit cross join)

Returns one row

select sched.id, max(txn.dttm), acc.id
from PaymentSchedulePeriod sched 
,PaymentSchedulePayment pay
right outer join AccountTransaction txn on pay.accountTransactionFk=txn.id 
right outer join Account acc on txn.accountFk=acc.id 
where sched.accountFk=acc.id 
group by sched.id, acc.id

My understanding, which may be incorrect is that writing from Table1 a, Table2 b is the same as writing from Table 1 a cross join Table2 b. So I don't understand why the queries return different results.

Is is something to do with the interaction between the cross join and the outer joins in the first query that causes this? I've looked at the query plans and second query plan looks reasonable. The first one has no outer joins at all which is strange.

This is on SQLServer 2008.

Answer 1

JOIN has a higher precedence than a COMMA, so your second statement is interpreted as (note the parens I added):

select sched.id, max(txn.dttm), acc.id
from PaymentSchedulePeriod sched 
,(PaymentSchedulePayment pay
right outer join AccountTransaction txn on pay.accountTransactionFk=txn.id 
right outer join Account acc on txn.accountFk=acc.id)
where sched.accountFk=acc.id 
group by sched.id, acc.id

See also: JOIN precendence rules per SQL-99