一覧DB-04-2 · JOINと部分問合せ

テーブル結合と自己結合

グループに所属する人と講座で組合せ数を確認し、注文の明細と入金を同時に結んだときの重複集計を直します。その後、一つの社員表を二つの別名で使い、社員と上司の対応を取得します。

説明

結合結果は、条件に合う行の組ごとに作られます。結合する列が一意でなければ、左右にある同じ値の件数を掛けた数だけ行が増えます。元の金額を結合後に合計すると、同じ値を重ねて数えてしまうことがあります。

1同じグループに複数の行を置く

人と講座を、それぞれA・Bグループへ所属させます。

CREATE TABLE people人の表

(id INTEGER PRIMARY KEY,group_code TEXT) STRICT;

CREATE TABLE courses講座の表

(id TEXT PRIMARY KEY,group_code TEXT) STRICT;
INSERT INTO people VALUES

(1,'A'),(2,'A')同じグループの二人

,(3,'B');
INSERT INTO courses VALUES('x','A'),('y','A'),('z','B');
SELECT * FROM people ORDER BY id;
SELECT * FROM courses ORDER BY id;

Aには人が二人、講座が二つあります。group_codeは両表で重複可能です。各表の主キーが一意でも、結合するgroup_codeの値が一意になるわけではありません。

2同じグループの組を取得する

group_codeが等しい組を表示します。

SELECT p.id AS person,c.id AS course,p.group_code FROM people AS p JOIN courses AS c ON c.group_code=p.group_code同じグループ同士の全組 ORDER BY p.id,c.id;

Aは(1,x)・(1,y)・(2,x)・(2,y)の4組、Bは(3,z)の1組、合計5行です。左一行に右一行とは限らず、一致する右の全行が対応します。

3条件なしの全組合せ

CROSS JOINで全員と全講座を組み合わせます。

SELECT p.id AS person,c.id AS course FROM people AS p CROSS JOIN courses AS c左右すべての組合せ ORDER BY p.id,c.id;

3人×3講座で9行です。CROSS JOINは直積を作ります。先ほどの条件付き結合は、この九つの候補からグループが一致する五つの組を選んだものとして理解できます。

4注文に明細二件と入金二件を用意する

注文101に商品明細と分割入金を追加します。

CREATE TABLE orders(id INTEGER PRIMARY KEY,total INTEGER) STRICT;

CREATE TABLE lines商品明細の表

(id INTEGER PRIMARY KEY,order_id INTEGER,amount INTEGER) STRICT;

CREATE TABLE payments入金の表

(id INTEGER PRIMARY KEY,order_id INTEGER,amount INTEGER) STRICT;
INSERT INTO orders VALUES(101,300);
INSERT INTO lines VALUES(1,101,100),(2,101,200);
INSERT INTO payments

VALUES(11,101,120),(12,101,180)120円と180円の二回入金

;
SELECT * FROM lines ORDER BY id;
SELECT * FROM payments ORDER BY id;

商品明細の合計は300円、入金の合計も300円です。各明細と各入金は注文へ対応しますが、特定の明細一件と入金一件が対応する列はありません。

5明細と入金を同時に結ぶ

両方を注文IDで結合します。

SELECT o.id,l.id AS line_id,p.id AS payment_id,l.amount AS line_amount,p.amount AS paid FROM orders AS o JOIN lines AS l ON l.order_id=o.id注文から明細へ JOIN payments AS p ON p.order_id=o.id同じ注文から入金へ ORDER BY l.id,p.id;

明細2件×入金2件で4行です。100円の明細は入金11と12にそれぞれ現れ、120円の入金も明細1と2にそれぞれ現れます。行が増えたのは取引が増えたためではなく、結合が組合せを作ったためです。

6結合後の合計が二重になる

その四行を合計します。

SELECT COUNT(*) AS pairs,SUM(l.amount)繰り返された明細額を加算 AS line_sum,SUM(p.amount)繰り返された入金額を加算 AS payment_sum FROM orders AS o JOIN lines AS l ON l.order_id=o.id JOIN payments AS p ON p.order_id=o.id;

組合せは4、明細合計も入金合計も600円です。実際の300円の倍になりました。金額にDISTINCTを付ける修正は、同額の別取引までまとめるため一般的な解決にはなりません。先に注文単位へ集約します。

7各表を注文単位に集約してから結ぶ

FROMの内側のSELECTで注文ごとに一行へまとめます。

