Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Comparing with NULL values

Tags:

mysql

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.

like image 265
shantanuo Avatar asked Mar 06 '10 15:03

shantanuo


People also ask

Can we compare the value with NULL?

You can't compare with NULL. You need is. null to test if something is a reference to the NULL object. @CarlesMitjans the variable is not always NULL, normally it has another integer value.

Can we compare 2 NULL values in SQL?

SQL has the is [not] null predicate to test if a particular value is null . With is [not] distinct from SQL also provides a comparison operator that treats two null values as the same.

What would be the result if the two NULL values were compared?

Because null is considered to be unknown, two null values compared to each other are not considered to be equal. In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

Which comparison operator is used for comparing NULL values?

ISNULL() can be used instead of = to test whether a value is NULL . (Comparing a value to NULL using = always yields NULL .) The ISNULL() function shares some special behaviors with the IS NULL comparison operator.


1 Answers

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)
like image 106
Ja͢ck Avatar answered Sep 24 '22 11:09

Ja͢ck