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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
