複数行副問合せを復習するついでにSQLアンチパターンについて考えた

SQL文に自信がなかったので1年前くらいに買ったスッキリわかるSQL入門 第2版 ドリル222問付き！ - インプレスブックス(以下、本A)を復習した。
特に第7章「副問合せ」の練習問題7-3の3を解いてみて、あー、なるほどと思ったのでメモする。

まずは、カラムなどは本と違うけど、データを用意する。
ちなみに、名前の作成は、日本人名前自動生成機 -- 高樹凱.COMで行った。

create table family (
  id int,
  name varchar(10), 
  profession varchar(10), 
  parent_id int
);

中身はこうなっている。

select * from family;

やりたいことは、

「母親の職業がミュージシャンである人のidと名前を抽出したい」

である。

つまり、答えは以下のようになる。

ここで普段ほとんどアプリケーションのコードしか書かないので混乱する。

脳内でアルゴリズムを考えると、まず professionが ミュージシャン である人のidを配列に格納し、parent_idがその配列に格納されている人のnameを出力する、という感じ。

けど、SQL文に配列はない。
ここで登場するのが、副問合せ、特に複数行副問合せ である。

実際に書いてみると、こうなる。

select id, name from family 
where parent_id in (select id from family 
                    where profession = 'ミュージシャン');

複数行副問合せとは、本によると、以下のようなものである。

検索結果が複数の行からなる単一列(結果がn行1列)の値となる副問合せをさします。...
SQL文中で「複数の値を列挙」するような場所に、その代わりとして記述することができます。
具体的には、IN, ANY, ALL演算子を用いた条件式が代表的な事例です。

今回の例では、単一列は、idの列で、結果は「2, 3, 5」の3行である。
また、「複数の値を列挙するような場所」とは、さっきの脳内アルゴリズムの配列のことである。

問題の回答はここで終わりだけど、最近はO'Reilly Japan - SQLアンチパターン(以下、本B)も読んでいるため、作ったテーブルの妥当性を軽く検証する。

create table family (
  id int,
  name varchar(10), 
  profession varchar(10), 
  parent_id int
);

まずそもそもこのテーブルは、母親と子供の関係だけを格納していて、有用性があまり無い。(本Aの問題では母牛だった)
例えば、後から父親の情報も入れたい、となったときはどうだろう。
parent_idを消してmother_id, father_id のカラムを追加したとしても、1つのテーブルで親子関係管理するのは無理があることは想像しやすい。
そうならないように、テーブルを分割する必要がある。

create table child (
  id int,
  name varchar(10), 
  profession varchar(10), 
  parent_id int
);

create table mother (
  id int,
  name varchar(10), 
  profession varchar(10)
);

ここで、本Bの「4.2 アンチパターン: 外部キー制約を使用しない」に当てはまらないよう、外部キーを追加する。

create table child (
  id serial primary key,
  name varchar(10), 
  profession varchar(10), 
  parent_id int,
  foreign key (parent_id) references mother(id)
);

create table mother (
  id serial primary key,
  name varchar(10), 
  profession varchar(10)
);

こうすると、先ほどの副問合せは、

select id, name from child
where parent_id in (select id from mother
                    where profession = 'ミュージシャン');

となる。

次に、本Bの「10.2 アンチパターン:限定する値を列定義で指定する」について考える。
今回の場合、職業として選択されているのは、「公務員」と「ミュージシャン」である。
このような選択肢だけのアプリケーションは存在しないだろうけど、「学生」「公務員」「アルバイト」...といったような選択肢がプルダウンボックスで表示されるアプリケーションは多々あると思う。
そして、今のままのテーブルでは、

insert into child (profession) values ('ばなな');

というようなprofessioへの意味不明な挿入がエラーで弾かれない。
無効なデータ入力を拒絶するために、check制約やenum制約をつけることがある。

create table child (
  id serial primary key,
  name varchar(10), 
  profession enum('公務員', 'ミュージシャン'), 
  parent_id int,
  foreign key (parent_id) references mother(id)
);

しかし、これは本Bではアンチパターンとされていて、professionの追加や廃止に弱い。
解決策とされているのは、参照テーブルを新たに作成することである。
結果、以下のようになる。

create table child (
  id serial primary key,
  name varchar(10), 
  profession varchar(10), 
  parent_id int,
  foreign key (parent_id) references mother(id)
  foreign key (profession) references profession(profession)
);

create table profession {
  profession varchar(20) primary key
}

insert into profession (profession) values ('公務員'), ('ミュージシャン')

create table mother (
  id serial primary key,
  name varchar(10), 
  profession varchar(10)
  foreign key (profession) references profession(profession)
);

こんな感じで、正しいかどうかはさておき、独学中も自分であれこれ考えられるようになった(気がする)。