テーブル間の結合 - - Alibaba Cloud ドキュメントセンター

各クエリでは、1 つ以上のテーブルにアクセスできます。また、1 つ以上のテーブルの複数の行を同時に処理することもできます。このクエリは結合クエリと呼ばれます。たとえば、すべての従業員に関する情報と関連する部門の名前と住所をリストする場合は、emp テーブルの各行の deptno 列を dept 表のすべての行の deptno 列と比較し、これらの値が一致する行のペアを選択する必要があります。以下のクエリを使用して、この目的を達成することができます。

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno;

 ename  |   sal   | deptno |   dname    |   loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 2450.00 |     10 | ACCOUNTING | NEW YORK
 KING   | 5000.00 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000.00 |     20 | RESEARCH   | DALLAS
 JONES  | 2975.00 |     20 | RESEARCH   | DALLAS
 SMITH  |  800.00 |     20 | RESEARCH   | DALLAS
 ADAMS  | 1100.00 |     20 | RESEARCH   | DALLAS
 FORD   | 3000.00 |     20 | RESEARCH   | DALLAS
 WARD   | 1250.00 |     30 | SALES      | CHICAGO
 TURNER | 1500.00 |     30 | SALES      | CHICAGO
 ALLEN  | 1600.00 |     30 | SALES      | CHICAGO
 BLAKE  | 2850.00 |     30 | SALES      | CHICAGO
 MARTIN | 1250.00 |     30 | SALES      | CHICAGO
 JAMES  |  950.00 |     30 | SALES      | CHICAGO
(14 rows)

この結果セットに関しては、以下のコメントを理解する必要があります。

部門 40 に対応する結果行はありません。 emp テーブルのエントリは部門 40 に一致しないため、結合は dept テーブルの一致しない行を無視します。次のセクションでは、この問題を修正する方法について説明します。
以下のクエリを使用して、アスタリスク (*) を使用したり、修飾を省略したりする代わりに、テーブル名で修飾された出力列を一覧表示することを推奨します。
```
SELECT ename, sal, dept.deptno, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;
```

deptno 列は修飾する必要があります。他のすべての列には一意の名前があります。パーサーは、これらの列が属するテーブルを自動的に特定します。結合クエリでは列名を完全に修飾することを推奨します。

以下の構文に従って、結合クエリを作成することもできます。

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;

この構文で、以下のシナリオを理解できます。

前述の結合の結果では、部門 40 に属する従業員は返されず、部門 40 のエントリは生成されません。一致する従業員が存在しない結果から部門 40 のエントリを取得するには、クエリを使用して dept テーブルをスキャンし、一致する emp 行を見つけます。一致する行が見つからない場合は、NULL 値を使用して emp テーブルの列を置き換えることができます。このタイプのクエリは、外部結合と呼ばれます。ほとんどの結合は内部結合です。以下の例は、外部結合を示しています。

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno;

 ename  |   sal   | deptno |   dname    |   loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 2450.00 |     10 | ACCOUNTING | NEW YORK
 KING   | 5000.00 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000.00 |     20 | RESEARCH   | DALLAS
 JONES  | 2975.00 |     20 | RESEARCH   | DALLAS
 SMITH  |  800.00 |     20 | RESEARCH   | DALLAS
 ADAMS  | 1100.00 |     20 | RESEARCH   | DALLAS
 FORD   | 3000.00 |     20 | RESEARCH   | DALLAS
 WARD   | 1250.00 |     30 | SALES      | CHICAGO
 TURNER | 1500.00 |     30 | SALES      | CHICAGO
 ALLEN  | 1600.00 |     30 | SALES      | CHICAGO
 BLAKE  | 2850.00 |     30 | SALES      | CHICAGO
 MARTIN | 1250.00 |     30 | SALES      | CHICAGO
 JAMES  |  950.00 |     30 | SALES      | CHICAGO
        |         |     40 | OPERATIONS | BOSTON
(15 rows)

