> 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, Pennsylvania 19026

Wed, 12 Jan 2000 15:43:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Informix and OUTER join syntax
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 Service and other goodies at
  http://donb.photo.net.

Wed, 12 Jan 2000 13:13:08 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Re: Informix and OUTER join syntax
> >       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

Thu, 13 Jan 2000 05:00:40 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
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@pacifier.com>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.

Thu, 13 Jan 2000 06:55:14 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Re: Informix and OUTER join syntax
> 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

Fri, 14 Jan 2000 15:14:22 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: Informix and OUTER join syntax
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 Service and other goodies at
  http://donb.photo.net.

Fri, 14 Jan 2000 08:33:41 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Re: Informix and OUTER join syntax