Сравнение с значениями NULL

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

Во втором выборе выше я ожидаю запись "amar", потому что это имя не совпадает с первым, вторым или именем.

Ответы

Ответ 1

Любое сравнение с NULL дает NULL. Чтобы преодолеть это, можно использовать три оператора:

x IS NULL - определяет, имеет ли левое выражение выражение NULL,
x IS NOT NULL - как указано выше, но наоборот,
x <=> y - сравнивает оба операнда для равенства безопасным образом, т.е. NULL рассматривается как нормальное значение.

Для вашего кода вы можете захотеть использовать третий вариант и пойти с нулевым безопасным сравнением:

SELECT * FROM mycompare 
WHERE NOT(name <=> fname OR name <=> mname OR name <=> lname)

Ответ 2

Вы не можете использовать реляционные операторы с NULL. Единственными операторами, которые работают с NULL, являются IS NULL и IS NOT NULL.

Ответ 3

У меня была такая же проблема, когда я писал триггеры обновлений и хотел выполнить часть кода только тогда, когда два значения разные. Использование IS NULL и XOR оказалось полезным:

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

Итак, я закончил использование:

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

Ответ 4

Значения

NULL автоматически опущены, если вы выполняете сравнение значений (поскольку NULL не является значением). Ваше предложение where в основном означает: Сравните значения указанных полей, если они имеют значение в противном случае false.

Вам нужно будет включить отдельную проверку для NULL, если вы тоже хотите эти строки.

Ответ 5

Возможно, вам удастся избежать чего-то вроде следующего (предполагая, что отображение NULL в '' не является проблемой):

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