SELECT o.id,l.line_total,p.paid_total FROM orders AS o JOIN (SELECT order_id,SUM(amount) AS line_total FROM lines GROUP BY order_id)明細を注文単位の一行へ AS l ON l.order_id=o.id JOIN (SELECT order_id,SUM(amount) AS paid_total FROM payments GROUP BY order_id)入金も注文単位の一行へ AS p ON p.order_id=o.id;

注文101が一行となり、明細300円・入金300円です。部分問合せは括弧内のSELECTの結果を表として使っています。それぞれを注文IDにつき一行にしたので、結合しても金額が繰り返されません。

8上司IDを同じ社員表へ向ける

上司を持つ社員と持たない社員を用意します。

CREATE TABLE employees(id INTEGER PRIMARY KEY,name TEXT,manager_id INTEGER REFERENCES employees(id)同じ表の社員IDを参照

) STRICT;
INSERT INTO employees VALUES(1,'部長',NULL),(2,'課長',1),(3,'担当',2),(4,'顧問',NULL);

SELECT * FROM employees参照元と参照先の一覧 ORDER BY id;

課長の上司はID 1の部長、担当の上司はID 2の課長です。部長と顧問は上司IDがNULLです。同じ表の別の行を参照する構造を、自己参照と呼びます。

9社員と上司を別名で結ぶ

同じ表にeとmという二つの別名を付けます。

SELECT e.id,e.name,m.name AS manager FROM employees AS e社員として使う側 LEFT JOIN employees AS m上司として使う側 ON m.id=e.manager_id上司IDの対応 ORDER BY e.id;

課長には部長、担当には課長が付きます。部長と顧問もLEFT JOINで残り、上司名はNULLです。eは社員側、mは上司側という役割で、保存表が二つあるわけではありません。

10二段上の上司まで取得する

上司の上司をもう一度同じ表へ結びます。

SELECT e.name,m.name AS manager,g.name AS grand_manager FROM employees AS e LEFT JOIN employees AS m ON m.id=e.manager_id

LEFT JOIN employees AS g ON g.id=m.manager_id上司のmanager_idから二段上へ ORDER BY e.id;

担当の直属上司は課長、二段上は部長です。他の行には二段上がなくNULLになります。このSQLは二段に固定した取得です。深さが決まらない階層をすべてたどるには、別途反復や再帰の処理が必要です。

まとめ

重複する結合キーでは、一致する左右の件数の積だけ組合せが生まれます。

複数の一対多を結んで集計すると重複しやすいため、必要な単位へ各表をまとめてから結びます。

自己結合では表の別名を役割ごとに付け、親子のIDを対応させます。

操作履歴と解説を振り返れます。

SQLite 3.54.0

CREATE TABLE people(id INTEGER PRIMARY KEY,group_code TEXT) STRICT;
CREATE TABLE courses(id TEXT PRIMARY KEY,group_code TEXT) STRICT;
INSERT INTO people VALUES(1,'A'),(2,'A'),(3,'B');
INSERT INTO courses VALUES('x','A'),('y','A'),('z','B');
SELECT * FROM people ORDER BY id;
SELECT * FROM courses ORDER BY id;

╭────┬────────────╮
│ id │ group_code │
╞════╪════════════╡
│  1 │ A          │
│  2 │ A          │
│  3 │ B          │
╰────┴────────────╯
╭────┬────────────╮
│ id │ group_code │
╞════╪════════════╡
│ x  │ A          │
│ y  │ A          │
│ z  │ B          │
╰────┴────────────╯

SELECT p.id AS person,c.id AS course,p.group_code FROM people AS p JOIN courses AS c ON c.group_code=p.group_code ORDER BY p.id,c.id;

╭────────┬────────┬────────────╮
│ person │ course │ group_code │
╞════════╪════════╪════════════╡
│      1 │ x      │ A          │
│      1 │ y      │ A          │
│      2 │ x      │ A          │
│      2 │ y      │ A          │
│      3 │ z      │ B          │
╰────────┴────────┴────────────╯

SELECT p.id AS person,c.id AS course FROM people AS p CROSS JOIN courses AS c ORDER BY p.id,c.id;

╭────────┬────────╮
│ person │ course │
╞════════╪════════╡
│      1 │ x      │
│      1 │ y      │
│      1 │ z      │
│      2 │ x      │
│      2 │ y      │
│      2 │ z      │
│      3 │ x      │
│      3 │ y      │
│      3 │ z      │
╰────────┴────────╯

