Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データベース05: SQL(2/3) 結合質問

データベース05: SQL(2/3) 結合質問

1. 集合演算
2. 結合演算

講義ノートURL
https://dbnote.hontolab.org/content/sql/02.html

Y. Yamamoto

May 20, 2024
Tweet

More Decks by Y. Yamamoto

Other Decks in Technology

Transcript

  1. 典型的な関係データモデル 学生ID 姓 名 入学年 所属 s00001 川澄 桜 2023

    A学部 s00002 山畑 滝子 2024 B学部 s00003 田辺 通 2024 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割
  2. 典型的な関係データモデル 学生ID 姓 名 入学年 所属 s00001 川澄 桜 2023

    A学部 s00002 山畑 滝子 2024 B学部 s00003 田辺 通 2024 C学部 科目ID 科目名 開講年度 c0001 線形代数 2023 c0002 線形代数 2024 c0003 統計学入門 2024 科目ID 学生ID 成績 c0001 s00001 不可 c0002 s00001 良 c0002 s00002 優 c0003 s00003 可 学⽣ 科⽬ 履修 … … … データの正しさの確保するためにデータを複数の表に分割 関係データベースでは 複数の表を組み合わせることが 頻繁に起こる
  3. 集合の代数学 2つの集合から別の集合を作り出す数学的操作 A B 2 4 6 8 10 12

    16 20 A B 2 4 6 8 10 12 16 20 共通集合 𝑨 ∩ 𝑩 和集合 𝑨 ∪ 𝑩 A B 2 4 6 8 10 12 16 20 差集合 𝑨 − 𝑩 1 2 3 合 否 (1, 合) (1, 否) (2, 合) (2, 否) (3, 合) (3, 否) A B 直積集合 𝑨×𝑩 集合を扱う関係データモデルにも集合演算がある
  4. 和集合 SELECT * FROM elderly_population_top10 UNION SELECT * FROM university_student_population_top10;

    地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ∪ UNION句はテーブルAとテーブルBのいずれかに 含まれるレコードを返す
  5. 和集合 SELECT * FROM elderly_population_top10 UNION SELECT * FROM university_student_population_top10;

    UNION句はテーブルAとテーブルBのいずれかに 含まれるレコードを返す 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R04000 福岡県 5124000 11件
  6. 関係データモデルにおける集合演算の注意点 (1/2) SELECT ⽂1 UNION SELECT ⽂2; ここだけ取り出しても 実⾏可能なSQL⽂にする WHERE句なども

    混ぜることが可能 SELECT * FROM elderly_population_top10 WHERE 総⼈⼝ ≧ 7000000 UNION SELECT * FROM university_student_population_top10 WHERE 総⼈⼝ ≧ 7000000;
  7. 関係データモデルにおける集合演算の注意点 (2/2) – 和両⽴ SELECT 列名1, 列名2, 列名3… UNION SELECT

    列名1, 列名2, 列名3… ; (縦方向の)関係の集合演算では 関係の属性(列)集合の要素と順序が一致する必要あり 和両⽴条件 完全⼀致
  8. 共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000

    … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ∪ SELECT * FROM elderly_population_top10 INTERSECT SELECT * FROM university_student_population_top10; INTERSECT句はテーブルAとテーブルBの 両方に含まれるレコードを返す
  9. 共通集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000

    … R28000 兵庫県 5432000 R40000 福岡県 5124000 9件 SELECT * FROM elderly_population_top10 INTERSECT SELECT * FROM university_student_population_top10; INTERSECT句はテーブルAとテーブルBの 両方に含まれるレコードを返す
  10. 差集合 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000

    … R22000 静岡県 3608000 … 地域コード 都道府県 総人口 R01000 北海道 5183000 R11000 埼玉県 7340000 … R26000 京都府 2561000 … elderly_population_top10 university_student_population_top10 ー SELECT * FROM elderly_population_top10 EXCEPT SELECT * FROM university_student_population_top10; EXCEPT句はテーブルAには存在するがテーブルB には存在しないレコードを返す
  11. 差集合 SELECT * FROM elderly_population_top10 EXCEPT SELECT * FROM university_student_population_top10;

    地域コード 都道府県 総人口 R22000 静岡県 3608000 A ‒ BとB ‒ Aの集合演算は結果が異なることに注意 EXCEPT句はテーブルAには存在するがテーブルB には存在しないレコードを返す
  12. 今⽇のメニュー 集合演算 + = + = 結合演算 表を縦⽅向につなぐ (レコードの数を操作する) 表を横⽅向につなぐ

    (関連するレコードをつなぐ) 結合演算を使いこなすことが関係データベース操作の鍵
  13. 直積 集合S1 , S2 , …, Sn が与えられたとき, 𝑆! ×𝑆"

    …×𝑆# = 𝑥! , … , 𝑥# 𝑥! ∈ 𝑆! , … , 𝑥# ∈ 𝑆# } なる 𝑆! × ⋯×𝑆" を S1 , …, Sn 上の直積集合と呼ぶ 例 A = 2, 3 , 𝐵 = {−2, −3} が与えられたとき A×𝐵 = 2, −2 , 2, −3 , 3, −2 , (3, −3) 2 A 3 -2 B -3
  14. 直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 データベース 2年前期 機械学習 3年前期 統計モデリング

    2年後期 氏名 職階 山畑 教授 桜山 教授 川澄 准教授 田辺 講師 テーブル「科⽬」 テーブル「教員」 × 直積 SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す
  15. 直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑

    教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ CROSS JOIN 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す 12件
  16. 直積(交差結合; CROSS JOIN)(1/2) 科目名 開講時期 氏名 職階 データベース 2年前期 山畑

    教授 データベース 2年前期 桜山 教授 データベース 2年前期 川澄 准教授 データベース 2年前期 田辺 講師 機械学習 3年前期 山畑 教授 … SELECT * FROM 科⽬ , 教員; 直積はテーブルAとテーブルBにあるレコードの すべての組み合わせを返す CROSS JOINを カンマで省略表記
  17. 直積(交差結合; CROSS JOIN)(2/2) SELECT * FROM university_student_population_top10 AS u, activity

    AS a WHERE u.都道府県 = a.都道府県; WHERE句と組み合わせれば直積結果を絞り込める 名前が⻑いので AS修飾句で別名をつける 特定のテーブルの要素を 明⽰的に参照するときはドットを使う
  18. 内部結合(INNER JOIN)(1/3) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2

    2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 商品マスタに商品Dに関するデータがないので無視される 指定した列が指定条件を満たす行を探して 2つの表の行を結合 商品IDが同じ⾏を探して結合
  19. 内部結合(INNER JOIN)(2/3) SELECT 購買履歴.*, 商品.* FROM 購買履歴 INNER JOIN 商品

    ON 購買履歴.商品ID = 商品.商品ID; 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 結合条件 商品IDが同じ⾏を探して結合
  20. 内部結合(INNER JOIN)(3/3) SELECT 購買履歴.*, 商品.* FROM 購買履歴 INNER JOIN 商品

    USING (商品ID); 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 結合に使う列名が 2つの表で同じであれば USINGで条件を短く書ける 商品IDが同じ⾏を探して結合
  21. 直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, activity

    AS a WHERE u.都道府県 = a.都道府県; 直積の場合 直積集合をすべて生成した後,WHERE句で絞り込む ①ここで直積集合を作った後 ②直積集合から該当レコードを絞り込む
  22. 直積に対する絞り込み vs. 内部結合 SELECT * FROM university_student_population_top10 AS u, INNER

    JOIN u USING (都道府県); 内部結合の場合 結合条件を満たす組み合わせのみを抽出し表示 内部的には直積を計算していない
  23. JOINとWHEREの評価順序 SELECT university_student_population_top10.*, activity.学習・⾃⼰啓発・訓練 AS 学習・⾃⼰鍛錬 FROM university_student_population_top10 AS u

    INNER JOIN activity ON u.都道府県 = activity.都道府県 WHERE u.総⼈⼝ >= 7000000; ①まず内部結合 ②FROM句の内容を条件で絞り込む
  24. 外部結合(OUTER JOIN) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2

    2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 指定した列が指定条件を満たす表の行を結合 (条件を満たさない時,結合される側の⾏に空値で結合する) 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 商品IDが同じ⾏を探して結合
  25. 外部結合(OUTER JOIN) 日時 顧客ID 商品ID 個数 2019/1/1 い A 2

    2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 指定した列が指定条件を満たす表の行を結合 (条件を満たさない時,結合される側の⾏に空値で結合する) 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 商品IDが同じ⾏を探して結合 NULL値
  26. 左外部結合(LEFT OUTER JOIN) SELECT 購買履歴.*, 商品.* FROM 購買履歴 LEFT OUTER

    JOIN 商品 ON 購買履歴.商品ID = 商品.商品ID; 結合条件 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 商品IDが同じ⾏を探して結合 日時 顧客ID 商品ID 個数 商品名 単価 2019/1/1 い A 2 伊左衛門 130 2019/1/1 い B 3 午前の紅茶 150 2022/3/7 に D 8 NULL値
  27. 右外部結合(RIGHT OUTER JOIN) SELECT 購買履歴.*, 商品.* FROM 購買履歴 RIHGT OUTER

    JOIN 商品 ON 購買履歴.商品ID = 商品.商品ID; 日時 顧客ID 商品ID 個数 2019/1/1 い A 2 2019/1/1 い B 3 2022/3/7 に D 8 購買履歴 商品ID 商品名 単価 A 伊左衛門 130 B 午前の紅茶 150 C おいしい牛乳 250 商品 商品IDが同じ⾏を探して結合 日時 顧客ID 個数 商品ID 商品名 単価 2019/1/1 い 2 A 伊左衛門 130 2019/1/1 い 3 B 午前の紅茶 150 C おいしい牛乳 250 NULL値
  28. 外部結合の例 SELECT university_student_population_top10.*, activity.学習・⾃⼰啓発・訓練 AS 学習・⾃⼰鍛錬 FROM university_student_population_top10 AS u

    LEFT OUTER JOIN activity ON u.都道府県 = activity.都道府県; 左外部結合 外部結合は条件を満たさなかったレコードを⾒つけることが可能
  29. 回 実施日 トピック 1 04/15 ガイダンス:データベースを使わない世界 2 04/22 データベースの概念 3

    04/29(祝) 関係データモデル 4 05/13 SQL (1/3) 5 05/20 SQL (2/3) 6 05/27 SQL (3/3) 7 06/03 SQL演習 – レポート課題1 8 06/10 実体関連モデル (1/3) 9 06/17 実体関連モデル (2/3) 10 06/24 実体関連モデル (3/3) 11 07/01 正規化 (1/2) 12 07/08 正規化 (2/2) 13 07/15(祝) データベース設計演習 – レポート課題2 14 07/22 索引付け 15 07/29 NoSQL 16 08/05 期末試験 今後の予定 54