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