【初心者向け】表の結合入門

オラクルデータベースの基本
2024.10.03

本記事では、Oracleデータベースの基本的な操作である「表の結合」について、Oracleのデフォルトスキーマである「SCOTT」スキーマを使ってわかりやすく解説していきます。SCOTTスキーマは、データベースを学習する際のベンチマークとなるシンプルなデータ構造を持っていますので、実際の業務に入る前にしっかりと基本を身につけましょう。

1.表の結合

データベースの中では、データはしばしば「テーブル」と呼ばれる表に整理されて保存されています。でも、全ての情報を1つのテーブルに入れると、データが増えれば増えるほど、管理が難しくなります。そのため、データをテーマごとに別々のテーブルに分けて保存するのが一般的です。この分割した情報をうまく組み合わせるために使うのが「表の結合（JOIN）」です。

たとえば、学校のデータを考えてみましょう。1つのテーブルには「生徒の情報」が、もう1つのテーブルには「クラスの情報」が入っています。この2つのテーブルを結合することで、どの生徒がどのクラスに所属しているのか、簡単に確認できるようになります。

結合を使うと、関連する情報をまとめて表示することができ、たとえば「全ての生徒の名前と、その所属クラス名を一度に見たい」というようなときに非常に便利です。

では、表の結合を使うメリットを、もっと簡単に説明しますね。

1. 情報を整理して保存できる

1つのテーブルに全ての情報を入れなくても、テーマごとに情報を分けて保存できます。その後、必要に応じて結合すれば、簡単に情報を取り出すことができます。これにより、データが複雑になっても整理して管理しやすくなります。

例えば：

  • 生徒の情報は「生徒テーブル」
  • クラスの情報は「クラステーブル」 と分けて保存できますが、結合すれば「生徒の名前」と「所属クラス」を一緒に見ることができます。

2. 一度に複数の情報を取り出せる

結合を使うと、複数のテーブルから必要な情報をまとめて取得できます。例えば、生徒とクラスの情報を持つ2つのテーブルを結合して、「どの生徒がどのクラスに所属しているか」という情報を一度に取り出すことが可能です。

3. データの一貫性が保てる

データを分けて保存することで、一部の情報を変更したときに他の場所に影響を与えることなく、全てが正しいまま保たれます。例えば、クラス名が変わったときも、クラスのテーブルを修正するだけで、生徒の情報と整合性が保たれます。

4. データの重複を防げる

同じ情報を何度も繰り返し保存する必要がなくなります。たとえば、生徒テーブルにクラス名も一緒に保存してしまうと、クラス名を変えるたびに全ての生徒データを修正する必要がありますが、別のテーブルに分けて保存すれば、クラス名だけを1箇所で修正すれば済みます。

5. 柔軟にデータを組み合わせて分析できる

テーブルを結合することで、必要に応じてデータを自由に組み合わせることができます。たとえば、どのクラスにどれだけの生徒がいるか、または特定のクラスに所属する生徒のリストなどを簡単に取得できます。

結合のイメージ

結合をイメージするには、2つの表（テーブル）がパズルのピースのように噛み合うと考えてください。どこでピースを合わせるかは、表と表の間で共通する情報（例えば、生徒IDやクラスID）によって決まります。

たとえば：

  • 「生徒テーブル」にある「クラスID」と、
  • 「クラステーブル」にある「クラスID」

この2つを基にして結合します。そうすることで、どの生徒がどのクラスに所属しているのかがわかります。

具体的な例

例：生徒とクラスの情報を表示したい

  1. 生徒テーブルには、生徒ID、名前、クラスIDが保存されています。
  2. クラステーブルには、クラスID、クラス名が保存されています。

これら2つのテーブルを結合して、全ての生徒の名前と所属するクラス名を表示させると、こんな結果が得られます。

生徒ID名前クラス名
1太郎数学クラス
2花子英語クラス
3次郎理科クラス

2. SCOTTスキーマとは？

まず、SCOTTスキーマについて簡単に説明します。SCOTTスキーマは、Oracleデータベースに標準で含まれているサンプルスキーマです。SCOTTスキーマの中には、いくつかの重要なテーブルがあります。その中でも、代表的なテーブルは以下の4つです：

  • EMP（従業員情報）
  • DEPT（部署情報）
  • SALGRADE（給与グレード）
  • BONUS（ボーナス情報）

