- 前回の勉強内容
- 勉強のきっかけになった問題
- 結合にはいろんな方法がある
- 自己結合
- 外部結合 : OUTER JOIN
- 内部結合/単純結合 : INNER JOIN / JOIN
- 直積結合/交差結合 : CROSS JOIN
- 次回の勉強内容
前回の勉強内容
勉強のきっかけになった問題
"部品"表から，部品名に'N11'が含まれる部品情報(部品番号，部品名)を検索するSQL文がある。このSQL文は，検索対象の部品情報のほか，対象部品に親部品番号が設定されている場合は親部品情報を返し，設定されていない場合はNULLを返す。aに入れる字句はどれか。ここで，実線の下線は主キーを表す。
部品（部品番号，部品名，親部品番号）
〔SQL文〕
SELECT B1.部品番号, B1.部品名,
B2.部品番号 AS 親部品番号, B2.部品名 AS 親部品名
FROM 部品 [ a ]
ON B1.親部品番号=B2.部品番号
WHERE B1.部品名 LIKE ‘%N11%’
- B1 JOIN 部品 B2
- B1 LEFT OUTER JOIN 部品 B2
- B1 RIGHT OUTER JOIN 部品 B2
- B2 LEFT OUTER JOIN 部品 B1
出典 : 平成30年 春期 データベーススペシャリスト試験 午前Ⅱ 問8
結合にはいろんな方法がある
結合にはたくさん種類があります。「〜結合」がたくさんあるんです、しかも「結合」とは別に「連結」すらあります。
試験はよく日本語で書かれるのですが、日頃SQLでしか書かないのでとっても分かりづらいです。
このページでは「MySQL8.0」を使ってSQLを実際に実行してみました。
自己結合
自己結合は、1つのテーブルを2つあるように見立てて結合することです。自分同士をくっつけます。
「勉強のきっかけになった問題」は、1つの部品テーブルを「B1」「B2」の2つあるように見立てて結合しています。
-- ↓自分と ↓自分をJOIN ... FROM 部品 B1 LEFT JOIN 部品 B2 ON B1.親部品番号=B2.部品番号 WHERE ...
外部結合 : OUTER JOIN
外部結合は、くっつけるテーブルの「どちらかにしかないレコード」も取得する方法です。
SQLでは「外部結合」という方法で、結合先のテーブルに対応する値を含む行が存在しなくても、結合元の行をすべて表示させることができます。
独習SQL 第2版（長谷川 裕行 黒石 博明）｜翔泳社の本
「勉強のきっかけになった問題」の部品テーブルがこんな感じだった場合で考えます。
mysql> select * from 部品; +--------------+---------------------+-----------------+ | 部品番号 | 部品名 | 親部品番号 | +--------------+---------------------+-----------------+ | P01 | ペンN11 | NULL | | P02 | 修正用品 | NULL | | P03 | 留め具 | NULL | | P04 | メモ用紙N111 | NULL | | S01 | ボールペンN110 | P01 | | S02 | N11消しゴム | P02 | | S03 | クリップM10 | P03 | | S04 | のりN11 | P03 | | S05 | N110付箋 | NULL | +--------------+---------------------+-----------------+ 9 rows in set (0.01 sec)
「勉強のきっかけになった問題」では「対象部品に親部品番号が設定されている場合は親部品情報を返し，設定されていない場合はNULLを返す」ので左に指定される「部品 B1」を全部表示させるために「B1 LEFT OUTER JOIN 部品 B2」が答えになります。
mysql> SELECT B1.部品番号, B1.部品名, B2.部品番号 AS 親部品番号, B2.部品名 AS 親部品名 FROM 部品 B1 LEFT OUTER JOIN 部品 B2 ON B1.親部品番号=B2.部品番号 WHERE B1.部品名 LIKE '%N11%'; +--------------+---------------------+-----------------+--------------+ | 部品番号 | 部品名 | 親部品番号 | 親部品名 | +--------------+---------------------+-----------------+--------------+ | P01 | ペンN11 | NULL | NULL | | P04 | メモ用紙N111 | NULL | NULL | | S01 | ボールペンN110 | P01 | ペンN11 | | S02 | N11消しゴム | P02 | 修正用品 | | S04 | のりN11 | P03 | 留め具 | | S05 | N110付箋 | NULL | NULL | +--------------+---------------------+-----------------+--------------+ 6 rows in set (0.00 sec)
左外部結合 : LEFT OUTER JOIN / LEFT JOIN
「左」に指定するテーブルのレコードを全部取得する方法です。
"社員取得資格"表に対し，SQL文を実行して結果を得た。SQL文のaに入れる字句はどれか。
社員取得資格
社員コード 資格 S001 FE S001 AP S001 DB S002 FE S002 SM S003 FE S004 AP S005 NULL 〔結果〕
社員コード 資格1 資格2
------------------------------
S001 FE AP
S002 FE NULL
S003 FE NULL
〔SQL文〕
SELECT C1.社員コード, C1.資格 AS 資格1, C2.資格 AS 資格2
FROM 社員取得資格 C1 LEFT OUTER JOIN 社員取得資格 C2 [ a ]
- ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C1.資格 = 'FE'
- ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C1.資格 IS NOT NULL
- ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C2.資格 = 'AP'
- ON C1.社員コード = C2.社員コード WHERE C1.資格 = 'FE' AND C2.資格 = 'AP'
出典 : 令和3年 秋期 データベーススペシャリスト試験 午前Ⅱ 問8
-- ①「LEFT OUTER JOIN」なのでC1の全レコードに mysql> SELECT C1.社員コード, C1.資格 AS 資格1 FROM 社員取得資格 C1; +-----------------+---------+ | 社員コード | 資格1 | +-----------------+---------+ | S001 | FE | | S001 | AP | | S001 | DB | | S002 | FE | | S002 | SM | | S003 | FE | | S004 | AP | | S005 | NULL | +-----------------+---------+ 8 rows in set (0.00 sec) -- ②C2の「AP」になるレコードを mysql> SELECT C2.社員コード, C2.資格 AS 資格2 FROM 社員取得資格 C2 WHERE C2.資格 = 'AP'; +-----------------+---------+ | 社員コード | 資格2 | +-----------------+---------+ | S001 | AP | | S004 | AP | +-----------------+---------+ 2 rows in set (0.00 sec) -- ③資格1が「FE」になるレコードだけにくっつけて mysql> SELECT C1.社員コード, C1.資格 AS 資格1, C2.資格 AS 資格2 FROM 社員取得資格 C1 LEFT OUTER JOIN 社員取得資格 C2 ON C1.社員コード = C2.社員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP'; +-----------------+---------+---------+ | 社員コード | 資格1 | 資格2 | +-----------------+---------+---------+ | S001 | FE | AP | | S001 | AP | NULL | | S001 | DB | NULL | | S002 | FE | NULL | | S002 | SM | NULL | | S003 | FE | NULL | | S004 | AP | NULL | | S005 | NULL | NULL | +-----------------+---------+---------+ 8 rows in set (0.00 sec) -- ④資格1が「FE」のレコードだけに絞り込むと〔結果〕になる mysql> SELECT C1.社員コード, C1.資格 AS 資格1, C2.資格 AS 資格2 FROM 社員取得資格 C1 LEFT OUTER JOIN 社員取得資格 C2 ON C1.社員コード = C2.社 員コード AND C1.資格 = 'FE' AND C2.資格 = 'AP' WHERE C1.資格 = 'FE'; +-----------------+---------+---------+ | 社員コード | 資格1 | 資格2 | +-----------------+---------+---------+ | S001 | FE | AP | | S002 | FE | NULL | | S003 | FE | NULL | +-----------------+---------+---------+ 3 rows in set (0.01 sec)
右外部結合 : RIGHT OUTER JOIN / RIGHT JOIN
「右」に指定するテーブルのレコードを全部取得する方法です。
完全外部結合 : FULL OUTER JOIN / FULL JOIN
「左右両方」に指定するテーブルのレコードを全部取得する方法です。
表Rと表Sに対し，SQL文を実行して結果を得るとき，aに入れる字句はどれか。ここで，結果のNULLは値が存在しないことを表す。
R
ID 名称1 1 AAA 2 BBB 3 CCC S
ID 名称2 2 bbb 3 ccc 4 ddd 〔結果〕
ID 名称1 名称2
------------------------------
1 AAA NULL
2 BBB bbb
3 CCC ccc
4 NULL ddd
〔SQL文〕
SELECT [ a ] (R.ID, S.ID) AS ID, 名称1, 名称2 FROM R FULL OUTER JOIN S ON R.ID = S.ID ORDER BY IDア. COALESCE イ. DISTINCT ウ. NULLIF エ. UNIQUE
-- この「FULL JOIN」は、MySQLでは使えません。なので、「LEFT JOIN」「RIGHT JOIN」をくっつけて「FULL JOIN」と同じ結果を取得します。 mysql> SELECT * FROM R LEFT JOIN S ON R.ID = S.ID UNION SELECT * FROM R RIGHT JOIN S ON R.ID = S.ID; +------+---------+------+---------+ | ID | 名称1 | ID | 名称2 | +------+---------+------+---------+ | 1 | AAA | NULL | NULL | | 2 | BBB | 2 | bbb | | 3 | CCC | 3 | ccc | | NULL | NULL | 4 | ddd | +------+---------+------+---------+ 4 rows in set (0.01 sec) --２つある「ID」のうちNULLじゃない方を返却する関数なのでCOALESCEが正解です。日本語で「融合する」で「コウアレス」と読みます。 mysql> SELECT COALESCE(RID,SID) AS ID,名称1,名称2 FROM (SELECT R.ID AS RID,S.ID AS SID,名称1,名称2 FROM R LEFT JOIN S ON R.ID = S.ID UNION SELECT R.ID AS RID,S.ID AS SID,名称1,名称2 FROM R RIGHT JOIN S ON R.ID = S.ID) AS A; +------+---------+---------+ | ID | 名称1 | 名称2 | +------+---------+---------+ | 1 | AAA | NULL | | 2 | BBB | bbb | | 3 | CCC | ccc | | 4 | NULL | ddd | +------+---------+---------+ 4 rows in set (0.00 sec)
内部結合/単純結合 : INNER JOIN / JOIN
内部結合は、くっつけるテーブルの「どっちにもあるレコード」も取得する方法です。
直積結合/交差結合 : CROSS JOIN
直積結合は、くっつけるテーブルの「全部を全組合せ」で取得する方法です。
-- Rテーブルと mysql> SELECT * FROM R; +----+---------+ | ID | 名称1 | +----+---------+ | 1 | AAA | | 2 | BBB | | 3 | CCC | +----+---------+ 3 rows in set (0.01 sec) -- Sテーブルを mysql> SELECT * FROM S; +----+---------+ | ID | 名称2 | +----+---------+ | 2 | bbb | | 3 | ccc | | 4 | ddd | +----+---------+ 3 rows in set (0.00 sec) -- 直積結合すると全組み合わせが取得できる mysql> select * from r cross join s; +----+---------+----+---------+ | ID | 名称1 | ID | 名称2 | +----+---------+----+---------+ | 3 | CCC | 2 | bbb | | 2 | BBB | 2 | bbb | | 1 | AAA | 2 | bbb | | 3 | CCC | 3 | ccc | | 2 | BBB | 3 | ccc | | 1 | AAA | 3 | ccc | | 3 | CCC | 4 | ddd | | 2 | BBB | 4 | ddd | | 1 | AAA | 4 | ddd | +----+---------+----+---------+ 9 rows in set (0.01 sec)
