本当は怖いNULLの世界(データベース)

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

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

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

### 結果
| NULL + 10 | NULL - 10 | 10 * NULL |10 / NULL |
| ---- | ---- | ---- | ---- |
| NULL | NULL | NULL | NULL |

# 絞り込み関数
絞り込み条件でよく使う関数を見ていきたいと思います。
## IN
次のようなテーブルを作る。

```sql
-- 有名人テーブル
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 |

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

```sql
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
```sql
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を使って、有名人と年齢が同じ社員を抽出

```sql
 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 |

```sql
 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 |

```sql
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 |

```sql
SELECT *
  FROM celebrities
 ORDER BY age;
```

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

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

# 集約関数
## GROUP BY, COUNT
以下のテーブルを用意
students

```sql
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 |

クラスの人数を抽出

```sql
SELECT class, COUNT(*)
  FROM students
 GROUP BY class;
```
### 結果

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

## SUM
```sql
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になってしまった。
生徒の合計得点を取得する

```sql 
 -- 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 |

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

```sql
 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 |

```sql
SELECT value
  FROM a
 UNION
SELECT value
  FROM b;
```

### 結果

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

```sql
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の用法・容量は正しく。