【SQL入門】SQLを用いてテーブルをつなげる

なぜデータを繋げる必要があるのか

データベースからデータを取得する際、複数のテーブルからそれぞれデータを取得したいといったケースは頻繁に起こり得ます。

その際に「テーブルAを調べる→テーブルBを調べる…」のようにデータを取得していると時間がかかってしまいます。そこで複数のテーブルを繋げることでまとめてデータを見ることができ、それらを実現する手法として、これから説明する「内部結合」や「外部結合」があります。

例:注文データと商品データを結合

※そもそもなぜ多くのデータは分かれているのか

データを複数テーブルに分けるのは何故？
- 上の結合テーブルのように、同じ商品名の注文が複数回入っていると、もし「おにぎり」の単価が120円になった時、おにぎりを含むすべての行で値段を更新しなければなりません。1つのデータが複数に分散していると、変更等があった際に整合性を保つのが難しくなるため、別々に管理する必要があるのです。
正規化と非正規化
- 正規化とは、データに矛盾や重複が生じないように表を最適化することを指します。非正規化の表の例として、以下のようなものが挙げられます。繰り返しの項目があり、率直な2次元の表になっていません。DBではこのような表を管理できないので、正規化する必要があります。

※正規化についてさらに知りたい方は以下の説明を一読してください。少し難しいので飛ばしても大丈夫です。

第1正規形
- 非正規系の表から、繰り返しの部分を取り除いたものが第1正規形となります。

第2正規形
- 複合キーの一部の要素だけで列の値が一意に定まる関係を「部分関数従属」といいます。上のテーブルの場合、「注文ID」が決まれば「注文日、購入者ID、購入者」が決まります。また「商品ID」が決まれば「商品名、価格」が決まります。「個数」は「注文ID、商品ID」で決まります。部分関数従属している列を切り出したものが第2正規形となります。

第3正規形
- 主キーが決まれば列の値が一意に定まる関係を「関数従属」といいます。第2正規形の表から主キー以外の列に関数従属している列を切り出したものが第3正規形となります。上のテーブルの場合、購入者名は注文IDではなく、購入者IDをキーとして決定するので、これらを分離させると、以下のようになります。これが第3正規形となります。

正規化のメリット
- 上記のように正規化を経ることによって、効率的に管理できる表の構造となります。

データのつなげ方

データの繋げ方として、内部結合と外部結合があります。ここでは下の2つのサンプルテーブルを用いて説明します。

内部結合

2つのテーブル間で、両方に存在するデータを抽出する結合です。サンプルテーブルの両方にある「商品ID」を抽出して内部結合すると以下のテーブルとなります。「商品ID」が4,5,6,8のデータは片方にしか存在していないので結合結果からは消滅します。

外部結合

外部結合には左外部結合,右外部結合,完全外部結合,交差結合があります。

左外部結合

左外部結合では、左側のテーブルが基準となります。左テーブルのデータを全て取り出し、それに右側のテーブルから抽出できるデータのみを取得します。「商品ID」が4,5のものは左テーブルにしか存在しないため、それらの価格はNULLとなります。

右外部結合

右外部結合では、右側のテーブルが基準となります。右テーブルのデータを全て取り出し、それに左側のテーブルから抽出できるデータのみを取得します。「商品ID」が6,8のものは右テーブルにしか存在しないため、それらの商品名はNULLとなります。

完全外部結合

完全外部結合では、2つのテーブルのすべての行を取り出して結合します。

交差結合

以下のサンプルテーブルがあった時、交差結合では2つのテーブルの全組み合わせを取り出します。

つなげる際の注意点

JOINが増えるとパフォーマンスが低下します。テーブルやデータの数が増えると、その分処理に時間がかかるようになるので、高速化のためには

インデックスを利用する
- インデックスは「索引」を意味します。本の索引も、特定のキーワードを探す際に役立ちますが、データベースでも同じです。インデックスを利用することで効率良くデータを探索することができるので、パフォーマンスが向上します。
JOIN前に条件を絞り込む
- テーブルを結合してからWHERE句を記載すると、結合後の大きなテーブルに対して、条件絞り込みを行うため、処理に時間がかかってしまいます。JOIN前に条件を絞り込みことで、JOIN元やJOIN先のテーブルの行数が少なくなり、パフォーマンスが向上します。

