Re: Understanding mysql NULL handling ...
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" <[EMAIL PROTECTED]> To: 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/[EMAIL PROTECTED] -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Understanding mysql NULL handling ...
On Wed, Jun 13, 2007 at 12:50:03PM -0700, Mufaddal Khumri wrote: > mysql> select * from t1 where sid != 2; > > 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 ? It is in accordance to the standard. NULL is not equal to anything, and it is not unequal to anything. NULL is just the magic ineffable NULL. If you want your NULL rows too you must use: SELECT * FROM t1 WHERE sid !=2 OR sid IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
