はじめに

自分を戒めるためにNULLが入っているテーブルに対して、関数を実行するとどういう結果になるのか調べてみた。

なお、SQLはMySQLを使用しています。PostgreSQLやOracle、SQL Serverなどでは挙動が変わる可能性があります。

算術

NULLを使った算術は全てNULLになる。

SELECT NULL + 10, NULL - 10, 10 * NULL, 10 / NULL;

結果

NULL + 10	NULL - 10	10 * NULL	10 / NULL
NULL	NULL	NULL	NULL

絞り込み関数

絞り込み条件でよく使う関数を見ていきたいと思います。

IN

次のようなテーブルを作る。

-- 有名人テーブル
CREATE TABLE celebrities
(id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(255), 
 age INT);

-- 社員テーブル
CREATE TABLE personnel
(id int PRIMARY KEY AUTO_INCREMENT, 
 name varchar(255), 
 age int);

有名人(celebrities)

id	name	age
1	松本人志	55
2	明石家さんま	63
3	安室奈美恵	NULL

社員(personnel)

id	name	age
1	山田太郎	24
2	佐藤一	55
3	藤原正	60

社員の中で有名人と年齢が同じ人を探したい。

SELECT *
  FROM personnel
 WHERE age IN (
  SELECT age
    FROM celebrities
);

結果

id	name	age
2	佐藤一	55

有名人の年齢にNULLが入っているので、空集合が返ってくると思っていたが、一行返ってきた。

では、社員の年齢にNULLが入っていたらどうなるのか。

id	name	age
1	山田太郎	24
2	佐藤一	55
3	藤原正	60
4	近藤久義	NULL

同じクエリを実行する

結果

id	name	age
2	佐藤一	55

同じ結果になった。NULLは同じと判定されない。

NOT IN

SELECT *
  FROM personnel
 WHERE age NOT IN (
  SELECT age
    FROM celebrities
);

結果

id	name	age
一行も返ってこない。

EXISTS

上記と同じテーブルを用意。

celebrities

id	name	age
1	松本人志	55
2	明石家さんま	63
3	安室奈美恵	NULL

personnel

id	name	age
1	山田太郎	24
2	佐藤一	55
3	藤原正	60
4	近藤久義	NULL

EXISTSを使って、有名人と年齢が同じ社員を抽出

 SELECT *
   FROM personnel p
  WHERE EXISTS (
    SELECT *
      FROM celebrities c
	 WHERE p.age = c.age
  );

結果

id	name	age
2	佐藤一	55
INと同じ

NOT EXISTS

問題はNOT EXISTS

celebrities

id	name	age
1	松本人志	55
2	明石家さんま	63
3	安室奈美恵	NULL

personnel

id	name	age
1	山田太郎	24
2	佐藤一	55
3	藤原正	60
4	近藤久義	NULL

 SELECT *
   FROM personnel p
  WHERE NOT EXISTS (
    SELECT *
      FROM celebrities c
	 WHERE p.age = c.age
  );

結果

id	name	age
1	山田太郎	24
3	藤原正	60
4	近藤久義	NULL
NOT INとは結果が違う。

LIKE

次のテーブルを使用

celebrities

id	name	age
1	松本人志	55
2	明石家さんま	63
3	NULL	33

SELECT *
  FROM celebrities
 WHERE name LIKE '%'

結果

id	name	age
1	松本人志	55
2	明石家さんま	63

名前がNULLは抽出されない。

整列

ORDER BY

celebrities

id	name	age
1	松本人志	55
2	明石家さんま	63
3	安室奈美恵	NULL

SELECT *
  FROM celebrities
 ORDER BY age;

結果

id	name	age
3	安室奈美恵	NULL
1	松本人志	55
2	明石家さんま	63

NULLが一番最初になる。
DESC(降順)は逆になる。

集約関数

GROUP BY, COUNT

以下のテーブルを用意
students

CREATE TABLE students
(id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(255), 
 class VARCHAR(255));

students

id	name	class
1	小田切竜	A
2	神谷俊輔	A
3	内山春彦	B
4	沢田慎	B
5	熊井輝夫	NULL
6	毛利研一	NULL

クラスの人数を抽出

SELECT class, COUNT(*)
  FROM students
 GROUP BY class;

結果

id	COUNT(*)
NULL	2
A	2
B	2
NULLは一つに集約される。(本来はNULL同士は同じ値ではない。)

SUM

CREATE TABLE scores
(id INT, 
 subject VARCHAR(255), 
 score INT,
 PRIMARY KEY (id, subject));

students(前のを流用)

id	name	class
1	小田切竜	A
2	神谷俊輔	A
3	内山春彦	B
4	沢田慎	B

scores

id	subject	score
1	国語	60
1	英語	75
2	国語	77
2	英語	53
3	国語	NULL
3	英語	60
4	国語	90
4	英語	100

休みで国語のテストが受けられなかったのでNULLになってしまった。
生徒の合計得点を取得する

 -- MySQLではGROUP BYに入っていないカラムでもそのままSELECTで抽出できるが、一応MAXで他ベンダーにも対応
 SELECT MAX(st.name), SUM(score)
   FROM students st
  INNER JOIN scores sc
     ON st.id = sc.id
  GROUP BY st.id;

結果

MAX(st.name)	SUM(score)
小田切竜	135
神谷俊輔	130
内山春彦	60
沢田慎	190

NULLは0として合計されるらしい。
NULLの演算は必ずNULLになる(SELECT NULL + 10; => NULL)ので、NULLになるかと思った。

MIN, MAX, AVG

students

id	name	class
1	小田切竜	A
2	神谷俊輔	A
3	内山春彦	B
4	沢田慎	B

scores

id	subject	score
1	国語	60
1	英語	75
2	国語	77
2	英語	53
3	国語	NULL
3	英語	60
4	国語	90
4	英語	100

各教科の最低点と最高点と平均点を求める

 SELECT subject AS '教科', 
        MIN(score) AS '最低得点',
        MAX(score) AS '最高得点',
        AVG(score) AS '平均点'
   FROM students st
  INNER JOIN scores sc
     ON sc.id = st.id
  GROUP BY subject;

結果

教科	最低得点	最高得点	平均点
国語	60	90	75.6667
英語	53	100	72.000
NULL以外の最小値と最大値と平均が出てる。

国語はNULLがいるので3人の平均点が出ている。英語は4人の平均点が出ている。

集合演算

UNION, UNION ALL

a

value
a
a
b
c
NULL
b

value
b
b
c
d
NULL
NULL

SELECT value
  FROM a
 UNION
SELECT value
  FROM b;

結果

value
a
b
c
NULL
d
UNIONは重複行を排除する。NULLもまとめられてる。

SELECT value
  FROM a
 UNION ALL
SELECT value
  FROM b;

結果

value
a
a
b
c
NULL
b
b
c
d
NULL
NULL
UNION ALLは全部つなげる。NULLもそのまま。

まとめ

NULLは薬だと思ってほしい。正しく使っている限りは有用だが、乱用すればすべてをぶち壊す。最良の選択は、可能な限り使用を避けて、どうしても使わざるを得ないときだけ、適切に使用することだ。
ジョー・セルコ著ミック監訳『プログラマのためのSQL 第4版』 p 261

NULLの用法・容量は正しく。