実際につなげてみる

準備

【パイプライン入門】spreaad sheet to BQを参考にデータを準備
- ここではsalesテーブルのみ作成しましたが、今回はproductsテーブルも作成してください。手順はsalesテーブルを作成する時とほとんど同じですが、「シート範囲」に下のように「products」と入力してください。

サンプルSQL

実際にsalesテーブルとproductsテーブルを繋げてみます。

内部結合

内部結合の基本構文は以下の通りです。

SELECT 
    カラム名
FROM 
    テーブルA
JOIN 
    テーブルB
    ON 結合条件;

カラム名は「テーブル名.カラム名」で指定し、結合条件は、「ON テーブルA.カラム名 = テーブルB.カラム名」で指定します。

サンプルテーブルの両方に存在する「product_id」を抽出します。

SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.product_id,
    sales.count,
    sales.tax,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
JOIN 
    manabiba.products 
    ON sales.product_id = products.product_id;

SELECT* を使用してもよいのですが、その場合「product_id,product_id_1」のように余計なカラムができてしまうので、全カラムをSELECTしてください。結合を行なったことで、どの日に何が購入されたかが一目瞭然になりました。

条件を絞り込む

WHERE カラム名 (演算子) 〇〇　のように書くことで「〇〇カラムが〇〇であるレコード」といった条件を指定できます。

SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.product_id,
    sales.count,
    sales.tax,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
JOIN 
    manabiba.products 
    ON (sales.product_id = products.product_id and products.profit >= 300);

SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.product_id,
    sales.count,
    sales.tax,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
JOIN 
    manabiba.products 
    ON (sales.product_id = products.product_id)
WHERE 
    products.profit >= 300;

上のコードは処理時間3秒、シャッフルされたバイト数は1.73KB。これに対し、下のコードは処理時間5秒、シャッフルされたバイト数は7.13KB。JOIN前に条件を絞り込むことがパフォーマンスに関わってくることがわかります。

左外部結合

左外部結合の基本構文は以下の通りです。

SELECT
    カラム名
FROM 
    テーブル名
LEFT JOIN 
    テーブル名
    ON 結合条件;

SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.product_id,
    sales.count,
    sales.tax,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
LEFT JOIN 
    manabiba.products 
    ON (sales.product_id = products.product_id);

サンプルデータを見てもらうとわかりますが、salesテーブルの「product_id」の値は2~10、それに対しproductsテーブルの「product_id」の値は1~10なので、左外部結合をした時はproductsテーブルの「product_id=1」のレコードは抽出されません。

右外部結合

右外部結合の基本構文は以下の通りです。

SELECT 
    カラム名
FROM 
    テーブル名
RIGHT JOIN 
    テーブル名
    ON 結合条件;

SELECT 
    sales.date,
    sales.order_id,
    sales.user_id,
    sales.count,
    sales.tax,
    products.product_id,
    products.name,
    products.profit,
    products.price,
    products.cost,
    products.calories
FROM 
    manabiba.sales 
RIGHT JOIN 
    manabiba.products 
    ON (sales.product_id = products.product_id);

salesテーブルの「product_id」の値は2~10、それに対しproductsテーブルの「product_id」の値は1~10なので、右外部結合をした時はproductsテーブルの「product_id=1」のsalesテーブルに関するカラムは全てnullとなります。

完全外部結合と交差結合は基本構文のみ載せておきます。

完全外部結合

SELECT 
    カラム名
FROM 
    テーブル名
FULL JOIN 
    テーブル名
    ON 結合条件;

交差結合

SELECT 
    カラム名
FROM 
    テーブル名
CROSS JOIN 
    テーブル名
    ON 結合条件;

サンプルSQLに適したJOIN

今回のように、salesテーブルとproductsテーブルを結合する時は、1注文の商品データを詳しく見たいので、左外部結合が適しています。理由としては、salesテーブルの注文データを全て取り出し、それにproductsテーブルから抽出できるデータのみを取得することで、すべての注文データの商品データを詳しく見ることができるからです。ただし、左外部結合なので商品データに関するカラムが全てnullのレコードがある可能性があります。