Oracle Database
ジョイン
サーチ…
クロスジョイン
CROSS JOINは、明示的な結合句を使用しない2つの表間のCROSS JOIN実行し、2つの表のデカルト積になります。デカルト積は、1つの表の各行が結合の2番目の表の各行と結合されることを意味します。たとえば、 TABLEAに20行、 TABLEBに20行がある場合、結果は20*20 = 400出力行になります。
例：
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
これは以下のように書くこともできます：
SELECT *
FROM TABLEA, TABLEB;
次に、2つのテーブル間のSQLでのクロスジョインの例を示します。
サンプルテーブル： TABLEA
+-------+---------+
| VALUE | NAME |
+-------+---------+
| 1 | ONE |
| 2 | TWO |
+-------+---------+
サンプルテーブル： TABLEB
+-------+--------+
| VALUE | NAME |
+-------+--------+
| 3 | THREE |
| 4 | FOUR |
+-------+--------+
さて、クエリを実行すると：
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
出力：
+-------+--------+-------+--------+
| VALUE | NAME | VALUE | NAME |
+-------+--------+-------+--------+
| 1 | ONE | 3 | THREE |
| 1 | ONE | 4 | FOUR |
| 2 | TWO | 3 | THREE |
| 2 | TWO | 4 | FOUR |
+-------+--------+-------+--------+
これは、2つのテーブル間でクロス結合が行われる方法です。 
クロス・ジョインの詳細： Oracleのドキュメント
INNER JOIN
INNER JOINは、明示的な結合句を指定できるJOIN操作です。
構文
TableExpression [INNER] JOINテーブル式{ON booleanExpression | USING節}
ブール式でONを指定して、結合句を指定することができます。
ON句の式の範囲には、現行の表と、現行のSELECTに対する外部問合せブロック内の表が含まれます。次の例では、ON句は現在のテーブルを参照します。
-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Join the EMPLOYEE and DEPARTMENT tables,
-- select the employee number (EMPNO),
-- employee surname (LASTNAME),
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table)
-- and department name (DEPTNAME)
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT
ON WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE) < 1930
-- Another example of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an
-- alternate form of a fullselect).
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1
-- This results in:
-- C1 |C2 |C1 |2
-- -----------------------------------------------
-- 3 |4 |3 |2
-- 1 |5 |1 |2
-- List every department with the employee number and
-- last name of the manager
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT INNER JOIN EMPLOYEE
ON MGRNO = EMPNO
-- List every employee number and last name
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E INNER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO
LEFT OUTER JOIN
LEFT OUTER JOINは、明示的な結合句を必要とするが、一致しない行を最初の表から除外しない2つの表間のLEFT OUTER JOIN実行します。
例：
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
ANSI構文が推奨される方法であっても、非常に頻繁に従来の構文に遭遇する可能性があります。ある条件内で(+)を使用すると、方程式のどちら側が外側とみなされるかが決まります。
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP,
SCOTT.DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO(+);
次に、2つのテーブル間の左外部結合の例を示します。
サンプルテーブル： EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
サンプルテーブル： DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
さて、クエリを実行すると：
SELECT
*
FROM
EMPLOYEE LEFT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
出力：
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| F | 1 | 1 | ACCOUNTING |
| E | 1 | 1 | ACCOUNTING |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| A | 2 | 2 | FINANCE |
| C | 3 | | |
| H | 4 | | |
| G | 4 | | |
+-----------+---------+---------+--------------+
右外側のジョイン
RIGHT OUTER JOINは、明示的な結合句を必要とするが、2番目の表から一致しない行を除外しない2つの表間のRIGHT OUTER JOIN実行します。
例：
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
SCOTT.DEPTしない行は含まれていますが、 SCOTT.EMPのSCOTT.DEPTしない行は含まれていないため、上記はLEFT OUTER JOINを使用する次の文と同じです。
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
次に、2つのテーブル間の右外部結合の例を示します。
サンプルテーブル： EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
サンプルテーブル： DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
さて、クエリを実行すると：
SELECT
*
FROM
EMPLOYEE RIGHT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
出力：
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| | | 5 | MARKETING |
| | | 6 | HR |
+-----------+---------+---------+--------------+
クエリに相当するOracle（+）構文は次のとおりです。
SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;
フル・アウター・ジョイン
FULL OUTER JOINは、明示的な結合句を必要とするが、どちらの表でも一致しない行を除外しない2つの表間のFULL OUTER JOIN実行します。つまり、各テーブルのすべての行を返します。
例：
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
次に、2つのテーブル間の完全外部結合の例を示します。
サンプルテーブル： EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
サンプルテーブル： DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
さて、クエリを実行すると：
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
出力
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| C | 3 | | |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| G | 4 | | |
| H | 4 | | |
| | | 6 | HR |
| | | 5 | MARKETING |
+-----------+---------+---------+--------------+
ここでは、一致しない列はNULLになっています。
アンティン
antijoinは、述部の右側から対応する行がない述部の左側から行を戻します。右側のサブクエリと一致しない（NOT IN）行を返します。
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
次に、2つのテーブル間のアンチ結合の例を示します。
サンプルテーブル： EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
サンプルテーブル： DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
さて、クエリを実行すると：
SELECT
*
FROM
EMPLOYEE WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM DEPT);
出力：
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| C | 3 |
| H | 4 |
| G | 4 |
+-----------+---------+
出力には、DEPT表にDEPTNOが存在しなかったEMPLOYEE表の行のみが表示されます。
SEMIJOIN
例えば、Semijoinクエリを使用して、給与が2500を超える従業員が少なくとも1人いるすべての部署を見つけることができます。
SELECT * FROM departments
WHERE EXISTS
(SELECT 1 FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
これは、従業員の内部結合として給与が2500を超える必要があることを詳述するwhere句を与えることで、同じ部門を何回も返すことができるため、完全な結合の選択肢よりも効率的です。消防署に給与3000人のn人の従業員がいれば、部署select * from departments, employeesし、idsに必要な参加select * from departments, employeesとwhere句で消防署をn回返すとします。
ジョイン
JOIN操作は、2つの表の間のJOINを実行します。最初の表の一致しない行は除きます。 Oracle 9iから、 JOINはINNER JOIN機能的に同等です。この操作には、 CROSS JOINおよびNATURAL JOIN演算子とは対照的に、明示的な結合句が必要です。
例：
select t1.*,
t2.DeptId
from table_1 t1
join table_2 t2 on t2.DeptNo = t1.DeptNo
Oracleのマニュアル：
NATURAL JOIN
NATURAL JOINでは、明示的結合条件は必要ありません。結合されたテーブルに同じ名前のすべてのフィールドに基づいて1つを構築します。
create table tab1(id number, descr varchar2(100));
create table tab2(id number, descr varchar2(100));
insert into tab1 values(1, 'one');
insert into tab1 values(2, 'two');
insert into tab1 values(3, 'three');
insert into tab2 values(1, 'ONE');
insert into tab2 values(3, 'three');
結合は両方のテーブルに共通のフィールドIDとDESCRで行われます。
SQL> select *
2 from tab1
3 natural join
4 tab2;
ID DESCR
---------- ----------
3 three
異なる名前の列は、JOIN条件では使用されません。
SQL> select *
2 from (select id as id, descr as descr1 from tab1)
3 natural join
4 (select id as id, descr as descr2 from tab2);
ID DESCR1 DESCR2
---------- ---------- ----------
1 one ONE
3 three three
結合された表に共通の列がない場合、条件のないJOINが実行されます。
SQL> select *
2 from (select id as id1, descr as descr1 from tab1)
3 natural join
4 (select id as id2, descr as descr2 from tab2);
ID1 DESCR1 ID2 DESCR2
---------- ---------- ---------- ----------
1 one 1 ONE
2 two 1 ONE
3 three 1 ONE
1 one 3 three
2 two 3 three
3 three 3 three
