JOIN | uruos.net

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1 データの準備
2 JOIN
3 LEFT JOIN

1 データの準備

データの準備をします。

使うテーブルは、

Purchase_log
product
user

の３つのになります。

これらのテーブルはまだデータベースにないので、データを作成するSQLを実行して、３つのテーブルを準備します。 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ではテーブルが２つ登場しますので、JOIN の後に２つ目のテーブルを指定します。 ONのあとは、WHEREと同様に条件式を指定します。ここでの条件に基づいて２つのテーブルを結合することになります。

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ではこの条件に合うように、２つのテーブルが結合され、１つにまとまったテーブルになります。

3 LEFT JOIN

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

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

書式/コード

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