Re: subquery returning no rows in WHERE clause

That would force a sub-sort on the inner query.

How about a outer join like:-

select textcol from table1, idtable
where idcol != nvl(id(+), 1.0E-130)
and idvalue = '...'

Now assuming that you have no value in idcol = 1.oE-130 that you want to be returned.

-M

Erika Grondzakova wrote:

> 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 - 22:30:39 CDT