left outer join not working as expected

[t1950]

Please excuse the poor readability, i couldn't figure out how to make underscores show up consistently. I'm using ASA 11 build 2331, 64 bit WinServ 2008.

This first select works ( it returns 6 rows as expected ). Note that e.moved-to-payment-history is part of the left outer join but e.moved-to-payment-history does NOT exist in the oh table.

SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history   
  FROM customer c join order-header oh on c.customer-id = oh.customer-id  
    left outer join eft-payment-history e on oh.invoice-number = e.invoice-number  
                    and e.moved-to-payment-history = 'n'  
 WHERE oh.open-amount <> 0  
    and c.customer-id = '12345'

This select does NOT work ( no rows returned ). This is the SQL that I expected to work ( converted from tSQL *=, see below ). Note that e.moved-to-payment-history is in the WHERE

SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history   
  FROM customer c join order-header oh on c.customer-id = oh.customer-id  
    left outer join eft-payment-history e on oh.invoice-number = e.invoice-number  
   WHERE oh.open-amount <> 0  
    and e.moved-to-payment-history = 'n'  
    and c.customer-id = '12345'

If I use the "old" tSQL *= this select also works ( 6 rows returned )

SELECT oh.c1, oh.c2, oh.c3, oh.c4, c.c1, e.moved-to-payment-history   
  FROM customer c, order-header oh, eft-payment-history e 
   WHERE c.customer-id = oh.customer-id  
        and oh.invoice-number *= e.invoice-number  
        and oh.open-amount <> 0  
    and e.moved-to_payment-history = 'n'  
    and c.customer-id = '12345'

I've been SLOWLY converting PowerBuilder datawindows from *= to LOJ syntax. What puzzles me is the SQL I expected to work doesn't, and the LOJ that I didn't expect to work does. Explanations from a GURU would be helpful.

[graeme_perrow]

FYI code should be indented by 4 spaces - that will make it much easier to read. I've done this for you.

[VolkerBarth · Accepted solution]

Do the 6 rows from the expected result have values for the e.moved-to-payment-history column? Or is this NULL?

In case the latter is true: That would be fully expected behaviour:

Using a filter for a row from the NULL-supplying side of an outer join in the WHERE clause that just accepts TRUE will turn the outer join in an implicit inner join. This is explained in detail in this doc page.

---

If e.moved-to-payment-history column is not null, well, then I don't have a clue.

[t1950 · Accepted solution]

The 6 returned rows have nulls as expected. The Sybase doc explained the outer join logic.
Thanks for pointing me in the right direction.

[VolkerBarth · Accepted solution]

Glad you got it working! If this has helped, feel free to accept the answer - cf. this FAQ🙂