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;
ミドルネームがあるか、ないか、分からないので、なかったらどうなる？
- A
- JunyanChen
- B
- Junyan _ _ Chen
- C
- Junyan _ _ _ Chen
- D
- ””
- E
- その他
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;