はじめに
自分を戒めるために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の用法・容量は正しく。