データベース

OracleDatabase 表結合について理解をまとめる(12c)

OracleMasterSilverSQL2019を取得するために勉強中です。ちなみに勉強はOracle SQL Liveを利用すると環境構築せずにSQLを試せるので良いです。

表結結合周りについて理解をまとめておきます。図はおいおい作成して貼る。

表結合の種類

表結合は以下

  • 自然結合(NATURAL JOIN)
  • USING句を利用した結合
  • ON句を利用した結合
  • 非等価結合
  • 自己結合
  • 外部結合
  • クロス結合

自然結合(NATURAL JOIN)

自然結合とは自然な感じに表間を結合すると覚えました。もし「2つの表があった場合に結合するとしたらどう結合するか」を考えれば、おのずと結合した形が分かります。

自然結合・・・同じカラム名・同じ型のところで結合

データベースが自動的にどのカラムを結合か判定するため、結合するカラムを指定する必要がありません。

// 自然結合
select * from dept natural join emp;

 

Using句を利用した結合

自然結合ではデータベースが自動的に結合するカラムを判定していましたが、明示的に結合するカラムを示したい場合に利用する。

例えば表2つに同一名・同一型のカラムが2つある場合、一方だけを結合したいということもあると思います。結合するカラムは「(括弧)」で囲むこと。

// usingを利用した結合
select * from dept join emp using (dept_id);

 

ON句を利用した結合

自然結合・Usingを利用した結合は同一名のテーブル結合でした。ON句を利用すると名前が異なるカラム名の列同士を結合できます。

以下は同名なのでusing(dept_id)と意味は同じになります。

// on句を利用した結合
select * from dept join emp on dept.dept_id = emp.dept_id;

3つの表を結合する場合は以下のようになる。N個の表を結合する場合はN-1個のJOIN ONを記載することとなる。

// 3つの表を結合
select * from emp 
    join company on emp.company_id = company.id
    join dept    on emp.dept_id = dept.dept_id
    order by dept.dept_id;

 

非等価結合

表の結合は「=」以外に以下も可能。非等価結合とは等価結合(=)以外での結合を意味する。

  • >=
  • <=
  • BETWEEN
// 非等価結合「>」
select * from emp 
    join dept
    on emp.dept_id > dept.dept_id;

 

自己結合

自己結合とは1つの表を別名で定義し、別名表を結合すること。

例えば従業員表の場合、

  • 従業員ID
  • 従業員の上司の従業員ID

が1つの表にある場合がある。この場合自己結合を利用すれば、従業員に紐づく上司がだれかわかる。

自己結合の場合は表の別名をつけること。

// 自己結合
select * from emp w join emp m on w.mng_id = m.id;

 

外部結合

外部結合とは結合条件を満たすだけでなく、満たしていないデータまで取得する結合のこと。

  • 左側外部結合・・・左側は全量取得、右側は紐づかない列はNULL
  • 右側外部結合・・・右側は全量取得、左側は紐づかない列はNULL
  • 完全外部結合・・・左右全量取得、紐づかない列はNULL

の3種類存在する。

// 左外部結合
select * from emp left join dept on emp.dept_id = dept.dept_id;
select * from dept left join emp on dept.dept_id = emp.dept_id;
// 右外部結合
select * from emp right join dept on emp.dept_id = dept.dept_id;
select * from dept right join emp on dept.dept_id = emp.dept_id;
// 完全外部結合
select * from emp full outer join dept on emp.dept_id = dept.dept_id;

 

クロス結合

デカルト積(直積)を求める結合方法。

2つの表があり、1つが5レコード、1つが3レコードの場合、5×3の15レコードのデータが生成される。

// クロス結合
select * from emp cross join dept;

これは以下と同等。

/ / from句で複数指定
select * from emp, dept;