これらのテーブルを使用して、さまざまなSQLクエリを練習することができます。
本記事では以下のSCOTTスキーマのDEPT表とEMP表を使用します。

オラクルデータベースには学習用スキーマとしてSCOTTスキーマ等のサンプルスキーマが用意されています。
SCOTTスキーマをインストールする場合は、以下を実行します。

SQL> @?/rdbms/admin/utlsampl.sql

【SCOTTサンプルスキーマをインストール】

SQL> @?/rdbms/admin/utlsampl.sql
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0との接続が切断されました。
[oracle@v19single ~]$ sqlplus scott/tiger
 
SQL*Plus: Release 19.0.0.0.0 - Production on 火 10月 1 19:51:59 2024
Version 19.21.0.0.0
 
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
 
 
最終正常ログイン時間: 火 10月 01 2024 19:39:47 +09:00
 
 
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
に接続されました。
SQL> show user
ユーザーは"SCOTT"です。


SCOTTスキーマで以下のDEPT表とEMP表を使用します。
DEPT表とEMP表には共通でDEPTNOという列があり、この列を元にして結合を行います。
それぞれ個別にSELECTした結果は以下です。

SQL> show user
ユーザーは"SCOTT"です。
SQL> select deptno,dname from dept;
 
    DEPTNO DNAME
---------- ------------------------------------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS
 
SQL> select empno,ename,deptno from emp;
 
     EMPNO ENAME                              DEPTNO
---------- ------------------------------ ----------
      7369 SMITH                                  20
      7499 ALLEN                                  30
      7521 WARD                                   30
      7566 JONES                                  20
      7654 MARTIN                                 30
      7698 BLAKE                                  30
      7782 CLARK                                  10
      7839 KING                                   10
      7844 TURNER                                 30
      7900 JAMES                                  30
      7902 FORD                                   20
      7934 MILLER                                 10
 
12行が選択されました。
Oracleデータベースの基礎：サンプルスキーマで簡単に始める方法
サンプルスキーマって何？まず、サンプルスキーマとは何かから始めましょう。サンプルスキーマは、Oracleデータベースに含まれる「学習用のデータベース構造とデータ」のことで、インストールすることで学習に使用できるユーザーや表が作成されます。O...
oracle-master00000.com
2024.09.23

3. SQLの基本的なSELECT文の構造

まず、表の結合を行う前に、基本的なSQLのSELECT文の構造を確認しておきましょう。

SELECT 列名1, 列名2, …FROM テーブル名WHERE 条件;

ここで、SELECT句には取得したい列を指定し、FROM句にはデータを取得するテーブルを指定します。WHERE句は、データの絞り込みに使います。

4. INNER JOIN（内部結合）の構文

INNER JOINは、2つのテーブルの間で共通する行を取得します。結合条件が一致する行だけが結果として返されます。INNER JOINの構文は以下のようになります。

SELECT <テーブル名>.<列名1>, <テーブル名>.<列名2>, ...
FROM <テーブルA>
INNER JOIN <テーブルB> ON <テーブルA>.<共通の列> = <テーブルB>.<共通の列>;

各テーブルに同名の列が存在する可能性があるため、列名は<テーブル名>.<列名>のように指定する必要があります。

例：EMPテーブルとDEPTテーブルを部署番号（DEPTNO）で結合

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
FROM EMP
INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

この例では、従業員の情報が格納されているEMPテーブルと部署の情報が格納されているDEPTテーブルを、部署番号（DEPTNO）を使って内部結合しています。

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
  2  FROM EMP
  3  INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
 
     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          RESEARCH
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          ACCOUNTING
      7839 KING                           ACCOUNTING
      7844 TURNER                         SALES
      7900 JAMES                          SALES
      7902 FORD                           RESEARCH
      7934 MILLER                         ACCOUNTING
 
12行が選択されました。

5. LEFT OUTER JOIN（左外部結合）の構文

