Re: simple SQL queries give different results: Why?

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Tue, 8 Feb 2011 20:52:26 -0500
Message-ID: <AANLkTin-iPmpmVJg41kYS+miHCb5c-C20pYgCqxPQyZc_at_mail.gmail.com>

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_at_gmail.com>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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 08 2011 - 19:52:26 CST