Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

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が0の空文字列

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を避けるため、−1にする

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;

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした