Cross join behaviour (SQLServer 2008)

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.i开发者_运维知识库d 
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.

---

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

---

Without looking at the actual data and query plans, I'd say (ok, guess) it has to do with the way the optimizer builds the query plans.

In the first, it is more or less explicitly told to "take the first table, cross join it with the second, then right join in the third, then right join in the fourth"

In the second, that cross join is (at least to my way of thinking) implicit. This is "old" SQL syntax from the days when all joins were performed in the WHERE clause, which--again, to my way of thinking--means that the database engine was free to work out on its own the order in which to process tables. Or, in other words, SQL is not being give a specific order in which to join tables. (With inner joins and cross joins, it makes no difference, but with outer joins, it can make a huge difference.)

...I prefer @Joe's answer (upvoted), as it's technically accurate. I'm tossing it my own anyway just for detail's sake.