Re: simple SQL queries give different results: Why?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: "Daniel W. Fink" <daniel.fink@xxxxxxxxxxxxxx>
- Date: Wed, 9 Feb 2011 13:49:47 -0800
Daniel,
I agree, when I first started working with SQL 17 years ago it was not
obvious. But now, having written 100's of 1000's of lines of code, I really
have no excuse. LOL ;-)
Regards,
Mike
On Wed, Feb 9, 2011 at 1:38 PM, Daniel W. Fink
<daniel.fink@xxxxxxxxxxxxxx>wrote:
> 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@xxxxxxxxxxxx>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
>>
>>
>>
>>
>
>
> 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
>
>
>
Other related posts:
