Re: AW: A fine point about OUTER JOIN semantics

Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)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