Re: simple SQL queries give different results: Why?
Date: Wed, 09 Feb 2011 14:38:51 -0700
Message-ID: <4D53096B.1000905_at_optimaldba.com>
Oracle and the implementation of NULLs is anything but obvious (at least to me). And it is an easy one to forget about!
On 2/9/2011 10:42 AM, Michael Moore wrote:
> 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 <mailto: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 <http://www.avg.com>
>>> Version: 9.0.872 / Virus Database: 271.1.1/3430 - Release Date: 02/08/11 00:34:00
>>>
>>>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.872 / Virus Database: 271.1.1/3431 - Release Date: 02/08/11 12:34:00
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 09 2011 - 15:38:51 CST