uruos.net

JOIN

データサイエンス

この記事ではJOINについて説明します。

JOIN の内容について概要を説明していきます。

 JOIN ですがテーブルが二つある場合にそれらを結合して使う場合にJOINを使うことになります。

 ここでは例としては注文テーブル商品テーブルがあるような場合を考えます

注文テーブルとは注文があった時にそれを記録していくテーブルになります。

このようなテーブルは時系列毎にデータが増えていきますが 、

商品についての情報は ID などで管理されることによって

データ量を減らしたり後から商品名が変わった場合も影響がない、という利点があります 。

このような場合は別のテーブルとして商品のテーブルを準備しておきます 。

idの1はどのような商品なのか、商品名などを持ったテーブルとして商品テーブルを持っています 。

 この注文テーブルでは例えば分析に使う場合にどのような商品か分かりにくいので 、

 この場合は二つのテーブルを組み合わせて、id の部分を商品名に変えたりすることで

わかりやすいテーブルを作ることができます 。

 このような場合に JOIN を使ってテーブルを結合するということが SQL でも多く使われます。

1 データの準備

データの準備をします。

使うテーブルは、

  • Purchase_log
  • product
  • user

の3つのになります。

これらのテーブルはまだデータベースにないので、データを作成するSQLを実行して、3つのテーブルを準備します。 SQLを実行して、テーブル作成を行います。

purchase_logテーブルは下記SQLを実行することで作成できます。

CREATE TABLE if not exists purchase_log(purchase_date TEXT,product_id TEXT,user_id TEXT,quantity INTEGER);

INSERT INTO
  purchase_log ("purchase_date","product_id","user_id","quantity")
VALUES
  ('2021-5-20', '1', 'uid01', '20'),
  ('2021-5-20', '2', 'uid02', '2'),
  ('2021-5-20', '3', 'uid02', '2'),
  ('2021-5-21', '4', 'uid04', '10'),
  ('2021-5-22', '5', 'uid03', '10'),
  ('2021-5-22', '6', 'uid99', '2');

purchase_date
product_id
user_id
quantity
2021-5-20
1
uid01
20
2021-5-20
2
uid02
2
2021-5-20
3
uid03
2
2021-5-21
4
uid04
10
2021-5-22
5
uid05
10
2021-5-22
6
uid99
2

productテーブル

CREATE TABLE product (id TEXT , name TEXT)"
cursor.execute(sql);
INSERT INTO "product" ("id", "name") VALUES 
('1', 'マスク'),
('2', 'フェイスシールド'),
('3', 'アルコール液'),
('4', '除菌シート'),
('5', '手袋'),
('6', 'アクリルパーティション');
id
name
1 マスク
2 フェイスシールド
3 アルコール液
4 除菌シート
5 手袋
6 アクリルパーティション

userテーブル

CREATE TABLE  if not exists user (id TEXT, sex TEXT, age INTEGER);
INSERT INTO
  user ("id", "sex", "age")
VALUES
  ('uid01', '男性', '19'),
  ('uid02', '女性', '35'),
  ('uid03', '男性', '57'),
  ('uid04', '女性', '22'),
  ('uid05', '男性', '31'),
  ('uid06', '女性', '32')
;
id sex age
uid01
男性 19
uid02
女性 35
uid03
男性 57
uid04
女性 22
uid05
男性 31
uid06
女性 32
 

以上で、利用するデータが準備できました。

2 JOIN

JOINの使い方を解説します。 JOINを使うことでテーブルを組み合わせた SQL の実行をすることができます。

書式/コード

書式は下記になります。

SELECT
    列名
FROM
    テーブル名1
JOIN
    テーブル名2
ON
    条件

JOINではテーブルが2つ登場しますので、JOIN の後に2つ目のテーブルを指定します。 ONのあとは、WHEREと同様に条件式を指定します。 ここでの条件に基づいて2つのテーブルを結合することになります。

SQLを実行してみます。

SELECT
  *
FROM
  purchase_log
  JOIN product
  ON purchase_log.product_id=product.id
  • FROM 句のではpurchase_logテーブル指定されて、JOINの後にproductテーブルが指定されています
  • これらふたつのテーブルが結合されることになります
  • ONにつづく条件ではpurchase_logテーブルのproduct_idとproduct テーブルのidが等しいときという条件が指定されています
  • この条件でテーブルが結合されることになります

結果は下記になります。

purchase_date
product_id
user_id
quantity
id
name
2021-5-20
1
uid01
20 1 マスク
2021-5-20
2
uid02
2 2 フェイスシールド
2021-5-20
3
uid03
2 3 アルコール液
2021-5-21
4
uid04
10 4 除菌シート
2021-5-22
5
uid05
10 5 手袋
2021-5-22
6
uid99
2 6 アクリルパーティション

ポイント

ON句の中で条件式を指定することです。 JOINではこの条件に合うように、2つのテーブルが結合され、1つにまとまったテーブルになります。

3 LEFT JOIN

LEFT JOINの使い方を解説します。

LEFT JOINを使うことで、条件によらず1つ目のテーブルのデータはすべて取得するという使い方ができます。

書式/コード

LEFT JOIN の書式は下記になります。

SELECT
    列名
FROM
    テーブル名1
JOIN
    テーブル名2
ON
    条件

書式はJOINと全く同じです。

  • JOINは条件に合うデータのみを返すのに対し LEFT JOIN は条件にあったものと左側のテーブルの内容すべてを返します
  • ここで左側というのは先に指定したテーブルということになります
  • ですので書式で言うとテーブル名1のところで指定したテーブルデータは全て返すということになります

SQLを実行してみます。

SELECT
  *
FROM
  purchase_log
  LEFT JOIN user
  ON purchase_log.user_id=user.id

結果は下記になります。

purchase_date
product_id
user_id
quantity
id
sex age
2021-5-20
1
uid01
20
uid01
男性 19
2021-5-20
2
uid02
2
uid02
女性 35
2021-5-20
3
uid02
2
uid02
女性 35
2021-5-21
4
uid04
10
uid04
女性 22
2021-5-22
5
uid03
10
uid03
男性 57
2021-5-22
6
uid99
2 NULL NULL NULL

ポイント

存在しないデータはNULLを埋めた形でデータを返します。NULLはデータが存在しないという意味になります。 LEFT JOIN の場合はこのように、データが存在しない場合がありますのでこのような場合にNULLをうめてテーブルを作成します。

タイトルとURLをコピーしました