参考書を読んでも分かりづらい
SQLの学習のために参考書を読んでも、「クロス結合が〜〜」「自然結合は〜〜」とたくさんの結合名称が出てきてしまい、こんがらがったのでまとめました。
当記事では内部結合と外部結合以外も紹介しているのですが、内部結合と外部結合さえ知っていればやっていけます。
SQL関連の資格を勉強している方やSQLの知識が浅い方は「そういう結合もあるんだ」くらいの理解をして頂ける記事になっていれば幸いです。
プログラマーとしてLEMP環境に主に生息しており、DevOps 的な立ち回りをしながらご飯を食べている当ブログの管理人のやんやんと申します。
最近はTmux使うのを辞めました。
結合の種類
自分がテーブル結合の勉強をしている際に出会った結合名称です。
- 内部結合
- 外部結合(左外部結合、右外部結合)
- 完全外部結合
- 自己結合
- クロス結合
- 自然結合
- 非等価結合
※自分は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
