はじめに
限定述語とNULL
SQLは、ALLとANYという 2つの限定述語を持っています。ですが、ANYは、IN句と同値なのであまり使われません。
そこで、比較的よく使われるALLの注意点を見ていきます。
限定述語ALL
ALLは、比較述語と併用して、以下のような意味を表します。
- 「〜全てと等しい」
- 「〜すべてよりも大きい」
以下のデータで、「Bクラスの東京在住民よりも年齢が若いAクラスの人」を、SQL（限定述語ALLと比較述語を併用）を使って表現します。
Schema SQL
CREATE TABLE members (
id INT NOT NULL PRIMARY KEY auto_increment,
name TEXT,
age INT,
city TEXT,
class_name TEXT
);
INSERT INTO members VALUES
(1, 'brown', 22, 'tokyo', 'A'),
(2, 'rally', 17, 'saitama', 'A'),
(3, 'boggie', 21, 'chiba', 'A'),
(4, 'saito', 22, 'tokyo', 'B'),
(5, 'tashiro', 23, 'tokyo', 'B'),
(6, 'yamada', 18, 'tokyo', 'B'),
(7, 'izumi', 19, 'chiba', 'B'),
(8, 'takeda', 20, 'chiba', 'B'),
(9, 'ishikawa', 19, 'kanagawa', 'B');
SQL
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < ALL (SELECT age FROM members WHERE class_name = 'B' AND city = 'tokyo')
Result
| id | name | age | city | class_name |
|---|---|---|---|---|
| 2 | rally | 17 | saitama | A |
rallyだけが選ばれました。
以下のNULLが含まれた場合の結果は空になります。
result
There are no results to be displayed.
これはなぜかというと、ALL述語が条件をANDで連結した論理式の省略形として定義されているからです。
意味わかりますか？実際のクエリを見て説明します。
こんなクエリ書きましたよね？
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < ALL (SELECT age FROM members WHERE class_name = 'B' AND city = 'tokyo')
サブクエリを実行して、年齢のリストを取得
SELECT * FROM members_A
WHERE age < ALL (22, 22, NULL)
ALL述語を、ANDで同値変換
SELECT * FROM members_A
WHERE (age < 22) AND (age < 22) AND (age < NULL)
おなじみ「比較演算子にNULL適応するとunknownになる」
SELECT * FROM members_A
WHERE (age < 22) AND (age < 22) AND (age < NULL)
AND unknown は FALSEかunknownにしかならない （trueにならない）
SELECT * FROM members_A
WHERE FALSE or unknown
だから1行も選ばれません。
限定述語と極値関数は同値ではない
極値関数とは
SQLのMAX関数やMIN関数など、データベース上にあるテーブルの中から、指定したグループの値極値（ex: 最大値 or 最小）が含まれるレコードを取得する関数のことです。
ALL述語を極値関数で代用
先ほど見たこのクエリ（クエリAとする）
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < ALL (SELECT age FROM members WHERE class_name = 'B' AND city = 'tokyo')
以下のような、極値関数で代用する人もいるのではないでしょうか？
👇 （クエリBとする）
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < ALL (SELECT MIN(age) FROM members WHERE class_name = 'B' AND city = 'tokyo')
result
| id | name | age | city | class_name |
|---|---|---|---|---|
| 2 | rally | 17 | saitama | A |
例えば、 tashiroのageがNULLだとしても上記のような結果になります。
なぜなら、極値関数は集計の際にNULLを排除するからです。
じゃあ、今後ずっと、極値関数で代用すればいいじゃんと言えばそうでもないのです。
上記2種類のクエリを日本語にすると
- クエリAは「Bクラスの東京在住民よりも年齢が若いAクラスの人」
- クエリBは「Bクラスの東京在住民の最年少者よりも年齢が若いAクラスの人」
です。
現実世界では同じ結果になりますが、クエリA（ALL述語）とクエリB（極限関数）が同値でなくなるケースがまだあります。
それが、述語、または関数の入力が空集合だった場合です。
両者の結果が変わってきます。
Schema
CREATE TABLE members (
id INT NOT NULL PRIMARY KEY auto_increment,
name TEXT,
age INT,
city TEXT,
class_name TEXT
);
INSERT INTO members VALUES
(1, 'brown', 22, 'tokyo', 'A'),
(2, 'rally', 17, 'saitama', 'A'),
(3, 'boggie', 21, 'chiba', 'A'),
(4, 'saito', 22, 'tokyo', 'B'),
(5, 'tashiro', NULL, 'tokyo', 'B'),
(6, 'yamada', 18, 'tokyo', 'B'),
(7, 'izumi', 19, 'chiba', 'B'),
(8, 'takeda', 20, 'chiba', 'B'),
(9, 'ishikawa', 19, 'kanagawa', 'B');
クエリA（ALL述語）
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < ALL (SELECT age FROM members WHERE class_name = 'B' AND city = 'aichi')
所在地が愛知の人がいない場合、ALL述語を使ったSQLはAクラスの全員を選択します。
クエリAの結果
| id | name | age | city | class_name |
|---|---|---|---|---|
| 1 | brown | 22 | tokyo | A |
| 2 | rally | 17 | saitama | A |
| 3 | boggie | 21 | chiba | A |
クエリB（極値関数）
WITH
members_A AS ( SELECT * FROM members WHERE class_name = 'A')
SELECT * FROM members_A
WHERE age < (SELECT MIN(age) FROM members WHERE class_name = 'B' AND city = 'aichi')
クエリBの結果
There are no results to be displayed.
なぜこうなるというと、極値関数の仕様で、入力が空集合だった場合はNULLを返すからです。
で、age < NULLになり、unknownになり、出力結果は0になります。
さいごに
比較対象が、存在しない場合、全行を返すか、1行も返さない
どちらがいいのかは、要件次第でしょう。
全行を返したい要件は、例えば、不戦勝のようなイメージでしょうか？
いずれにせよ、比較対象が空集合だった場合の出力結果の違いは覚えておきましょう。
参照
72 - 75p
アウトプット100本ノック実施中