MySQLの文字列で「NULLも含めて一致しない」条件

文字列で「NULLも含めて一致しない」条件

• 環境 : Mysql 8.0.32

とても基本的なことだが「NULL」の比較をすぐに忘れてしまう。

文字列で「NULLも含めて一致しない」条件

select * from table where not (columnA<=>columnB);

「<>」は比較対象に「NULL」があると判定結果が「NULL」になってしまう

不等価演算子は、「<>」だが比較対象にNULLがあるとNULLが返却されてしまう。

=、<、または <> などの算術比較演算子を使用して NULL をテストすることはできません。
...省略...
MySQL では、0 や NULL は false を意味し、それ以外はすべて true を意味します。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.3.4.6 NULL 値の操作

select 'a'<>'b','a'<>'','a'<>0,'a'<>false,'a'<>null,null<>null;

'a'<>'b'	'a'<>''	'a'<>0	'a'<>false	'a'<>null	null<>null
1	1	0	0	« NULL »	« NULL »

「is not null」を使うこともできるが「どらかがNULLでどちらかがNULL以外」を「一致しない」と判定する条件を書くのが面倒くさい

「NULLではない」は「is not null」で確認できる。

select 'a' is not null,''  is not null,0  is not null,false is not null,null  is not null;

'a' is not null	'' is not null	0 is not null	false is not null	null is not null
1	1	1	1	0

「どらかがNULLでどちらかがNULL以外」を「一致しない」と判定する条件を書くと面倒くさい。

select * from table where
-- 左辺がnull以外で右辺がnullの場合
(columnA is not null and columnB is null)
or
-- 左辺がnullで右辺がnull以外の場合
(columnA is null and columnB is not null)
or
-- 両方null以外で値が一致しない場合
(columnA is not null and columnB is not null and columnA<>columnB)
;

「NULLも含めて一致」を確認するには「<=>」を使う

<=>
NULL - 安全等価。 この演算子では、= 演算子のように等価比較が実行されますが、両方のオペランドが NULL であれば、NULL でなく 1 が返され、一方のオペランドが NULL の場合は、NULL でなく 0 が返されます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.2 比較関数と演算子

比較するカラムが文字列でないと判定がうまくいかないので注意。
「0(数値)」「false(ブール)」は対応できない。

select 'a'<=>'b','a'<=>'','a'<=>0,0<=>0,'a'<=>false,'a'<=>null,null<=>null;

'a'<=>'b'	'a'<=>''	'a'<=>0	0<=>0	'a'<=>false	'a'<=>null	null<=>null
0	0	1	1	1	0	1

「NULLも含めて一致」を否定して「NULLも含めて一致しない」にする

NOT, !
NOT 演算。 オペランドが 0 の場合は 1 に、オペランドがゼロ以外の場合は 0 にそれぞれ評価され、NOT NULL の場合は NULL が返されます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.4.3 論理演算子

select not('a'<=>'b'),not('a'<=>''),not('a'<=>0),not(0<=>0),not('a'<=>false),not('a'<=>null),not(null<=>null);

not('a'<=>'b')	not('a'<=>'')	not('a'<=>0)	not(0<=>0)	not('a'<=>false)	not('a'<=>null)	not(null<=>null)
1	1	0	0	0	1	0