Comparing with NULL values
mysql
Solution
Any comparison with `NULL` yields `NULL`. To overcome this, there are three operators you can use:
- `x IS NULL` - determines whether left hand expression is `NULL`,
- `x IS NOT NULL` - like above, but the opposite,
- `x <=> y` - compares both operands for equality in a safe manner, i.e. `NULL` is seen as a normal value.
For your code, you might want to consider using the third option and go with the null safe comparison:
SELECT * FROM mycompare
WHERE NOT(name <=> fname OR name <=> mname OR name <=> lname)
Problem
``` CREATE TABLE `mycompare` ( `name` varchar(100) default NULL, `fname` varchar(100) default NULL, `mname` varchar(100) default NULL, `lname` varchar(100) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `mycompare` VALUES('amar', 'ajay', 'shankar', NULL); INSERT INTO `mycompare` VALUES('akbar', 'bhai', 'aslam', 'akbar'); INSERT INTO `mycompare` VALUES('anthony', 'john', 'Jim', 'Ken'); _____ SELECT * FROM mycompare WHERE (name = fname OR name = mname OR name = lname) akbar bhai aslam akbar select * from mycompare where !(name = fname OR name = mname OR name = lname) anthony john Jim Ken ``` In the second select above, I expect the "amar" record as well because that name does not match with First, second or last name.