クエリ内の1つ以上のテーブルから複数の行を同時に処理できます。 このようなクエリは結合クエリと呼ばれます。
たとえば、A sample databaseで作成されたサンプルデータベースに基づいて、すべての従業員に関する情報と、関連する部署の名前と場所を照会するとします。 empテーブルの各行のdeptno列をdeptテーブルのすべての行のdeptno列と比較し、値が一致する行のペアを照会する必要があります。 この目的を達成するには、次のステートメントを実行します。
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY 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)以下の点にご注意ください。
アスタリスク (*) を使用したり、修飾を省略したりする代わりに、次のステートメントを実行して、テーブル名で修飾された出力列を指定することをお勧めします。
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 ORDER BY deptno;この構文で、以下のシナリオを理解できます。
部門40に対応する結果行はありません。 参加は、部門40に一致するempテーブルのエントリがないため、部門テーブルの一致しない行を無視します。 次のセクションでは、この問題を修正する方法について説明します。
ステートメントを実行して、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 ORDER BY 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)このタイプのクエリは、左外部結合と呼ばれます。 join演算子の左側のテーブルでは、テーブルの各行が少なくとも1回出力に表示されます。 右側のテーブルには、左側のテーブルの特定の行と一致する行のみが出力に表示されます。 左側のテーブルの行が右側のテーブルのどの行とも一致しない場合、NULL値を使用して、右側のテーブルの一致しない列の値を置き換えます。
外部結合の代替構文として、WHERE句内の結合条件で外部結合演算子 (+) を使用できます。 外部結合演算子は、一致しない行を置き換えるためにNULL値が使用されるテーブルの列名の後に配置されます。 empテーブル内の行が一致しないdeptテーブル内のすべての行に対して、PolarDB For PostgreSQL (Compatible with Oracle) は、empテーブルの列を含むSELECTリスト内のすべての式に対してNULLを返します。 したがって、次の方法で上記のステートメントを書き直すことができます。
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno ORDER BY 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テーブルは、SELECTリストおよび結合条件の従業員行を表すe1としてエイリアスされ、SELECTリストおよび結合条件の一致したマネージャー行を表すe2としてエイリアスされます。 これらの種類のエイリアスは、ステートメントを単純化するために使用できます。 例：
SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY 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)