Sqlite3で交差テーブルでリストを取得する

SQL(Sqlite3)で交差テーブルでリストを取得する

N対N、多対多、のデータを表す時に現れる、SQLのアンチパターンの「ジェイウォーク」対策として「交差テーブル」（中間テーブル・関連テーブルと呼んだりするときもあります。）を取り上げたいと思います。

ちなみに「交差テーブル」は「交差結合（CROSS JOIN句）」とは何の関係もありません。むしろ多対多の関係でテーブルを交差結合すると問い合わせ結果が凄い量になるので使用しないほうが良いです。

以下リンク内のスライド22を題材として使用し対応するSQLを書いていきます。

SQLアンチパターン - ジェイウォーク

当該スライドを画像として以下に引用します。

実施環境

• Windows10
• Sqlite3 ver.3.27.2
• コマンドラインで確認

テーブル定義

まず、以下の定義を「01_cross_table.sql」としてUTF-8でテキストに保存します。

PRAGMA foreign_keys=true;

CREATE TABLE IF NOT EXISTS blogs
(
  -- スライドからid → blog_idに名前変更
  blog_id INTEGER PRIMARY KEY,
  title   TEXT
);

CREATE TABLE IF NOT EXISTS users
(
  -- スライドからid → user_idに名前変更
  user_id INTEGER,
  name    TEXT,
  PRIMARY KEY(user_id)
);

-- 交差テーブルは最後に作る
CREATE TABLE IF NOT EXISTS authors
(
  blog_id INTEGER,
  user_id INTEGER,
  FOREIGN KEY(blog_id) REFERENCES blogs(blog_id),
  FOREIGN KEY(user_id) REFERENCES blogs(user_id)
);

以下のコマンドで流し込みます。

> sqlite3
> .read c:/xxxx/01_cross_table.sql

そうすると、冒頭の画像のテーブルが作成されます。（コメントの通り一部カラム名が異なるので注意してください。

初期データの作成

初期データを記述したファイル「02_data_init.sql」を以下の通り作成します。

-- 初期データの作成
-- blogsテーブル
INSERT INTO blogs(title) VALUES('俺のブログ');
INSERT INTO blogs(title) VALUES('私のブログ');
-- usersテーブル
INSERT INTO users VALUES(10000,'太郎');
INSERT INTO users VALUES(10001,'一郎');
INSERT INTO users VALUES(10002,'次郎');
INSERT INTO users VALUES(20000,'n郎');
--- authorsテーブル
INSERT INTO authors VALUES(1,10000);
INSERT INTO authors VALUES(1,10001);
INSERT INTO authors VALUES(1,10002);
INSERT INTO authors VALUES(2,20000);
INSERT INTO authors VALUES(2,10001);
INSERT INTO authors VALUES(2,10002);

これも先ほどと同じようにsqlite3で実行します。(ログインしている状態でコマンドを実行しているためsqlite3は省略しています)

> .read c:/xxxx/02_data_init.sql

確認用のSQLを作成して実行します。

ファイル名：「03_check_tables.sql」

.headers ON
.mode column
SELECT * FROM blogs;
SELECT * FROM users;
SELECT * FROM authors;

コマンドを実行すると以下のような出力が得られると思います。

> .read c:/xxxx/03_check_tables.sql

blog_id     title
----------  ----------
1           俺のブログ
2           私のブログ

user_id     name
----------  ----------
10000       太郎
10001       一郎
10002       次郎
20000       n郎

blog_id     user_id
----------  ----------
1           10000
1           10001
1           10002
2           20000
2           10001
2           10002

3表を結合してリストを取得する

では、この表を全て結合して"俺のブログ"のauthorをリストとして取得したいと思います。

以下内容を「04_list_users.sql」に保存し実行します。

.headers ON
.mode column
-- 俺のブログに関係するusers.user_idとusers.nameを全部列挙
SELECT A.blog_id, A.title, C.user_id, C.name
FROM (blogs A INNER JOIN authors B ON A.blog_id = B.blog_id)
INNER JOIN users C ON B.user_id = C.user_id
WHERE A.blog_id = 1;

コマンドラインから実行します。

> .read c:/xxxx/04_list_users.sql

blog_id     title       user_id     name
----------  ----------  ----------  ----------
1           俺のブログ       10000       太郎
1           俺のブログ       10001       一郎
1           俺のブログ       10002       次郎

リストが取得できました。

必要に応じてSELECT句の内容は調整する必要があると思います。