List:General Discussion« Previous MessageNext Message »
From:mizioumt Date:November 17 2006 6:19pm
Subject:Re: finding NULL records
View as plain text  
Well, I'm not sure about the exact definition of 'all'.

NULL OR TRUE      /* result is TRUE, example (NULL = NULL) OR (1 = 1) */

and so on. Didn't try to execute the actual SQL with such a close, 
though.

Thanks,
Michael

-----Original Message-----
From: jschwartz@stripped
To: mizioumt@stripped; m.tonies@stripped; mysql@stripped
Sent: Wed, 15 Nov 2006 3:04 PM
Subject: RE: finding NULL records

The result of all but two expressions will be NULL (not 0 or 1) if 
either
side is NULL. The two exceptions are the operators IS NULL and IS NOT 
NULL.

SELECT NULL = NULL; /* result is NULL */
SELECT NULL IS NULL; /* result is 1, or TRUE */
SELECT NULL IS NOT NULL; /* result is 0, or FALSE */

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341


> -----Original Message-----
> From: mizioumt@stripped [mailto:mizioumt@stripped]
> Sent: Wednesday, November 15, 2006 2:22 PM
> To: m.tonies@stripped; mysql@stripped
> Subject: Re: finding NULL records
>
> trying to revive an old thread for fun...
>
> > I inherited a system in which the code was writing the
> string "NULL"
> into
> a
> > CHAR field.
>
> This is what happens when one of the most innovative concepts of SQL
> gets overlooked
> or understressed which it seems to be in most modern SQL books.
> The thing is that SQL's logic isn't binary. Rather it's built over
> three values: TRUE, FALSE, NULL.
> You can indeed compare anything to NULL:
>
> x = NULL
>
> in most systems is a valid syntax. The result is NULL if x is
> not NULL,
> and the result of
>
> NULL = NULL
>
> I honestly don't remember, but it's either NULL or FALSE, definitely
> TRUE.
> The same goes for any other binary operator I can imagine.
>
> x IS NULL
>
> on the other hand isn't a binary operator, it's an UNARY one with the
> result values of TRUE or FALSE.
>
> The whole join theory obviously revolves around NULL treatment but
> otherwise the NULL algebra becomes
> fuzzy. I'm not sure what the standards say to the question of sorting
> NULLs, or whether each null constitutes
> a separate group as in GROUP BY, or all can be grouped together, but
> the actual implementations
> often make the answers configurable.
>
> I wish SQL educators paid a little more attention to the math
> of it. On
> the other hand describing joins
> in a kind of 'traditional' mathematics [no pictures, just symbols] is
> definitely an overkill.
>
> Thanks,
> Michael
>
> It took me a long time to figure out what MySQL CLI was
> showing
> > me.
> >
> > These kids today...
>
> Oh my, sounds like a candidate for www.thedailywtf.com :-)
>
>
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> ______________________________________________________________
> __________
> Check Out the new free AIM(R) Mail -- 2 GB of storage and
> industry-leading spam and email virus protection.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>




________________________________________________________________________
Check Out the new free AIM(R) Mail -- 2 GB of storage and 
industry-leading spam and email virus protection.

Thread
finding NULL recordsAlan Nilsson31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsAlan Nilsson31 Oct
    • Re: finding NULL recordsChris31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • Re: finding NULL recordsMartijn Tonies31 Oct
RE: finding NULL recordsJay Blanchard31 Oct
  • RE: finding NULL recordsJerry Schwartz31 Oct
    • Re: finding NULL recordsMartijn Tonies31 Oct
      • Re: finding NULL recordsmizioumt15 Nov
        • RE: finding NULL recordsJerry Schwartz15 Nov
          • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsJoerg Bruehe16 Nov
            • Re: finding NULL recordsmizioumt17 Nov
          • Re: finding NULL recordsmizioumt17 Nov