Comparing with NULL values

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.

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)

You cannot use relational operators with NULL. The only operators that work with NULL are IS NULL and IS NOT NULL.

I had the same problem when I was writing update triggers and wanted to execute portion of code only when two values different. Using IS NULL and XOR came in handy:

SELECT 1!=1;                          -- 0
SELECT 1!=2;                          -- 1
SELECT 1!=NULL;                       -- NULL
SELECT NULL!=NULL;                    -- NULL
SELECT NULL IS NULL XOR 1 IS NULL;    -- 1
SELECT NULL IS NULL XOR NULL IS NULL; -- 0
SELECT 1 IS NULL XOR 1 IS NULL;       -- 0

So I've ended up using:

IF (OLD.col != NEW.col) OR ((OLD.col IS NULL) XOR ( NEW.col IS NULL)) THEN ...

NULL values are omitted automatically if you do a value comparison (because NULL isn't a value). Your where clause basically means: Compare the values of the mentioned fields, if they have a value—otherwise false.

You'd have to include a separate check for NULL if you want those rows, too.

You could probably get away with something like the following (assuming that mapping NULL to '' is not a problem):

SELECT * FROM mycompare 
WHERE (ifnull(name,'') = ifnull(fname,'') 
       OR ifnull(name,'') = ifnull(mname,'') 
       OR ifnull(name,'') = ifnull(lname,''));

select * from mycompare 
where !(ifnull(name,'') = ifnull(fname,'') 
        OR ifnull(name,'') = ifnull(mname,'')  
        OR ifnull(name,'') = ifnull(lname,''));