LEFT JOINは、左側のテーブルの全ての行を返し、右側のテーブルに一致する行がある場合にその行を返します。もし右側のテーブルに一致する行がない場合、その部分にはNULLが挿入されます。

SELECT <テーブル名>.<列名1>, <テーブル名>.<列名2>, ...
FROM <テーブルA>
LEFT OUTER JOIN <テーブルB> ON <テーブルA>.<共通の列> = <テーブルB>.<共通の列>;

例：EMPテーブルの全従業員と、存在する場合は対応する部署を表示

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
FROM EMP
LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

このクエリでは、全ての従業員が表示され、もし従業員に部署が割り当てられていない場合、その部署の名前にはNULLが表示されます。

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
  2  FROM EMP
  3  LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
 
     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7782 CLARK                          ACCOUNTING
      7839 KING                           ACCOUNTING
      7934 MILLER                         ACCOUNTING
      7369 SMITH                          RESEARCH
      7566 JONES                          RESEARCH
      7902 FORD                           RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7844 TURNER                         SALES
      7900 JAMES                          SALES
 
12行が選択されました。

6. RIGHT OUTER JOIN（右外部結合）の構文

RIGHT JOINは、左外部結合の逆です。右側のテーブルの全ての行が返され、左側のテーブルに一致する行がある場合にその行が返されます。左側に一致する行がない場合、その部分はNULLが返されます。

SELECT <テーブル名>.<列名1>, <テーブル名>.<列名2>, ...
FROM <テーブルA>
RIGHT OUTER JOIN <テーブルB> ON <テーブルA>.<共通の列> = <テーブルB>.<共通の列>;

例：DEPTテーブルの全ての部署と、存在する場合は対応する従業員を表示

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
FROM EMP
RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

このクエリでは、全ての部署が表示され、対応する従業員がいない部署にはNULLが表示されます。

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
  2  FROM EMP
  3  RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
 
     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          RESEARCH
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          ACCOUNTING
      7839 KING                           ACCOUNTING
      7844 TURNER                         SALES
      7900 JAMES                          SALES
      7902 FORD                           RESEARCH
      7934 MILLER                         ACCOUNTING
                                          OPERATIONS  ★EMP表のDEPT列と一致しない値
 
13行が選択されました。

7. FULL OUTER JOIN（完全外部結合）の構文

FULL OUTER JOINは、左側および右側の両方のテーブルから全ての行を返します。両方のテーブルで一致する行がない場合、NULLが返されます。

SELECT <テーブル名>.<列名1>, <テーブル名>.<列名2>, ...
FROM <テーブルA>
FULL OUTER JOIN <テーブルB> ON <テーブルA>.<共通の列> = <テーブルB>.<共通の列>;

例：EMPテーブルとDEPTテーブルの全ての従業員と全ての部署を表示

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
FROM EMP
FULL OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

このクエリでは、全ての従業員と全ての部署が表示され、部署が割り当てられていない従業員や従業員がいない部署にはNULLが表示されます。

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
  2  FROM EMP
  3  FULL OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
 
     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          RESEARCH
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          RESEARCH
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          ACCOUNTING
      7839 KING                           ACCOUNTING
      7844 TURNER                         SALES
      7900 JAMES                          SALES
      7902 FORD                           RESEARCH
      7934 MILLER                         ACCOUNTING
                                          OPERATIONS
 
13行が選択されました。

8. CROSS JOIN（クロス結合）の構文

CROSS JOINは、2つのテーブルの全ての組み合わせを返す結合方法です。CROSS JOINは、結合条件を指定せずに全ての行を結合するため、結果の行数が非常に多くなる可能性があります。

SELECT <テーブル名>.<列名1>, <テーブル名>.<列名2>, ...
FROM <テーブルA>
CROSS JOIN <テーブルB>;

例：EMPテーブルとDEPTテーブルの全ての組み合わせを表示

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
FROM EMP
CROSS JOIN DEPT;

このクエリは、従業員と部署の全ての組み合わせを返します。たとえば、EMPテーブルに10人の従業員がいて、DEPTテーブルに3つの部署がある場合、結果として30行が返されます。

SQL> SELECT EMP.EMPNO, EMP.ENAME, DEPT.DNAME
  2  FROM EMP
  3  CROSS JOIN DEPT;
 
     EMPNO ENAME                          DNAME
