Cross Interaction (SQLServer 2008)

Question

I am trying to find a problem with the query that I have. The query is actually generated using hibernate from HQL, but the resulting SQL does not do what I expect. Changing SQL leads to the correct result a little, but I'm not sure why the change should have any meaning.

Original query (does not return 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-connect is replaced with a comma (implicit cross-connect)

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 the entry from Table1 a, Table2 b matches the entry from Table 1 a cross join Table2 b . Therefore, I do not understand why queries return different results.

Is this something related to the interaction between the cross join and the outer joins in the first request that causes this? I looked at the query plans, and the second query plan seems reasonable. The first of them has no external connections in everything that is strange.

This is on SQLServer 2008.

Answer 1

JOIN has a higher priority than 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 extension rules for SQL-99

Answer 2

Despite the actual data and query plans, I would say (well, I guess) that this is due to the way the optimizer builds query plans.

The first, more or less explicitly says, "take the first table, cross-connect it to the second, and then join the third, and then join the fourth"

In the second, this cross connection (at least to my way of thinking) is implied. This is the "old" SQL syntax from the days when all the connections were made in the WHERE clause, which, again, to my way of thinking, means that the database engine was free to independently develop the order in which the process tables. Or, in other words, SQL does not give a specific order to join tables. (With internal joins and cross joins this doesn't make any difference, but with external joins, it can make a huge difference.)

... I prefer @Joe to answer (upvoted) because it is technically accurate. I still toss it on my own, just in order to tell in detail.