Re: [HACKERS] Re: Informix and OUTER join syntax

At 05:00 AM 1/13/00 +0000, Thomas Lockhart wrote:
>> > SELECT *
>> > FROM tab1, OUTER(tab2, OUTER tab3)
>> > WHERE tab1.col1 = tab2.col1 AND
>> > tab2.col1 = tab3.col1
>> >It does the tab2, tab3 as an _outer_ join first, then _outer_ joins to
>> >tab1. Can someone show me this in ANSI syntax?
>> SELECT *
>> FROM tab1 RIGHT JOIN (tab2 RIGHT JOIN tab3 on col1) on col1
>
>Pretty sure this is correct (assuming that the Informix syntax is
>showing a right-side outer join). istm that SQL92 is clearer, in the
>sense that the WHERE clause in the Informix syntax specifies that
>columns shall be equal, when in fact there is an implicit "or no
>column matches" coming from the OUTER specification. SQL92 uses unique
>syntax to specify this.

And if I understand SQL92 correctly, if tab1, tab2, and tab3 only
share col1 in common, then you can further simplify:

SELECT *
FROM tab1 NATURAL RIGHT JOIN (tab2 NATURAL RIGHT JOIN tab3)

Is that right? Again, I'm missing my Date SQL 92 primer...and some
might argue this is less clear than explicitly listing the column(s)
to join on.

Anyway, thanks for the verification of my first stab at this, I think
I'm getting a feel for the notation.

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.