Re: AW: A fine point about OUTER JOIN semantics

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> But suppose we make the query
>> SELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2;
>> It seems to me this should yield
>> x    y
>> 
>> 1    1
>> 3    NULL
>> 4    4

>           x           y

>           1           1
>           4           4

Oh, my mistake, I forgot that the WHERE clause would filter out NULLs.
Try
SELECT * FROM t1 LEFT JOIN t2 ON (x = y) WHERE y <> 2 OR y IS NULL;

>> An even more interesting example is
>> SELECT * FROM t1 FULL JOIN t2 ON (x = y AND y <> 2);
>> My interpretation is that this should produce
>> x    y
>> 
>> 1    1
>> 2    NULL
>> NULL    2
>> 3    NULL
>> 4    4

>           x           y

>           1           1
>           4           4

Here I believe Informix is broken.  Their result clearly does not
agree with the spec's definition of a FULL JOIN ... indeed it looks
exactly like an inner join.
        regards, tom lane