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.

score 9 · Accepted Answer · answered Jan 24 '11 at 17:37

9

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

answered Jan 24 '11 at 17:37

Joe Stefanelli

132,803
19
237
235

Thanks +1. In my HQL I have used a COMMA so I'm not sure why hibernate would render a CROSS JOIN instead. I switched the from clauses round so the Period table was last and it worked correctly. Still produced the cross join but because it's last in the precedence it works as expected. – Mike Q Jan 24 '11 at 18:01
@Mike This highlights the rough edge of using RIGHT JOIN instead of reordering to use LEFT JOIN. If this query was rewritten to put the important tables on the left (and at the top of the query), the whole UNION vs. comma thing would have disappeared. – ErikE Jan 25 '11 at 00:17
@Emtucifor - True, unfortunately this query is generated by some Hibernate HQL and due to the nature of it using the object model relations rather than defining arbitrary join conditions it's difficult to rewrite (or rather I don't know how to rewrite it!) – Mike Q Jan 25 '11 at 09:09

score 0 · Answer 2 · answered Jan 24 '11 at 17:41

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.

Cross join behaviour (SQLServer 2008)

2 Answers2

Linked