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;

ミドルネームがあるか、ないか、分からないので、なかったらどうなる？

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;