List:General Discussion« Previous MessageNext Message »
From:ViSolve DB Team Date:June 14 2007 6:10am
Subject:Re: Understanding mysql NULL handling ...
View as plain text  
Hi,

Because "NULL is not a value".  Operators [!=] must be suceeded by a value. 
These orphans must be selected in a correct way using "is" clause.

Thanks
ViSolve DB Team

----- Original Message ----- 
From: "Mufaddal Khumri" <mkhumri@stripped>
To: <mysql@stripped>
Sent: Thursday, June 14, 2007 1:20 AM
Subject: Understanding mysql NULL handling ...


> Hello,
>
> I am using Ver 8.41 Distrib 5.0.27, for apple-darwin8.5.1 on i686
>
> ==================
> My table definition:
> ==================
>
> mysql> show create table t1;
> +------- 
> +----------------------------------------------------------------------- 
> ------------------------------------------------------------------------ 
> ----------------------------------------+
> | Table | Create  Table 
> |
> +------- 
> +----------------------------------------------------------------------- 
> ------------------------------------------------------------------------ 
> ----------------------------------------+
> | t1    | CREATE TABLE `t1` (
>   `id` int(11) NOT NULL auto_increment,
>   `sid` int(11) default NULL,
>   `tx` varchar(10) default NULL,
>   PRIMARY KEY  (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
> +------- 
> +----------------------------------------------------------------------- 
> ------------------------------------------------------------------------ 
> ----------------------------------------+
> 1 row in set (0.00 sec)
>
> ==================
> The data in my table:
> ==================
>
> mysql> select * from t1;
> +----+------+-------+
> | id | sid  | tx    |
> +----+------+-------+
> |  1 |    1 | hello |
> |  2 |    2 | hello |
> |  3 | NULL |       |
> |  4 | NULL | NULL  |
> |  5 |    5 | hello |
> +----+------+-------+
> 5 rows in set (0.00 sec)
>
> ==================
> The query I execute:
> ==================
>
> mysql> select * from t1 where sid != 2;
> +----+------+-------+
> | id | sid  | tx    |
> +----+------+-------+
> |  1 |    1 | hello |
> |  5 |    5 | hello |
> +----+------+-------+
> 2 rows in set (0.00 sec)
>
>
> As you can see, the rows that had sid = NULL did not get returned in  the 
> results when i did "... where sid != ; "
>
> Question: Is this behaviour correct and is in accordance to the SQL 
> standard or is it specific to MySQL ?
>
> Thanks.
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
>
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Free Edition. Version: 7.5.472 / Virus Database: 
> 269.8.15/847 - Release Date: 6/12/2007 9:42 PM
>
> 

Thread
Understanding mysql NULL handling ...Mufaddal Khumri13 Jun
  • Re: Understanding mysql NULL handling ...Jon Ribbens13 Jun
  • Re: Understanding mysql NULL handling ...ViSolve DB Team14 Jun