Re: simple SQL queries give different results: Why?

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 <mailto: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  <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

Other related posts: