Thread: Re: Informix and OUTER join syntax
> Looking in the Informix manuals, I now see how they handle complex outer
> joins:
>
> SELECT *
> FROM tab1, OUTER(tab2, tab3)
> WHERE tab1.col1 = tab2.col1 AND
> tab2.col1 = tab3.col1
>
> It does the tab2, tab3 join first, then _outer_ joins to tab1.
> Interesting.
Here is another example that does a double outer join:
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. Even more interesting.
Can someone show me this in ANSI syntax?
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
Looking in the Informix manuals, I now see how they handle complex outer joins: SELECT * FROM tab1, OUTER(tab2, tab3)WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1 It does the tab2, tab3 join first, then _outer_ joins to tab1. Interesting. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
At 03:43 PM 1/12/00 -0500, Bruce Momjian 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. Even more interesting. > >Can someone show me this in ANSI syntax? Along the lines of SELECT * FROM tab1 RIGHT JOIN (tab2 RIGHT JOIN tab3 on col1) on col1 more or less. No where clause is needed, of course. I left my copy of Date's book back in Boston so can't be precise, guess I'll have to go visit my girlfriend ASAP! Thomas will probably make it clear I'm all wet here, but by trying to generate SQL-92 queries myself I'm hoping I'll learn something. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> > 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.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
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@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
> 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? ...and some
> might argue this is less clear than explicitly listing the column(s)
> to join on.
But this is "natural", right? ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
At 03:14 PM 1/14/00 +0000, Thomas Lockhart wrote: >> 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? ...and some >> might argue this is less clear than explicitly listing the column(s) >> to join on. > >But this is "natural", right? ;) Cute! I have no experience trying to read and understand other people's queries using SQL 92 outer joins so can't really say whether the "natural" style is more clear than the more cumbersome explicit notation. I think both forms are quite readable, though. - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.