Re: simple SQL queries give different results: Why?

From: Daniel W. Fink <daniel.fink_at_optimaldba.com>
Date: Tue, 08 Feb 2011 19:04:05 -0700
Message-ID: <4D51F615.9030600_at_optimaldba.com>

3 Valued Logic - True, False and Unknown is not the easiest Oracle concept to get your head around. I understand it...but I often struggle with explaining it.

The predicate "WHERE NOT EXISTS (subquery)" must evaluate to TRUE in order for DUMMY to be returned.
The results of the subquery can either be the empty set, a non-empty set or unknown.

WHERE NOT EXISTS (non empty set) - evaluates to FALSE
WHERE NOT EXISTS (empty set) - evaluates to TRUE
WHERE NOT EXISTS (unknown) - evaluates to UNKNOWN (NOT TRUE and NOT FALSE)

If you wrap NVL around the MAX(1), you find that the result set is UNKNOWN (represented in Oracle as NULL).

SQL> SELECT MAX(1)

   2               FROM DUAL d2
   3              WHERE d2.dummy = 'z'

   4 /

     MAX(1)

---

SQL> edit
Wrote file afiedt.buf

   1 SELECT NVL(MAX(1),0)

   2               FROM DUAL d2
   3*             WHERE d2.dummy = 'z'

SQL> / NVL(MAX(1),0)

---

             0

Since the result is UNKNOWN the predicate is evaluated to UNKNOWN, so no row is returned from the main query.

On 2/8/2011 6:39 PM, Michael Moore 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.
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.872 / Virus Database: 271.1.1/3430 - Release Date: 02/08/11 00:34:00
>

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

Received on Tue Feb 08 2011 - 20:04:05 CST