こんにちは、ECF Techブログ
担当のMichiharu.Tです。
本記事はSQL文について、実際に動作させながら学習する入門記事の第5回となります。今回はリレーショナルデータベースの最重要項目ともいえる表の結合について見ていきましょう。よろしくお願いいたします。
本記事の題材となっているデータベース内の各表、および学習の始め方については、下記の第1回の記事からご覧ください。
リレーション
表の結合を学ぶにあたり、リレーショナルデータベースの名前のもとになっている 「リレーション(Relation)」 についてご説明します。リレーションは「関係」という意味を持つ英単語で、RDBでは 表と表の関係のこと をリレーション、または 関連 と呼んでいます。
第1回の記事でもご紹介した下の図ですが、このとき「学生表と学科表には関連がある」などと表現したりします。
主キーと外部キー
この関連を表現する上で重要となる概念が、 主キー と 外部キー です。下に概要を示します。
- 主キー：表の中から1つの行を特定するのに使用する列、および列の組み合わせのこと。そのため重複した値を入れることができない。
- 外部キー：他の表の主キーを参照するために用いる列のこと、表と表の関連を表す際に用いる。
学生表と学科表の例でみてみましょう。
学生表における 学籍番号 は、表内において学生1人分の情報（レコード）をただ1つに特定するための項目で、学生表の主キーとなります。学部表の 学部番号 も同様です。一方で学生表の 学部番号 は、学部表の主キーである学部番号と関連性を持たせるための項目です。2つの表の学部番号を関連づけて見ることによって、たとえば「徳川家康は工学部である」ということが表現できるわけです。
今回、サンプルデータベースに格納している表について、主キー、外部キーの関係を下に示します。
注目すべき点がいくつかあります。1つめは注文明細表の主キー列です。注文明細表は単一の列で行をただ1つに特定できないため、 注文IDと明細Noの2列で主キーとしています。このように複数の列を用いて主キーとすることも可能で、特に 複合キー と表現する場合もあります。
もう1つは注文明細表の 注文ID 列です。同列は注文明細表の 主キーであると同時 に、注文表の主キーを参照する 外部キーでもあります。 このように1つの列が主キーと外部キー、2つの役割を担う 場合もあります。
表の結合（内部結合）
それでは、本題の表結合をSQL文で実現する方法について見ていきましょう。表の結合はFROM句で行ないます。結合にはいくつか種類がありますが、まずは内部結合について見てみましょう。
FROM句における凡例を記載します。
FROM 表1 INNER JOIN 表2 ON 結合条件
上記は結合条件を用いて、表1と表2を結合するという意味になります。結合条件となる式は一般的に次のような表記となります。
表1.列名 = 表2.列名
結合により複数の表が使用されるため、 「どの表のどの列か」を指定する必要があります。そのため、表1.列名 のように記述します。では実際に表同士を結合する実行例を見てみましょう。
実行例1
商品表(items)と分類表(categories)について、分類ID(cid)を使って関連づけを行なって表示する。
SELECT * FROM items INNER JOIN categories ON items.cid = categories.cid;
実行結果
iid name price cid cid name --- ------------ ------ --- --- -------- 1 Desktop PC 100000 1 1 PC 2 Note PC 85000 1 1 PC 3 Tablet PC 50000 1 1 PC 4 Mouse 1000 2 2 Supply 5 Keyboard 1200 2 2 Supply 6 USB Memory 800 2 2 Supply 7 LAN Cable 500 2 2 Supply 8 USB Cable 300 2 2 Supply 9 Mother Board 25000 3 3 PC Parts 10 LAN Card 3000 3 3 PC Parts
結合条件は
items.cid = categories.cid
となっています。商品表(itmes)の分類ID(cid)と分類表の分類ID(cid)が同じ行同士を結合するという意味になります。2つの表で分類IDが一致する組み合わせは下図のようになります。
この図を見てわかるように、商品表の商品IDが「11」の行は 分類IDを持たないため、分類表のどの行とも結びつかない ことがわかります。よって、この行は実行結果に表示されません。
複数の表を扱う場合の列表示
結合のように複数の表を扱う場合、列の表示について注意が必要です。次の実行例をご覧ください。
実行例2
SELECT name FROM items INNER JOIN categories ON items.cid = categories.cid;
実行結果
Error: ambiguous column name: name
実行するとエラーが発生しています。エラーメッセージを直訳すると「列名があいまいです。:name」という意味です。これはSELECT句に記述した「name」が、 どちらの表のものなのか明確でない ために発生しています。FROM句で指定した2つの表はいずれもname列を持っているため、どちらか1つを明確に指定する必要があります。表名と列名を合わせて指定する場合は次の記述を用います。
表名.列名
この書き方はONの後ろの結合条件でも用いられています。
再度、表名を指定して実行してみます。
実行例3
SELECT items.name, categories.name FROM items INNER JOIN categories ON items.cid = categories.cid;
実行結果
name name ------------ -------- Desktop PC PC Note PC PC Tablet PC PC Mouse Supply Keyboard Supply USB Memory Supply LAN Cable Supply USB Cable Supply Mother Board PC Parts LAN Card PC Parts
商品表の商品名と分類表の分類名を指定し、表示することができました。
書き方の工夫
表結合のSQL文では、文全体の記述がどうしても長くなってきます。ここでは次の3点を使って、SQL文の読みやすさ、書きやすさを高める工夫をします。
表別名の利用
表に別名を付けて、表名をシンプルに表記することができます。FROM句で表を指定する際に、次のように記述します。
表名 AS 表別名
※「AS」は省略可能です。以降の実行例では省略しています。
INNERの省略
INNER JOINの記述において、「INNER」は省略可能です。
字下げの活用
SQL文は、空白や改行を適宜追加しながら記述していくことができます。
それでは、これらを活用して実行例3を改良してみましょう。
実行例3(改良版)
SELECT
i.name,
c.name
FROM
items i
JOIN categories c
ON i.cid = c.cid
;
表の別名を1文字とし、「INNER」のキーワードを省略しました。また、字下げについては次のルールで行なっています。
- SELECT句においては、1列1行で表示します。
- SELECT、FROMなどの句の内容を右に半角空白2文字分ずらしています。
- ONはJOIN句の一部として、右に半角空白2文字分ずらしています。
外部結合
外部結合は、ここまで見てきた内部結合の該当行に加え、指定した一方の表について結合条件式に関わらず、すべての行を表示する という結合を実現します。
例えば先ほどの商品表と分類表の結合において、結合条件に該当せずに表示されなかった商品ID(iid)が11の行についても、次のように表示されます。
外部結合として 左外部結合 と 右外部結合 の2種類をご紹介します。まずはそれぞれの結合の書き方を確認します。
左外部結合
FROM 表1 LEFT OUTER JOIN 表2 ON 結合条件
右外部結合
FROM 表1 RIGHT OUTER JOIN 表2 ON 結合条件
※いずれの書き方の場合も「OUTER」の記述は省略可能です。
ここでいう左・右とは、「LEFT OUTER JOIN」および「RIGHT OUTER JOIN」の記述の左右です。下図でイメージしておきましょう。
それでは実行例を見ていきましょう。
実行例4
SELECT
*
FROM
items i LEFT OUTER JOIN categories c
ON i.cid = c.cid
;
実行結果
iid name price cid cid name --- -------------- ------ --- --- -------- 1 Desktop PC 100000 1 1 PC 2 Note PC 85000 1 1 PC 3 Tablet PC 50000 1 1 PC 4 Mouse 1000 2 2 Supply 5 Keyboard 1200 2 2 Supply 6 USB Memory 800 2 2 Supply 7 LAN Cable 500 2 2 Supply 8 USB Cable 300 2 2 Supply 9 Mother Board 25000 3 3 PC Parts 10 LAN Card 3000 3 3 PC Parts 11 Inkjet Printer 9000
今回の文例は、左右がイメージしやすいようにLEFT OUTER JOINの部分は1行で記述しています。LEFT OUTER JOINの左部分にあるのは items i なので、items表のすべての列が表示されます。また、左右の関係は改行しても変わりません。先に記述された表が常に 左表 です。したがって、実行例4を次のように書くこともできます。
SELECT
*
FROM
items i
LEFT OUTER JOIN categories c
ON i.cid = c.cid
;
3つ以上の表結合
内部結合(INNER JOIN)および外部結合(LEFT JOIN、RIGHT JOIN)を使って、3つ以上の表結合を行なうこともできます。3つの表結合を行なって、次のような実行結果を得る例をご紹介します。
上図は注文表(orders)、注文明細表(order_details)、商品表(items)を結合して、注文に関する情報を表示するイメージです。なお、注文明細表の全行は多いので、注文IDが1～4までのものを表示対象とします。また、赤囲みは表結合時の主キーと外部キーの関係を表しています。
では、実際のSQL文を見ていきましょう。
実行例5
SELECT
i.name,
od.amount,
i.price,
o.odate
FROM
orders o
JOIN order_details od
ON o.oid = od.oid
JOIN items i
ON od.iid = i.iid
WHERE
o.oid <= 4
;
実行結果
name amount price odate ------------ ------ ------ ---------- Desktop PC 1 100000 2021-04-10 Mouse 1 1000 2021-04-10 Keyboard 1 1200 2021-04-10 Mouse 1 1000 2021-04-15 USB Memory 2 800 2021-05-01 Desktop PC 1 100000 2021-05-10 Mother Board 1 25000 2021-05-10 LAN Card 1 3000 2021-05-10
FROM句の結合についてイメージが難しい場合は、JOIN句を1つずつ見ていくと良いでしょう。たとえば最初の
orders o JOIN order_details od ON o.oid = od.oid
でorders表とorder_details表を結合します(下図)。
※上記は表示の都合上、注文IDが4までのもののみ表示。
次の結合は、すでに結合済みの表に対して、追加で結合するイメージです。したがって結合条件に使える列は次のようになります。
また、3つの表を結合していますので、SELECT句やWHERE句でもこれら3つの表の列を使用することができます。この例では注文表(orders)の注文ID(oid)をWHERE句に用いて、表示対象行を決定しています。
練習問題
それではここから練習問題です。
問1
注文表(orders)と利用者表(users)をユーザーID(uid)を使って内部結合し、実行結果のように表示してください。
実行結果
uid name odate --- --------- ---------- 1 Hiroshi.T 2021-04-10 1 Hiroshi.T 2021-04-15 2 Yoko.M 2021-05-01 2 Yoko.M 2021-05-10 2 Yoko.M 2021-05-15 3 Naoto.H 2021-04-10 4 Ichiro.K 2021-04-30 4 Ichiro.K 2021-05-01 6 Akira.O 2021-05-10 6 Akira.O 2021-05-20
問2
問1の結果には、１度も注文の実績がない uidが5 の行が含まれていません。外部結合を使って、注文の実績がないユーザーの行も含めて実行結果のように表示してください。
実行結果
uid name odate --- --------- ---------- 1 Hiroshi.T 2021-04-10 1 Hiroshi.T 2021-04-15 2 Yoko.M 2021-05-01 2 Yoko.M 2021-05-10 2 Yoko.M 2021-05-15 3 Naoto.H 2021-04-10 4 Ichiro.K 2021-04-30 4 Ichiro.K 2021-05-01 5 Hitomi.S 6 Akira.O 2021-05-10 6 Akira.O 2021-05-20
問3
商品表(items)と分類表(categories)を分類ID(cid)を使って結合し、分類名(name)に「PC」が含まれるものを表示します。表示列は実行結果を参考に決定してください。
実行結果
name price ------------ ------ Desktop PC 100000 Note PC 85000 Tablet PC 50000 Mother Board 25000 LAN Card 3000
練習問題(解答例)
それでは、練習問題の解答例です。
問1
SELECT
u.uid,
u.name,
o.odate
FROM
users u
JOIN orders o
ON o.uid = u.uid
;
問2
SELECT
u.uid,
u.name,
o.odate
FROM
users u
LEFT JOIN orders o
ON o.uid = u.uid
;
問3
SELECT
i.name,
i.price
FROM
items i
JOIN categories c
ON i.cid = c.cid
WHERE
c.name LIKE '%PC%'
;
おわりに
本日は以上とさせていただきます。最後までご覧くださりありがとうございます。表結合をすることで、表示のバリエーションが一気に広がったことが実感できたのではないでしょうか。RDBでは表同士を結合することで、実際にシステムに有益な情報を取得できるデータとなります。次回から引き続きSELECT文の句についてご紹介していきます。次回はGROUP BY句、HAVING句をご紹介します。引き続きよろしくお願いいたします。
合同会社イー・シー・エフでは、子ども向けプログラミングなどの教育講座を実施しています。プログラミング教室の案内や教育教材の情報、また関連するご相談・問い合わせにつきましては下記よりご確認ください。
