Re: simple SQL queries give different results: Why?

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 9 Feb 2011 09:42:19 -0800
Message-ID: <AANLkTikhiRL6m-a=vu9jOSYgDJ84jnsxBQMqaoa0fP27_at_mail.gmail.com>

Once in a while I am looking for something so tricky that I fail to see the obvious. This is one of those time. I feel like the guy who is looking for his glasses but fails to consider they are on his face. <Which is now red by they way>

Thanks for your time!
Regards,
Mike

On Tue, Feb 8, 2011 at 11:45 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> And to make explicit what is implicit in Dan's explanation, a WHERE
> condition that evaluates to UNKNOWN gives the same result as FALSE, because
> Oracle (and most SQL products, although sqlite has quirks) only returns a
> row when WHERE evaluates to TRUE.
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
> On 02/09/2011 03:04 AM, Daniel W. Fink wrote:
>
> 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 Wed Feb 09 2011 - 11:42:19 CST