13章 Fear of the Unknown(恐怖のunknown)

## 13.0 起因
bugsのDBで、名前をSelectすること。
`````
SELECT first_name || ' ' || last_name AS full_name FROM Accounts;
`````

Junyan Chen

ミドルネームを追加する要望

`````
ALTER TABLE Account ADD COLUMN middle_initial CHAR(2);

UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM Accounts;
`````

ミドルネームがあるか、ないか、分からないので、なかったらどうなる？
<dl>
  <dt>A</dt>
  <dd>JunyanChen</dd>
  <dt>B</dt>
  <dd>Junyan _ _ Chen</dd>
  <dt>C</dt>
  <dd>Junyan _ _ _ Chen</dd>
  <dt>D</dt>
  <dd>””</dd>
  <dt>E</dt>
  <dd>その他</dd>
</dl>
## 13.1 目的： Distinguish Missing Values
Nullの意味

* 分からない値の追加する時：課程テーブルに学生を登録する時、成績カラムがNULL
* 意味がない値：個人情報を登録する時、ミドルネームや前名とか
* 関数の結果がNull：DAY('2014−09−32')
* Outer Joinした時、マーチされなかったら、NullをPlaceholderにする

## 13.2 アンチパターン：Nullを普通の値にする
* SQL: 特殊な値
* Oracle, Sybase :　Lengthが０の空文字列 

## 13.2.1 表現中のNULL
hoursがNULLの時
`````
SELECT hours + 10 FROM Bugs;
`````
結果がNULL
## 13.2.2 NULLの行を検索
Nullの行を検索したい
`````
SELECT * FROM Bugs WHERE assigned_to = 123;
SELECT * FROM Bugs WHERE NOT (assigned_to = 123);
`````
どっちでも返しない
`````
SELECT * FROM Bugs WHERE assigned_to = NULL;
SELECT * FROM Bugs WHERE assigned_to <> NULL;
`````
ブーブー
## 13.2.3 NULLをパラメータにする
`````
SELECT * FROM Bugs WHERE assigned_to = ?;
`````
PlaceHolderでする時もブーブーだ
## 13.2.4 NULLを避ける
避けろおと
`````
CREATE TABLE Bugs (
  bug_id          SERIAL PRIMARY KEY,
  -- other columns
  assigned_to     BIGINT UNSIGNED NOT NULL,
  hours           NUMERIC(9,2) NOT NULL,
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id)
);
`````
”未定義”の時が、特別なPlaceHolderが必要です。
NULLを避けるため、−１にする
`````
INSERT INTO Bugs (assigned_to, hours) VALUES (-1, -1);
SELECT AVG(hours) AS average_hours_per_hug FROM Bugs WHERE hours <> -1;
`````
余計なロジックが必要
まだ齟齬がある
SUMとかAVGすると、誤りが発生
assigned_toを外鍵する時、MATCHしましたか分からない

##13.3 アンチパターンの見つけ方
* NULLの行をSelectできないって
* DBにある行をSelectすると、結果が見えないって
* Select＋Whereの結果が一部分しかないって
* 特殊な値を利用したので、コストが発生したって

##13.4 アンチパターンを用いても良い場合
NULLの使用がアンチパターンじゃない
NULLを普通の値として使うのがアンチパターンだ。
* Textの導入（mysqlimport）\N がNULLを代表する
* ConvertEmptyString-ToNull　(.NET)
* 複数の特殊値が有る場合。例えば、”アサインしなかった”　と　”アサインした人がいない”
##13.5 解決策：NULLを特殊値にする

## 13.5.1 スケラー表現中のNULL：
|expression|expected|actual|because|
|:---------|:----------|:----|:------|
|NULL=0|TRUE|NULL| Null is not 0|
|NULL=12345|FALSE|NULL| Unknown if the unspecified value is equal to a given value|
|NULL<>12345|TRUE|NULL| Also unknown if it is unequal|
|NULL+12345|12345|NULL| Null is not 0|
|NULL ｜｜ 'string'|'string'|NULL| Null is not an empty string|
|NULL = NULL|TRUE|NULL| Unknown if one unspecified value is the same as another|
|NULL <> NULL|FALSE|NULL| Also unknown if they are different|

## 13.5.2 ブール表現中のNULL：
|expression|expected|actual|because|
|:---------|:----------|:----|:------|
|NULL AND TRUE|FALSE|NULL| NULL is not false|
|NULL AND FALSE|FALSE|FALSE| Any truth value AND FALSE is false|
|NULL OR FALSE|FALSE|NULL| NULL is not false|
|NULL OR TRUE|TRUE|TRUE| NULL is not 0|
|NOT(NULL) |TRUE|NULL| NULL is not false|

## 13.5.3 NULLの行を検索

一番正しい
`````
SELECT * FROM Bugs WHERE assigned_to IS NULL;
SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;
`````
これも行ける
`````
SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;
SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;
`````
IS DISTINCT FROMは
PostgreSQL, IBM DB2, and Firebird が支える
Oracle and　Microsoft SQL Server　が支えない

MySQLの場合、<=>
補足
`````````
'a' IS NULL     ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)
```````````````
## 13.5.3 NOT NULLの宣言
下記のパータンが必ずNOT NULLしたほうが
* 必須項目：reported_by
* Index：id
* 時間系：created_at
* status系：enum

## 13.5.3 Dynamic Defaults
最初の問題がまだ解決できてない
`````
SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM Accounts;
`````
結局、NULLになった。
ダメだ！

ところが、
Func COALESCE(value,...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce
`````
SELECT first_name || COALESCE(' ' || middle_name || ' ', '' ) || last_name AS full_name FROM Accounts;
`````