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: <mysql@lists.mysql.com>
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]
