「大人の教養・知識・気付き」を伸ばすブログ

一流の大人(ビジネスマン、政治家、リーダー…)として知っておきたい、教養・社会動向を意外なところから取り上げ学ぶことで“気付く力”を伸ばすブログです。目下、データ分析・語学に力点を置いています。

MENU

SQL中級者になる!(その04/X)

初めに

 データベースとSQLは現在のデータサイエンスに必須な知識・スキルの1つ。その基礎を


power-of-awareness.com


を基に学んできた。
 この知識をより昇華させてSQL中級者になりたい!ため、同じ著者の

を参考に、更なるSQLの知識を拡充していこう。

4. 3値論理とNULL

 \mathrm{SQL}は他のプログラミング言語とは異なり、3値論理を採用している。すなわち\mathrm{true},\mathrm{false},\mathrm{unknown}の3値からなる論理型を採用している。これは\mathrm{NULL}を採用したことで導入せざるを得なかったものだった。

4.1 NULLとは

 \mathrm{NULL}は値でも変数でもない値が無いことを表す「記号」である。そのため型という概念が無く、これに対する比較述語(等号や不等号)は\mathrm{unknown}になるために値を判定することができない*1

4.2 unknown

 3値論理では論理表が以下のとおりになる:

 
\mathrm{X}
\mathrm{NOT\ X}
  \mathrm{AND}
t
u
f
  \mathrm{OR}
t
u
f
 
t
f
 
t
t
u
f
 
t
t
t
t
 
u
u
 
u
u
u
f
 
u
t
u
u
 
f
t
 
f
f
f
f
 
f
t
u
f

4.3 NOT INとNOT EXISTS

 パフォーマンスのチューニングのために\mathrm{NOT\ IN}\mathrm{NOT\ EXISTS}を置き換えることがある。しかしこれらの結果が一致しない場合がある。

  Class_A        Class_B    
 
name

(名前)
age

(年齢)
city

(住所)
  
name

(名前)
age

(年齢)
city

(住所)
 
ブラウン
22
東京   
斎藤
22
東京
 
ラリー
19
埼玉   
田尻
23
東京
 
ボギー
21
千葉   
山田
東京
          
和泉
18
千葉
          
武田
20
千葉
          
石川
19
神奈川

 これらのテーブルに対して「Bクラスの東京在住の生徒と年齢が一致しないAクラスの生徒」を選択するクエリを考える。

-- これは上手くいかない
SELECT *
FROM Class_A
WHERE age NOT IN
(SELECT age
 FROM Class_B
 WHERE city = '東京')

これは途中で\mathrm{NOT\ IN}(22,23,\mathrm{NULL})となるため、上手くいかなくなる。
 正しい結果を得るには、\mathrm{EXISTS}句を用いる。

-- これは上手くいかない
SELECT *
FROM Class_A AS A
WHERE age NOT EXISTS
(SELECT age
 FROM Class_B AS B
 WHERE A.age = B.age
        AND B.city = '東京')

4.4 限定述語とNULL

 \mathrm{SQL}\mathrm{ALL}\mathrm{ANY}という2つの限定述語を持っている*2
 \mathrm{ALL}は比較述語と併用して「~すべてと等しい」や「~すべてよりも大きい」の意味を持つ。

  Class_A        Class_B    
 
name

(名前)
age

(年齢)
city

(住所)
  
name

(名前)
age

(年齢)
city

(住所)
 
ブラウン
22
東京   
斎藤
22
東京
 
ラリー
19
埼玉   
田尻
23
東京
 
ボギー
21
千葉   
山田
18
東京
          
和泉
18
千葉
          
武田
20
千葉
          
石川
19
神奈川
-- Bクラスの東京在住の誰よりも若いAクラスの生徒を選択する
SELECT *
FROM Class_A
WHERE age < ALL
(SELECT age
 FROM Class_B
 WHERE city = '東京')

これも\mathrm{NOT\ IN}と同じように\mathrm{NULL}を持っていると上手く動作しない。

4.5 極値関数とNULL

 極値関数で限定述語を代用する場合、厳密には全く同一の結果を返すわけではないことに注意する。

-- 
SELECT *
FROM Class_A
WHERE age < (SELECT MIN(age)
                    FROM Class_B
                    WHERE city = '東京')

 基本的には同じような結果を返す一方で、述語(または関数)の入力が空集合だと結果が異なる。\mathrm{ALL}述語を用いるとすべての行を選択する一方で、極値関数は空テーブルに対して\mathrm{NULL}を返す。要件次第で何れを用いるかを検討する。

4.6 集約関数とNULL

 入力が空テーブルだった場合に\mathrm{NULL}を返すのは集約関数も同様である。

4.7 まとめ

  • \mathrm{NULL}は値ではない。
  • 値ではないので述語も値の陽には適用できない。
  • 無理やり適用すると\mathrm{unknown}が生じる。
  • \mathrm{unknown}が論理演算に紛れ込むと\mathrm{SQL}が直観に反する動作をする。
  • これらに対処すべく、段落的なステップに分けて\mathrm{SQL}の動作を追うことが有効である。

*1:そのため\mathrm{IS\ NULL}を用いる。

*2:\mathrm{ANY}\mathrm{ALL}と同値なので殆ど利用されない。

プライバシーポリシー お問い合わせ