SQLのNULL比較にはIS NULL演算子を使う ストアドファンクションでも同様

SQLのNULL比較方法についてまとめています。

SQLでNULLの比較をするには、WHERE句で通常の値と同じように条件指定するのではなくIS NULLやIS NOT NULLなどのNULL比較専用の演算子を使う必要があるんですね。

NULL比較はIS NULL演算子を使う

IS NULL演算子は、対象カラムがNULLのレコードのみ対象とするための特殊な演算子です。NULLは特殊な扱いなので=NULLという記述だと正しく意図した動きをしてくれません。

以下は、titlesテーブルの「to_date」カラムにNULLを設定したレコードを挿入し、IS NULLを使ってselectした例です。

mysql> desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int         | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
 
mysql> INSERT INTO `titles`(`emp_no`, `title`, `from_date`, `to_date`)  VALUES (10001, 'Staff', '1992-04-01', NULL);
Query OK, 1 row affected (0.00 sec)
 
select * from titles where to_date IS NULL;
+--------+-------+------------+---------+
| emp_no | title | from_date  | to_date |
+--------+-------+------------+---------+
|  10001 | Staff | 1992-04-01 | NULL    |
+--------+-------+------------+---------+
1 row in set (0.15 sec)

【関連記事】
▶MySQLのNULL判定はどうやる？ 除外判定やNULLのSUMを取得するには？

ストアドファンクション内でもIS NULL演算子が使えます。

SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION CheckNULL(name varchar(30)) 
RETURNS VARCHAR(20)
BEGIN 
	declare msg varchar(20);
	declare depname varchar(20);
 
	select dept_name into depname from departments where dept_no=num;
 
	if depname IS NULL then set msg = 'NULL';
	else set msg=depname;
	END IF;
	return msg;
END
//
DELIMITER ;

【関連記事】
▶ SQLのif文でNULL判定するサンプルコード

1行目のSET GLOBAL log_bin_trust_function_creators = 1は、エラー回避用に設定しています。1行目の設定をしないと、以下のエラーが出力されます。

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

デフォルトでは、CREATE FUNCTION ステートメントを実行するためにDETERMINISTIC、NO SQL、または READS SQL DATA の少なくとも 1 つを明示的に指定する必要があるんですね。

参考）MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング

NOT INでNULLは使えない、AND演算子でIS NOT NULL条件を追加

NOT INにNULLを含めると「どんなデータにもマッチしない」という条件になってしまいます。例えば、Sales、Development、NULL以外という条件を指定するには、NOT IN ( ‘Sales’, ‘Development’ )
AND dept_name IS NOT NULLのように記述します。

SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sales', 'Development', NULL );
 
Empty set (0.00 sec)
 
SELECT * 
FROM   `departments` 
WHERE  dept_name NOT IN ( 'Sales', 'Development' ) 
       AND dept_name IS NOT NULL;
 
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
+---------+--------------------+
7 rows in set (0.00 sec)

【関連記事】
▶SQL not in データの除外指定　NULLや複数カラムを扱う方法

まとめ

ポテパンダの一言メモ

• NULL判定をするにはIS NULL演算子を使う
• ストアドファンクション内でもIS NULLを使ってNULL比較が可能
• NOT INでNULLを含めるには、AND演算子でIS NOT NULLを使う