Re: simple SQL queries give different results: Why?

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 09 Feb 2011 08:45:46 +0100
Message-ID: <4D52462A.4010600_at_roughsea.com>

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 - 01:45:46 CST