title: Cross join behaviour (SQLServer 2008)
body: <p>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.</p>
  
  <p>Original query (returns no rows)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Modified query - cross join replaced by a comma (implicit cross join)</p>
  
  <p>Returns one row</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My understanding, which may be incorrect is that writing <code>from Table1 a, Table2 b</code> is the same as writing <code>from Table 1 a cross join Table2 b</code>.  So I don't understand why the queries return different results.</p>
  
  <p>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.</p>
  
  <p>This is on SQLServer 2008.</p>