CREATE TABLE orders(id INTEGER PRIMARY KEY,total INTEGER) STRICT;
CREATE TABLE lines(id INTEGER PRIMARY KEY,order_id INTEGER,amount INTEGER) STRICT;
CREATE TABLE payments(id INTEGER PRIMARY KEY,order_id INTEGER,amount INTEGER) STRICT;
INSERT INTO orders VALUES(101,300);
INSERT INTO lines VALUES(1,101,100),(2,101,200);
INSERT INTO payments VALUES(11,101,120),(12,101,180);
SELECT * FROM lines ORDER BY id;
SELECT * FROM payments ORDER BY id;

╭────┬──────────┬────────╮
│ id │ order_id │ amount │
╞════╪══════════╪════════╡
│  1 │      101 │    100 │
│  2 │      101 │    200 │
╰────┴──────────┴────────╯
╭────┬──────────┬────────╮
│ id │ order_id │ amount │
╞════╪══════════╪════════╡
│ 11 │      101 │    120 │
│ 12 │      101 │    180 │
╰────┴──────────┴────────╯

SELECT o.id,l.id AS line_id,p.id AS payment_id,l.amount AS line_amount,p.amount AS paid FROM orders AS o JOIN lines AS l ON l.order_id=o.id JOIN payments AS p ON p.order_id=o.id ORDER BY l.id,p.id;

╭─────┬─────────┬────────────┬─────────────┬──────╮
│ id  │ line_id │ payment_id │ line_amount │ paid │
╞═════╪═════════╪════════════╪═════════════╪══════╡
│ 101 │       1 │         11 │         100 │  120 │
│ 101 │       1 │         12 │         100 │  180 │
│ 101 │       2 │         11 │         200 │  120 │
│ 101 │       2 │         12 │         200 │  180 │
╰─────┴─────────┴────────────┴─────────────┴──────╯

SELECT COUNT(*) AS pairs,SUM(l.amount) AS line_sum,SUM(p.amount) AS payment_sum FROM orders AS o JOIN lines AS l ON l.order_id=o.id JOIN payments AS p ON p.order_id=o.id;

╭───────┬──────────┬─────────────╮
│ pairs │ line_sum │ payment_sum │
╞═══════╪══════════╪═════════════╡
│     4 │      600 │         600 │
╰───────┴──────────┴─────────────╯

SELECT o.id,l.line_total,p.paid_total FROM orders AS o JOIN (SELECT order_id,SUM(amount) AS line_total FROM lines GROUP BY order_id) AS l ON l.order_id=o.id JOIN (SELECT order_id,SUM(amount) AS paid_total FROM payments GROUP BY order_id) AS p ON p.order_id=o.id;

╭─────┬────────────┬────────────╮
│ id  │ line_total │ paid_total │
╞═════╪════════════╪════════════╡
│ 101 │        300 │        300 │
╰─────┴────────────┴────────────╯

CREATE TABLE employees(id INTEGER PRIMARY KEY,name TEXT,manager_id INTEGER REFERENCES employees(id)) STRICT;
INSERT INTO employees VALUES(1,'部長',NULL),(2,'課長',1),(3,'担当',2),(4,'顧問',NULL);
SELECT * FROM employees ORDER BY id;

╭────┬──────┬────────────╮
│ id │ name │ manager_id │
╞════╪══════╪════════════╡
│  1 │ 部長 │ NULL       │
│  2 │ 課長 │          1 │
│  3 │ 担当 │          2 │
│  4 │ 顧問 │ NULL       │
╰────┴──────┴────────────╯

SELECT e.id,e.name,m.name AS manager FROM employees AS e LEFT JOIN employees AS m ON m.id=e.manager_id ORDER BY e.id;

╭────┬──────┬─────────╮
│ id │ name │ manager │
╞════╪══════╪═════════╡
│  1 │ 部長 │ NULL    │
│  2 │ 課長 │ 部長    │
│  3 │ 担当 │ 課長    │
│  4 │ 顧問 │ NULL    │
╰────┴──────┴─────────╯

SELECT e.name,m.name AS manager,g.name AS grand_manager FROM employees AS e LEFT JOIN employees AS m ON m.id=e.manager_id LEFT JOIN employees AS g ON g.id=m.manager_id ORDER BY e.id;

╭──────┬─────────┬───────────────╮
│ name │ manager │ grand_manager │
╞══════╪═════════╪═══════════════╡
│ 部長 │ NULL    │ NULL          │
│ 課長 │ 部長    │ NULL          │
│ 担当 │ 課長    │ 部長          │
│ 顧問 │ NULL    │ NULL          │
╰──────┴─────────┴───────────────╯

演習完了