Re: subquery returning no rows in WHERE clause

Hi,

What about NOT IN ?

SELECT TextCol FROM Table1
WHERE IDCol NOT IN (SELECT ID FROM IDTable WHERE IDValue = '...')

Erika

Thomas Briggs wrote:
>
> Is there any way in Oracle8 to have a not-equals comparison against a
> subquery return rows when the subquery returns no rows?
>
> Take the following query:
>
> SELECT TextCol FROM Table1
> WHERE IDCol <> (SELECT ID FROM IDTable
> WHERE IDValue = '...')
>
> When there is no matching record in the IDTable this returns no rows, as
> the subquery returns no
> rows. Logically, however, this is incorrect - everything is not equal
> to nothing! Is there any
> way (without an embedded PL/SQL function) to have this return the
> logically correct rows?
>
> Thanks
> -Tom
Received on Thu Jun 03 1999 - 09:41:44 CDT