Re: simple SQL queries give different results: Why?

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

Other related posts: