SQLiteでデータベース(SQL)の基本を学ぶ(5)

データベース

こんにちは、ECF Techブログ
担当のMichiharu.Tです。

本記事はSQL文について、実際に動作させながら学習する入門記事の第5回となります。今回はリレーショナルデータベースの最重要項目ともいえる表の結合について見ていきましょう。よろしくお願いいたします。

本記事の題材となっているデータベース内の各表、および学習の始め方については、下記の第1回の記事からご覧ください。

データベース
データベースのカテゴリです。トレーニングとして使えるSQLの入門記事などを連載しています。

リレーション

表の結合を学ぶにあたり、リレーショナルデータベースの名前のもとになっている 「リレーション(Relation)」 についてご説明します。リレーションは「関係」という意味を持つ英単語で、RDBでは 表と表の関係のことリレーション、または 関連 と呼んでいます。

第1回の記事でもご紹介した下の図ですが、このとき「学生表と学科表には関連がある」などと表現したりします。

主キーと外部キー

この関連を表現する上で重要となる概念が、 主キー外部キー です。下に概要を示します。

  • 主キー:表の中から1つの行を特定するのに使用する列、および列の組み合わせのこと。そのため重複した値を入れることができない。
  • 外部キー:他の表の主キーを参照するために用いる列のこと、表と表の関連を表す際に用いる。

学生表と学科表の例でみてみましょう。

学生表における 学籍番号 は、表内において学生1人分の情報(レコード)をただ1つに特定するための項目で、学生表の主キーとなります。学部表の 学部番号 も同様です。一方で学生表の 学部番号 は、学部表の主キーである学部番号と関連性を持たせるための項目です。2つの表の学部番号を関連づけて見ることによって、たとえば「徳川家康は工学部である」ということが表現できるわけです。

上段落で「レコード」という表現を用いています。本連載の第1回においても、テーブル内の1行のことをレコードとも呼ぶ点については述べていますが、「レコード」は「1件のデータ」という意味合いが強い表現です。この意味合いが強い場合は、「レコード」の記述を用いることとします。

今回、サンプルデータベースに格納している表について、主キー、外部キーの関係を下に示します。

注目すべき点がいくつかあります。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」の記述の左右です。下図でイメージしておきましょう。

SQLiteでは現在、右外部結合(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の結果には、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句をご紹介します。引き続きよろしくお願いいたします。


合同会社イー・シー・エフでは、子ども向けプログラミングなどの教育講座を実施しています。プログラミング教室の案内や教育教材の情報、また関連するご相談・問い合わせにつきましては下記よりご確認ください。

ECFエデュケーション
タイトルとURLをコピーしました