このクエリは、左外部結合と呼ばれます。結合演算子の左側に記載されているテーブルには、テーブルの各行が少なくとも 1 回は出力に表示されます。右側のテーブルには、左側のテーブルの一部の行と一致する行のみが出力に表示されます。左テーブルの行が右テーブルのどの行とも一致しない場合は、NULL 値を使用して右テーブルの列が置き換えられます。

外部結合の代替構文として、WHERE 句内の結合条件で外部結合演算子 "(+)" を使用することができます。外部結合演算子は、一致しない行を置き換えるために NULL 値が使用されるテーブルの列名の後に配置されます。 emp テーブルに一致する行がない dept テーブルのすべての行について、Oracle と互換性のある PolarDB データベースは、emp の列を含む選択リスト式に対して NULL を返します。したがって、次の方法でクエリを書き換えることができます。

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno;

 ename | sal | deptno | dname | loc
--------+---------+--------+------------+----------
 MILLER | 1300.00 | 10 | ACCOUNTING | NEW YORK
 CLARK | 2450.00 | 10 | ACCOUNTING | NEW YORK
 KING | 5000.00 | 10 | ACCOUNTING | NEW YORK
 SCOTT | 3000.00 | 20 | RESEARCH | DALLAS
 JONES | 2975.00 | 20 | RESEARCH | DALLAS
 SMITH | 800.00 | 20 | RESEARCH | DALLAS
 ADAMS | 1100.00 | 20 | RESEARCH | DALLAS
 FORD | 3000.00 | 20 | RESEARCH | DALLAS
 WARD | 1250.00 | 30 | SALES | CHICAGO
 TURNER | 1500.00 | 30 | SALES | CHICAGO
 ALLEN | 1600.00 | 30 | SALES | CHICAGO
 BLAKE | 2850.00 | 30 | SALES | CHICAGO
 MARTIN | 1250.00 | 30 | SALES | CHICAGO
 JAMES | 950.00 | 30 | SALES | CHICAGO
        | | 40 | OPERATIONS | BOSTON
(15 rows)

テーブルをそれ自体と結合することもできます。この結合は自己結合です。たとえば、従業員の名前とその管理者の名前を検索する場合は、各 emp 行の mgr 列を他のすべての emp 行の empno 列と比較します。

SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

 Employees and their Managers
------------------------------
 FORD works for JONES
 SCOTT works for JONES
 WARD works for BLAKE
 TURNER works for BLAKE
 MARTIN works for BLAKE
 JAMES works for BLAKE
 ALLEN works for BLAKE
 MILLER works for CLARK
 ADAMS works for SCOTT
 CLARK works for KING
 BLAKE works for KING
 JONES works for KING
 SMITH works for FORD
(13 rows)

この例では、emp テーブルは、選択リストと結合条件で従業員行を表す e1 として、選択リストと結合条件で一致するマネージャー行を表す e2 としてラベルが付けられています。これらのタイプのエイリアスを他のクエリで使用して、入力を減らすことができます。以下の例では、これらのタイプのエイリアスを使用しています。

SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno;

 ename  | mgr  | deptno |   dname    |   loc
--------+------+--------+------------+----------
 MILLER | 7782 |     10 | ACCOUNTING | NEW YORK
 CLARK  | 7839 |     10 | ACCOUNTING | NEW YORK
 KING   |      |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 7566 |     20 | RESEARCH   | DALLAS
 JONES  | 7839 |     20 | RESEARCH   | DALLAS
 SMITH  | 7902 |     20 | RESEARCH   | DALLAS
 ADAMS  | 7788 |     20 | RESEARCH   | DALLAS
 FORD   | 7566 |     20 | RESEARCH   | DALLAS
 WARD   | 7698 |     30 | SALES      | CHICAGO
 TURNER | 7698 |     30 | SALES      | CHICAGO
 ALLEN  | 7698 |     30 | SALES      | CHICAGO
 BLAKE  | 7839 |     30 | SALES      | CHICAGO
 MARTIN | 7698 |     30 | SALES      | CHICAGO
 JAMES  | 7698 |     30 | SALES      | CHICAGO
(14 rows)

これは一般的な省略形です。