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: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; mysql@lists.mysql.com
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 15, 2006 2:22 PM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
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/[EMAIL PROTECTED]
______________________________________________________________
__________
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/[EMAIL PROTECTED]
________________________________________________________________________
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/[EMAIL PROTECTED]
