【DB】テーブル結合したデータ参照【SQL】

テーブルを正規化すると単純に1テーブルだけの参照ではデータ取得ができません。そのため、複数のテーブルを結合して参照する必要があります。

テーブル結合

テーブルを結合する場合「SELECT句」に「JOIN」という条件を追加して複数のテーブルを結合します。

今回結合するテーブルは以下の通りです。

※テーブル作成までの経緯については下記を参照ください。

テーブルの正規化

テーブル構造

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 WARSHIP_TYPE_ID NUMERIC(2)
4 LAUNCH_DATE DATE

テーブル名:WARSHIP_TYPE

No. カラム名 データ型 主キー
1 ID NUMERIC(2)
2 NAME VARCHAR(50)

テーブルデータ

テーブル名:WARSHIP_TYPE

ID NAME
1 戦艦
2 空母
3 駆逐艦
4 海防艦

テーブル名:WARSHIP

ID NAME WARSHIP_TYPE_ID LUNCH_DATE
1 大和 1 1940年8月8日
2 赤城 2 1925年4月22日
3 五月雨 3 1935年7月6日
4 朝霜 3 1943年7月18日

エイリアス(別名)

テーブル結合を実施する場合、SQL文を少しでも簡素化する方法として「エイリアス」を定義します。

WARSHIP:「A」
WARSHIP_TYPE:「B」

単純にアルファベットを並べてエイリアスを定義しました。

※エイリアスにテーブル名の一部を省略した名称などを使うこともあります。個人的には略称を扱わないようにしております。略称はかぶってしまう場合が多々あるためルール化が複雑になるためです。

結合するSQL文

「SELECT」~「FROM」~「JOIN」~「ON」(~)

  • SELECT:取得したい項目を指定します。
  • FROM:取得したい項目を管理するテーブルを指定します。
  • JOIN:結合して取得した項目を管理するテーブルを指定します。
  • ON:結合する条件を指定します。
  • WHERE:取得するデータを絞り込む条件を指定します。

実際に結合してみます。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

結合したすべてのデータが出力されます。

※SQL文に「AS~」と指定しています。これはカラム名に対してのエイリアスになります。

ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

このようにカラム名とエイリアス指定した名称でデータが取得されます。この結合方法は内部結合といいます。

内部結合・外部結合について

上記で結合してデータを参照する方法を内部結合といいます。内部結合に対して外部結合という結合方法がありあます。

内部結合とは

結合するお互いのテーブルに存在する情報を出力する場合を「内部結合」するといいます。

上記例では「WARSHIP_TYPE」テーブルに「4:海防艦」が定義されているのに出力結果には存在しておりません。これは内部結合を行った結果になります。

内部結合はSQL文で明示的に記載することもできます。上記SQL文は内部結合の省略形式になります。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
INNER JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

「JOIN」を「INNER JOIN」に変更しました。この「INNER JOIN」句が内部結合の明示的な指定方法です。

外部結合とは

外部結合は、片側のテーブルにある情報すべてと結合できた情報を表示します。内部結合では抽出できなかった「WARSHIP_TYPE」テーブルの「4:海防艦」も出力したい場合は以下のようなSQL文なります。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
RIGHT JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

「INNER JOIN」を「RIGHT JOIN」に変更しました。「RIGHT JOIN」を指定したことで「WARSHIP_TYPE」テーブルのすべてを取得するという指定になります。

ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日
NULL NULL 海防艦 NULL

取得できないデータはすべて「NULL」として取得されます。

また、「RIGHT」という表現がでてくると「LEFT」はできないのかなと考えます。「LEFT JOIN」も可能です。上記SQL文で「LEFT JOIN」を指定すると「WARSHIP」テーブルのすべてを取得という指定になります。指定する意味は違いますが内部結合の時と同じ内容が取得されます。

 

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
LEFT JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)
ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

【DB】テーブル定義について

DBに定義するテーブルについて記載します。

テーブルとは

テーブルとはデータベース上でデータを種類ごとに分類し整理する箱のようなものだと考えます。テーブルを定義する場合に最低限必要な要素は以下の通りです。

  • テーブル名:データベース上でテーブルを識別する名称です。
  • 項目(カラム):テーブル内にデータを種類ごとに分ける枠の名称です。
  • データ型:カラムに登録するデータのプログラムから見たデータ種類です。
  • 主キー:各データを一意に識別可能な値になります。(※)

※主キーは「索引:インデックス」の仲間になります。インデックスの持つ意味についてはここでは割愛いたします。

データ型について

カラムに設定するデータ型によってデータの種類が決まってきます。主なデータ型については以下の通りです。

