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.

Ja͢ck · Accepted Answer · 2012-11-25 17:45:59Z

32

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)

edited Nov 25, 2012 at 17:45

answered Oct 19, 2012 at 4:15

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IvanRF Over a year ago

More info: MySQL Comparison Functions and Operators

Ignacio Vazquez-Abrams · Accepted Answer · 2010-03-06 16:01:18Z

6

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

answered Mar 6, 2010 at 16:01

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Comments

Vyktor · Accepted Answer · 2014-04-11 11:28:21Z

3

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 ...

answered Apr 11, 2014 at 11:28

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Comments

Joey · Accepted Answer · 2010-03-06 15:59:35Z

2

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.

answered Mar 6, 2010 at 15:59

Joey

357k88 gold badges704 silver badges699 bronze badges

Comments

Martin · Accepted Answer · 2010-03-06 19:19:20Z

1

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,''));

answered Mar 6, 2010 at 19:19

Martin

10k6 gold badges39 silver badges36 bronze badges

Collectives™ on Stack Overflow

Comparing with NULL values

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related