Re: How to get only the non-matching entries from an outer join

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Wed, 27 Nov 2002 11:25:48 +0100
Message-ID: <3de49dae$0$21580$91cee783@newsreader01.highway.telekom.at>

Dieter Nöth wrote:
> Heinz Huber wrote:
>

>>> SELECT T2.FK, T1.PK
>>> FROM T2
>>> LEFT JOIN T1 ON T2.FK = T1.PK
>>> WHERE (T1.PK Is Null);
>>
>>AFAIK, this will give you all rows from T2 without any data from T1.
>>The where clause is applied BEFORE the outer join. Therefore all rows
>>from T1 are filtered out since the PK cannot be NULL.

>
>
> No, the WHERE clause is applied AFTER an Outer Join, that's why the result
> set is correct.

That's interesting. We use Sybase ASA. This filters the tables based on the where clause. Therefore, the above query would not give the results you want.

Anybody care to comment on the standard?

Heinz Received on Wed Nov 27 2002 - 04:25:48 CST