No. 種類 データ型 主な用途
1 数値 NUMERIC 金額、年齢など
2 文字、
文字列
CHAR、
VARCHAR
など
 名前、住所、メールアドレスなど
3 日付、
時刻
DATE、DATETIME、TIMESTAMP
など
データ登録日時、誕生日など
4 論理値 BOOLEAN、
BIT、
など
フラグ管理など。
5 バイナリ  BLOB
など
 PDFファイル画像データなどを登録する

また、データ型にはサイズ(文字数、数値の桁数)を明示的に定義する必要があるものもあります。上記表にあるデータ型だと「NUMERIC」、「CHAR」、「VARCHAR」などが該当します。

※細かい型の説明については利用するDBの仕様に依存するためここでは割愛します。

主キーについて

主キーとは、データを一意に識別可能な情報です。主キーを定義する場合一つの項目から定義する場合と複数の項目から定義する場合とあります。

例えば以下のような社員情報のテーブル定義を作成します。

No. 社員番号 名前
1 SN0001 葉月しずく
2 SN0002 遠山りん
3 SN0003 八神コウ
4 SN0004 滝本ひふみ

テーブル定義をする場合は以下のように考えることができます。

No. 項目 データ型(サイズ) 主キー
1 社員番号 CHAR(6)
2 名前 VARCHAR(20)

「社員番号」は各社員ごとでユニークになると判断できるため主キーとして定義しました。データの特色から「社員番号」を固定長、「名前」を可変長として定義しました。

固定長と可変長

テーブルの作り方

テーブルの構成に最低限必要な要素を一つずつ定義します。

No. 艦名 艦種 進水日
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

上記の軍艦の情報をテーブル定義していきます。

※テーブルの正規化については別途記載するため今回は割愛します。

 テーブル名の定義

まずはテーブル名を定義します。テーブル名は情報が分かるものをアルファベットで表現します。

テーブル名:「WARSHIP

※データベースにより日本語をそのまま使えることもありますが今回はアルファベットにしました。

日本語を使わない理由

カラム構成の定義

カラム構成を下記のように考えていきます。

◆主キー

まず、主キーとなる項目を考えます。「No.」という項目がありますが実際には表を見やすくするための項目になります。この情報にはユニークになる項目がないため連番を識別番号の項目として追加します。

主キー項目:「ID

◆カラム名

カラム名もアルファベット化していきます。

艦名:「NAME
艦種:「TYPE
進水日:「LAUNCH_DATE

◆データ型

カラムごとにデータ型を定義します。データ型は情報の特性を考えて定義します。

ID:「NUMERIC(5)
※増やしていった場合1万件では不安と考えました。
艦名:「VARCHAR(100)
※海外艦でも100文字は超えないかなと考えました。
艦種:「VARCHAR(50)
※海外艦種名でも50文字は超えないかなと考えました。
進水日:「DATE
※年月日で表現できると考えました。

テーブル作成

テーブル構成は以下の通りで定義しました。

※正規化についてはここでは考慮しておりません。

正規化とは

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 TYPE VARCHAR(50)
4 LAUNCH_DATE DATE

テーブルを作成は以下のSQLで行います。

CREATE TABLE WARSHIP (
  ID NUMERIC(5) PRIMARY KEY,
  NAME VARCHAR(100),
  TYPE VARCHAR(50),
  LAUNCH_DATE DATE)

これでテーブルは作成されます。

【DB】テーブル正規化の考え方【考え方】

情報からテーブル作成をする場合、「正規化」という考え方があります。ここでは「第一正規化」について扱います。

※第一正規化より先はある程度複雑な業務などを扱うプロジェクトでないといまいちイメージが出来なかったため割愛させてください。

第一正規化とは

テーブル定義を行ったときに同じ情報「名称など」を繰り返し扱うことがあります。このような情報をグループごとにテーブル化することを言います。

正規化を行うと、データの冗長性、不整合が抑止できると考えております。

例えば同じ名称を繰り返しテーブルに追加していくと登録する文字列に間違いが生じることが予測されます。文字列に間違いが生じると本来同じ意味であった情報が違うものとして扱われてしまいます。このような状態を事前に抑止できるように正規化は行っておいた方がよいと思います。

それでは、以下のテーブルを正規化してみます。

※テーブル作成については別途記載しております。

テーブル作成方法

No. 艦名 艦種 進水日
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

正規化しないでテーブル定義をした場合は以下の通りです。

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 TYPE VARCHAR(50)
4 LAUNCH_DATE DATE

グループを抜き出してテーブル化

上記情報では、「艦種」の項目に重複した情報が存在することが分かります。この艦種だけでテーブルを作成することを考えます。

