MySQLのNULLの扱いについて、サンプルSQLを紹介しながらまとめています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。
サンプルデータベースのインストール方法は、以下の記事を参考にしてください。
【関連記事】
▶MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的
MySQLでNULL判定するサンプルSQL
NULL判定はIS NULLを使う
employeesデータベースの、titles（役職）テーブルを使って、NULL判定をおこなってみましょう。titlesテーブルのto_dateカラムは、NULLが許可されています。
mysql> desc titles; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | emp_no | int(11) | 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)
以下のSQLを実行し、NULLのカラムを持つデータをinsertします。
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)
以下のSQLで、to_dateカラムがNULLのレコードをselectできます。
mysql> 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)
【関連記事】
▶SQL isnullの使い方 NULL判定やNULL置換の方法 case式でも対応可能
ストアドプロシジャ、ストアドファンクション内のIF文でも、IS NULLを使ってNULL判定が可能です。
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; END DELIMITER ;
【関連記事】
▶SQL if文のサンプルコード集 NULL判定や、Switch文のような分岐をする方法
NULLの除外（否定）判定をするには、IS NOT NULL
NULLの除外判定をするには、IS NOT NULLを使います。ただし、例外としてNOT INと組み合わせる場合は要注意。
NOT IN （’Sales’, “Development’, NULL )と、NULLと他の値を同じように条件に指定すると、「何にもマッチしない」という結果になります。
意図した動きをさせるには、以下のように他の条件とNULL関連の条件を分けて指定する必要があります。
SELECT *
FROM `departments`
WHERE dept_name NOT IN ( 'Sales', 'Development' )
AND dept_name IS NOT NULL
【関連記事】
▶SQL not in データの除外指定 NULLや複数カラムを扱う方法
NULLの含まれるカラムの合計（SUM)を取得するには、COALESCE関数を使う
対象のカラムにNULLが含まれている場合、group byでSUMを取得すると結果がNULLになるケースがあります。
この現象を回避するためには、COALESCE関数を使います。COALESCE関数は、NULL値を別の値に置き換えることが可能。COALESCE（salary,0)は、カラムsararyがNULLの場合、NULLの代わりに0を取得します。
以下のSQLは、salaries（年収）テーブルから、営業部（Sales）の年ごとの合計を取得するSQLです。
salaryカラムにNULLが含まれている場合でも、正しく集計値を取得できます。
select Date_format(birth_date, '%Y') as year,
Sum(COALESCE(salary,0))
from employees
inner join salaries
on salaries.emp_no = employees.emp_no
inner join dept_emp
on employees.emp_no = dept_emp.emp_no
inner join departments
on dept_emp.dept_no = departments.dept_no
where salaries.to_date = '9999-01-01'
and dept_name = 'Sales'
group by Date_format(birth_date, '%Y')
実行結果はこうなります。
+------+-------------------------+ | year | Sum(COALESCE(salary,0)) | +------+-------------------------+ | 1952 | 266268511 | | 1953 | 279889946 | | 1954 | 284458184 | | 1955 | 292812067 | | 1956 | 288620912 | | 1957 | 284076293 | | 1958 | 294605154 | | 1959 | 289944673 | | 1960 | 281743172 | | 1961 | 282715210 | | 1962 | 290871654 | | 1963 | 289909858 | | 1964 | 281566480 | | 1965 | 23888563 | +------+-------------------------+ 14 rows in set (0.73 sec)
SUM以外のグループ関数AVGや、MAX、MINなどでも同様です。
【関連記事】
▶SQL sumのサンプルコード集 基本の小計出力から、NULL対策まで
まとめ
- NULL判定をおこなうには、IS NULLを使う
- NOT INでNULLを使う場合は、一般の式とNULLの判定文を分けて記述する
- NULLの含まれるカラムのSUM、AVG、MAXなど集計値を取得する場合は、COALESCE関数を使う
