参考書を読んでも分かりづらい
SQLの学習のために参考書を読んでも、「クロス結合が〜〜」「自然結合は〜〜」とたくさんの結合名称が出てきてしまい、こんがらがったのでまとめました。
当記事では内部結合と外部結合以外も紹介しているのですが、内部結合と外部結合さえ知っていればやっていけます。
SQL関連の資格を勉強している方やSQLの知識が浅い方は「そういう結合もあるんだ」くらいの理解をして頂ける記事になっていれば幸いです。
結合の種類
自分がテーブル結合の勉強をしている際に出会った結合名称です。
- 内部結合
- 外部結合(左外部結合、右外部結合)
- 完全外部結合
- 自己結合
- クロス結合
- 自然結合
- 非等価結合
※自分はSQLを使用した実務経験が6ヶ月(2022年1月時点)しかないのですが、内部結合と外部結合の使用がほとんどでした。(自己結合と非等価結合は1,2回の使用)
2テーブルで結合を試していくぅ
SQL> select * from testJoin;
NO NAME P AGE T
---------- ---------- - ---------- -
1 michel 1 20 1
2 jordan 2 30 1
3
4 Lebron 1 25 1
5 hanamiti 2 18 2
0 Popovich 0 80 2
SQL> select * from testJoin2;
NO POSITION
---------- ----------
1 PG
2 SG
3 Substitute内部結合
内部結合は、結合Keyが一致する行のみを取得するための構文です。
下記のSQL文は、「No:0」がtestJoin1に登録されていない為、testJoinの「No:0,NAME:Popovich」のデータは取得されません。
マスタデータがないデータを弾く時によく使用されますね。
SQL> select t1.name , t2.POSITION from testJoin t1
inner join testJoin2 t2 on t2.NO = t1.pname; 2
NAME POSITION
---------- ----------
michel PG
jordan SG
Lebron PG
hanamiti SG
外部結合(左外部結合、右外部結合)
外部結合は、基となるテーブルのデータをすべて残した上での結合となります。
下記SQL文の場合だと、testJoinを基とするため結合Keyに一致しない「No:0」と「No:3」のレコードを取得することが出来ます。
select t1.no , t1.name , t2.POSITION from testJoin t1
left join testJoin2 t2 on t2.NO = t1.pname order by t1.no;
SQL> 2
NO NAME POSITION
---------- ---------- ----------
0 Popovich
1 michel PG
2 jordan SG
3
4 Lebron PG
5 hanamiti SG完全外部結合
完全外部結合は、結合Keyが不一致でも結合するテーブルの全データを取得するための構文です。
結合Keyに一致しない「No:0,NAME:Popovich」「No:3,NAME:NULL」「No:NULL,NAME:NULL,Position:Substitute」の列が取得されていることが分かります。
正直使ったことないです。
SQL> select t1.no , t1.name , t2.POSITION from testJoin t1
Full outer join testJoin2 t2 on t2.NO = t1.pname order by t1.no; 2
NO NAME POSITION
---------- ---------- ----------
0 Popovich
1 michel PG
2 jordan SG
3
4 Lebron PG
5 hanamiti SG
Substitute自己結合
自己結合は、同一のテーブルを別テーブルと考えてデータを取得するための構文です。
下記SQL文では、同一のチームに所属している選手同士を出力しています。(※自己結合は同一テーブルを別テーブルとして扱うので別名称を付与しなければいけません)
余談ですがWHERE句にNAME列の比較演算子を使用しているので、同じ組み合わせのチームを除外しています。
SQL> select t1.no,t1.name,t1.team_id,t2.no,t2.name,t2.team_id from testJoin t1
inner join testJoin t2 on t1.team_id = t2.team_id
where t1.name > t2.name
order by t1.no; 2 3 4
NO NAME T NO NAME T
---------- ---------- - ---------- ---------- -
1 michel 1 2 jordan 1
1 michel 1 4 Lebron 1
2 jordan 1 4 Lebron 1
5 hanamiti 2 0 Popovich 2クロス結合
クロス結合は結合するテーブルのデータ数を乗算する構文のことを指します。
testJoinは6個のデータを、testJoin1は3個のデータを保持しています。
そのため6×3=18行が取得されます。
SQL> select t1.name,t2.POSITION from testJoin t1,testJoin2 t2
order by t1.no,t2.no; 2
NAME POSITION
---------- ----------
Popovich PG
Popovich SG
Popovich Substitute
michel PG
michel SG
michel Substitute
jordan PG
jordan SG
jordan Substitute
PG
SG
NAME POSITION
---------- ----------
Substitute
Lebron PG
Lebron SG
Lebron Substitute
hanamiti PG
hanamiti SG
hanamiti Substitute自然結合
自然結合は、結合するテーブル同士のカラム名称とデータ型をSQLさんが判断してくれる構文です。
下記SQL文だと、testJoinのNo列(NUMBER型)とtestJoin1のNo列(NUMBER型)が同一名称、同一データ型になるためNO列を結合Keyに設定してくれています。
下記のデータを正として取得します。
testJoin「No:1,NAME:michel」= testJoin1「No:1,PG」
testJoin「No:2,NAME:jordan」= testJoin1「No:1,SG」
testJoin「No:3,NAME:NULL」= testJoin1「No:1,Substitute」
多分実務で使うこと無いんじゃないかと思います。
SQL> select no,t1.name,t2.POSITION from testJoin t1
natural LEFt join testJoin2 t2;
NO NAME POSITION
---------- ---------- ----------
0 Popovich
1 michel PG
2 jordan SG
3 Substitute
4 Lebron
5 hanamiti
非等価結合
非等価結合は結合条件に「＝」演算子を使用しない構文を指します。
「＝」を条件に指定しないだけなので、あまり難しく考える必要はありません。
SQL> select * from testJoin t1
inner join testJoin2 t2 on t1.PNAME <> t2.POSITION
where t1.no <3
order by t1.no; 2 3 4
NO NAME P AGE T NO POSITION
---------- ---------- - ---------- - ---------- ----------
0 Popovich 0 80 2 1 PG
0 Popovich 0 80 2 2 SG
0 Popovich 0 80 2 3 Substitute
1 michel 1 20 1 1 PG
1 michel 1 20 1 2 SG
1 michel 1 20 1 3 Substitute
2 jordan 2 30 1 1 PG
2 jordan 2 30 1 2 SG
2 jordan 2 30 1 3 Substitute