テーブル名:WARSHIP_TYPE

No. カラム名 データ型 主キー
1 ID NUMERIC(2)
2 NAME VARCHAR(50)

元のテーブル構成を変更

元のテーブルから「艦種」項目に新しく追加したテーブル「WARSHIP_TYPE」を参照する「ID」の項目「WARSHIP_TYPE_ID」に変更します。

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 WARSHIP_TYPE_ID NUMERIC(2)
4 LAUNCH_DATE DATE

この形が「第一正規化」を実施した形になります。

正規化後のテーブルにデータを登録

実際に正規化したテーブルにデータを配置します。

テーブル名:WARSHIP_TYPE

ID NAME
1 戦艦
2 空母
3 駆逐艦
4 海防艦

※「4:海防艦」は、参照するときの説明にあると便利かなと思い追記しました。

テーブル名:WARSHIP

ID NAME WARSHIP_TYPE_ID LUNCH_DATE
1 大和 1 1940年8月8日
2 赤城 2 1925年4月22日
3 五月雨 3 1935年7月6日
4 朝霜 3 1943年7月18日

このような形でデータ登録を行います。この分割したテーブルをもとの情報と同じように参照する方法については下記を参照してください。

テーブルを結合して参照する方法

【DB】データ操作するSQL文【SQL文】

データベースのデータを操作するSQL文について説明します。

説明に使用するテーブルは以下の通りです。

テーブル名:USER

No. カラム名 データ型 主キー
1 ID NUMERIC
2 NAME VARCHAR
3 TEAM VARCHAR

登録されているデータは以下の通りです。

※データについては、趣味全開です。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

データを取得するSQL文

データベースからデータを取得するSQL文は以下のような構成になります。

※説明を簡素化したいと思いますので並び順、集約などのお話はここでは省いております。

「SELECT」~「FROM」~「WHERE」~

  • SELECT:取得したい項目を指定します。
  • FROM:取得したい項目を管理するテーブルを指定します。
  • WHERE:取得するデータを絞り込む条件を指定します。

一意のデータ参照する場合

SELECT
  ID,
  NAME,
  TEAM
FROM
  USER
WHERE
  ID = 2

このSQL文を発行することで「ID」が「2」に該当する情報のみ取得します。取得したイメージは以下の通りです。

ID NAME TEAM
2 西住みほ あんこう

全部取得する場合

SELECT
  ID,
  NAME,
  TEAM
FROM
  USER

上記の通り、条件を指定しないでSQL文を発行します。この場合はすべてのデータが取得されるため取得したイメージは以下の通りになります。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

データを登録(追加)するSQL文

データ登録を行うSQL文は以下のような構成になります。

※こちらも単純にデータを追加する説明といたします。

「INSERT INTO」~(~)「VALUES」(~)

  • INSERT INTO:データを登録するテーブルを指定します。また、()内にはデータを設定する項目(カラム名)を指定します。
  • VALUES:()内に上記で指定した項目に設定する値を指定します。

※項目や値を複数指定する場合は「カンマ」で区切ります。

一つのデータを登録する場合

INSERT INTO USER (
  ID,
  NAME,
  TEAM)
VALUES (
  4,
  '五十鈴華',
  'あんこう')

上記SQL文を発行することで、データが以下のように追加されます。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう
4 五十鈴華 あんこう

データを更新(変更)するSQL文

データベースに登録されているデータを変更するSQL文は以下のような構成になります。

「UPDATE」~「SET」~「WHERE」~

  • UPDATE:データを更新するテーブルを指定します。
  • SET:データを更新する項目(カラム名)と変更する値を指定します。(※)
  • WHERE:データ更新する対象を絞り込む条件を指定します。

※更新する対象項目(カラム名)と値は「イコール」を挟みます。また、更新する対象が複数ある場合は「カンマ」で区切ります。

一つのデータを更新する場合

UPDATE USER
SET
  NAME = '河嶋桃',
  TEAM = 'カメさん'
WHERE
  ID = 4

上記SQL文を発行することで以下のようにデータが更新されます。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう
4 河嶋桃 カメさん

データを削除(物理削除)するSQL文

データベースに登録されているデータを削除するSQL文は以下のような構文になります。

「DELETE FROM」~「WHERE」

  • DELETE FROM:データを削除するテーブルを指定します。
  • WHERE:削除対象を絞り込む条件を指定します。

※物理削除に対して論理削除という考え方がありますが今回は物理削除についてのみ説明します。

論理削除とは

一つのデータを削除する場合

DELETE FROM USER
WHERE
  ID = 4

上記SQL文を発行することで以下の通り削除された状態となります。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

追加したデータを更新して削除したため元通りになりました。