---------- ------------------------------ ------------------------------------------
      7369 SMITH                          ACCOUNTING
      7499 ALLEN                          ACCOUNTING
      7521 WARD                           ACCOUNTING
      7566 JONES                          ACCOUNTING
      7654 MARTIN                         ACCOUNTING
      7698 BLAKE                          ACCOUNTING
      7782 CLARK                          ACCOUNTING
      7839 KING                           ACCOUNTING
      7844 TURNER                         ACCOUNTING
      7900 JAMES                          ACCOUNTING
      7902 FORD                           ACCOUNTING
      7934 MILLER                         ACCOUNTING
      7369 SMITH                          RESEARCH
      7499 ALLEN                          RESEARCH
      7521 WARD                           RESEARCH
      7566 JONES                          RESEARCH
      7654 MARTIN                         RESEARCH
      7698 BLAKE                          RESEARCH
      7782 CLARK                          RESEARCH
      7839 KING                           RESEARCH
      7844 TURNER                         RESEARCH
      7900 JAMES                          RESEARCH
      7902 FORD                           RESEARCH
      7934 MILLER                         RESEARCH
      7369 SMITH                          SALES
      7499 ALLEN                          SALES
      7521 WARD                           SALES
      7566 JONES                          SALES
      7654 MARTIN                         SALES
      7698 BLAKE                          SALES
      7782 CLARK                          SALES
      7839 KING                           SALES
      7844 TURNER                         SALES
      7900 JAMES                          SALES
      7902 FORD                           SALES
      7934 MILLER                         SALES
      7369 SMITH                          OPERATIONS
      7499 ALLEN                          OPERATIONS
      7521 WARD                           OPERATIONS
      7566 JONES                          OPERATIONS
      7654 MARTIN                         OPERATIONS
      7698 BLAKE                          OPERATIONS
      7782 CLARK                          OPERATIONS
      7839 KING                           OPERATIONS
      7844 TURNER                         OPERATIONS
      7900 JAMES                          OPERATIONS
      7902 FORD                           OPERATIONS
      7934 MILLER                         OPERATIONS
 
48行が選択されました。

9. 結合条件の書き方

結合を行う際には、ON句を使って結合条件を指定します。通常は、2つのテーブルの共通の列を使って結合します。この共通の列は主キーや外部キーとして定義されていることが多いです。

SELECT 列名1, 列名2, ...
FROM テーブルA
INNER JOIN テーブルB ON テーブルA.共通の列 = テーブルB.共通の列;

この形式で結合条件を指定することで、効率的にデータを結合することができます。また、WHERE句を使ってさらに絞り込み条件を追加することも可能です。

10. 結合のポイントと注意点

  • 結合条件に注意：結合条件が適切でないと、正しい結果が得られません。特に、CROSS JOINは非常に多くの行を返す可能性があるため、使用には注意が必要です。
  • NULL値の扱い：外部結合では、NULL値が結果に含まれることがあります。これに対処するために、場合によってはCOALESCE関数などを使ってNULLを特定の値に置き換えるとよいでしょう。

SELECT EMP.EMPNO, EMP.ENAME, COALESCE(DEPT.DNAME, ‘未割り当て’) AS DNAME
FROM EMP
LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

このようにすることで、部署が割り当てられていない従業員については「未割り当て」という表示をすることができます。

[参考]
SQL言語リファレンス – 結合

これで、表の結合に関する基本的な構文と、その使い方について理解していただけたと思います。結合はデータベース操作において非常に重要な技術なので、実際のデータを使って練習することをおすすめします。次のステップとして、結合を使った複雑なクエリやサブクエリの使い方を学んでいくと、データベースの操作がさらに楽しくなります。

【初心者向け】SELECT文の基本
SELECT文の基本SELECT文の基本的な構文要素データベースは、多くの情報（データ）を効率的に管理し、必要な時に簡単に取り出せるようにするシステムです。データベースの中で最も重要な操作の一つが「SELECT文」です。これは、データベース...
oracle-master00000.com
2024.09.25

コメント

タイトルとURLをコピーしました