クロス結合の話（LT）

クロス結合 の話 Nextbeat Tech Bar 第一回関係データベース/SQL勉強会 July 26, 2024 Takeshi Yonekubo

About Me • 米久保 剛 (よねくぼ たけし) • SIer勤務 • X: @tyonekubo • 普段使っているDB: PostgreSQL（以前はOracle）

クロス結合（CROSS JOIN） 使ったことありますか？

結合の関係（ベン図） クロス結合 内部結合 内部結合はクロス結合 に完全に包含される 外部結合ははみ出る 外部結合 出典: 『SQL実践入門 高速でわかりやすいクエリの書き方』ミック著 第6章

クロス結合 • 直積・デカルト積 • 全ての組み合わせを求める演算 ペットID ペット 飼い主ID (FK) 100 ポチ 001 200 タマ 002 ペットID ペット 飼い主ID 飼い主 100 ポチ 001 山田 100 ポチ 002 佐藤 100 ポチ 003 鈴木 飼い主ID 飼い主 200 タマ 001 山田 001 山田 200 タマ 002 佐藤 002 佐藤 200 タマ 003 鈴木 003 鈴木

内部結合 • FKで内部結合した結果は、必ずクロス結合の結果に含 まれる ペットID ペット 飼い主ID (FK) 100 ポチ 001 200 タマ 002 ペットID ペット 飼い主ID 飼い主 100 ポチ 001 山田 100 ポチ 002 佐藤 100 ポチ 003 鈴木 飼い主ID 飼い主 200 タマ 001 山田 001 山田 200 タマ 002 佐藤 002 佐藤 200 タマ 003 鈴木 003 鈴木

外部結合 • 結合条件に対応するレコードが存在しなくても結果を 残すので、クロス結合の結果からはみ出る ペットID ペット 飼い主ID (FK) 100 ポチ 001 200 タマ 002 (↑) 飼い主ID 飼い主 001 山田 002 佐藤 003 鈴木 ペットID ペット 飼い主ID 飼い主 100 ポチ 001 山田 200 ポチ 002 佐藤 (null) (null) 003 鈴木

クロス結合が使われない理由 •実際にこういう結果を求めたいケー スがない •非常にコストがかかる演算である 出典: 『SQL実践入門 高速でわかりやすいクエリの書き方』ミック著 第6章

“パフォーマンス観点からは 良いところなしの結合” 出典: 『SQL実践入門 高速でわかりやすいクエリの書き方』ミック著 第6章

うっかりクロス結合 -- 結合条件の記述もれ SELECT * FROM Employees, Departments --------------------------------------------| Id | Operation | Name | Rows | --------------------------------------------| 0 | SELECT STATEMENT | | 640 | | 1 | MERGE JOIN CARTESIAN| | 640 | 出典: 『SQL実践入門 高速でわかりやすいクエリの書き方』ミック著 第6章

クロス結合の結果を求めたい ケースは本当にないのか？

..クロス結合じゃないとできない ケースはほとんどなさそう

..が、「全ての組み合わせ」と いう性質を利用した面白い例も

例 Q. 九九で結果が 12 になる組み合わせを全て求めよ

回答 Q. 九九で結果が 12 になる組み合わせを全て求めよ WITH digits AS ( SELECT * FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS digits(digit) ) SELECT d1.digit AS digit1, d2.digit AS digit2 FROM digits AS d1 CROSS JOIN digits AS d2 WHERE d1.digit * d2.digit = 12 ; 共通テーブル式(CTE)を 使った仮想的な定数 テーブル digit1 digit2 2 6 3 4 4 3 6 2 定数テーブルをクロス結合 （自己結合） →9 x 9 = 81 (通り)

まとめ

üクロス結合は知識として 知っておけば十分 üうっかりクロス結合には 気をつけて！

Fin

だと申し訳ないので..

豆知識

VALUES WITH digits AS ( SELECT * FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS digits(digit) ) SELECT d1.digit AS digit1, d2.digit AS digit2 SELECT * FROM FROM ( digits AS d1 CROSS JOIN digits AS d2 VALUES (1), (2), (3), (4), (5), WHERE (6), (7), (8), (9) d1.digit * d2.digit = 12 ; ) AS digits(digit) (1, ʻONEʼ) のようにタプルで 複数列の値を書くことも可能

INSERT文 • 複数レコードを投入するINSERT文 INSERT INTO pet̲owners VALUES (1, '山田'); INSERT INTO pet̲owners VALUES (2, '佐藤'); INSERT INTO pet̲owners VALUES (3, '鈴木');

INSERT文 • こう書くこともできます！ INSERT INTO pet̲owners VALUES (1, '山田'), (2, '佐藤'), (3, '鈴木');

KEY TAKEAWAY

「VALUESってINSERT文以外 でも使えるって知ってた？」と 同僚に自慢しよう SELECT * FROM ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS digits(digit)

(本当に) Fin