Re: simple SQL queries give different results: Why?
- From: Rumpi Gravenstein <rgravens@xxxxxxxxx>
- To: michaeljmoore@xxxxxxxxx, oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 8 Feb 2011 20:52:26 -0500
The issue is that NOT EXISTS is looking for a NULL. In the later case the
subquery has that while in the former you don't. The max function will
return a value even if no rows match the where clause.
SQL> select rownum
2 from ( select max(1)
3 from dual
4 where dummy = 'z'
5 );
ROWNUM
----------
1
On Tue, Feb 8, 2011 at 8:39 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> The only difference is the MAX function.
>
> SQL> SELECT d1.dummy
> FROM DUAL d1
> WHERE NOT EXISTS
> (SELECT MAX(1)
> FROM DUAL d2
> WHERE d2.dummy = 'z')
> no rows selected.
>
> SQL> SELECT d1.dummy
> FROM DUAL d1
> WHERE NOT EXISTS
> (SELECT 1
> FROM DUAL d2
> WHERE d2.dummy = 'z')
>
> DUMMY
> -----
> X
> 1 row selected.
>
--
Rumpi Gravenstein
Other related posts:
