mysql - Comparing with NULL values

Question

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.

Answer 1

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)