08-05.データー操作語（DML）【Join03--CROSS JOIN, INNER JOIN, NATURAL JOIN, USING, ON】

■CROSS JOIN
　-Cartesian Product値を検索する時に使用

SQL> SELECT ename FROM emp CROSS JOIN dept;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

.......

56行が選択されました。

SQL>

■INNER JOIN
　-結合時、「,」を省略しINNER JOINを追加し、WHERE句の代わりにON句を使用
　-INNERは省略可能
　-下記、2つの検索結果は同じ

--INNER JOIN使用例
SQL> SELECT e.empno, e.ename
  2  FROM dept d INNER JOIN emp e
  3  ON d.deptno = e.deptno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14行が選択されました。


--普通のSQL文
SQL> SELECt e.empno, e.ename
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14行が選択されました。

■NATURAL JOIN
　-Equi Joinと結果は同じ
　-２つのテーブルの同じ名前を持つコラムはすべて結合される
　-同一コラムを内部で検索するのでテーブルAliasを使うとエラーになる
　-同一コラムが２つ以上の場合、JOIN～USINGで結合されるコラムを制御できる
　-下記２つの結果は同じ

--NATURAL JOINを使用したSQL文
SQL> SELECT empno, ename, deptno
  2  FROM emp NATURAL JOIN dept;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。


--通常のSQL文
SQL> SELECT e.empno, e.ename, d.deptno
  2  FROM emp e, dept d
  3  WHERE e.deptno = d.deptno;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■JOIN～USING
　-USING文を使用するとNATURAL JOINでコラムを選択して結合できる
　-USING句内に含まれるコラムにAliasを指定するとエラーになる

--JOIN～USING使用例
SQL> SELECT e.empno, e.ename, deptno
  2  FROM emp e JOIN dept d USING(deptno);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■ON
　-結合条件を指定できる
　-すべての論理演算及びサブクエリを指定できる

--テストのため、scottユーザーで下記データーを追加
SQL> INSERT INTO bonus(ename, job, sal) VALUES('SMITH', 'CLERK', 500);

1行が作成されました。

--ON使用例（multi-table joins）
SQL> SELECT e.empno, e.ename, e.sal
  2  FROM emp e JOIN dept d ON (e.deptno = d.deptno)
  3             JOIN bonus b ON (b.ename = e.ename)
  4  WHERE e.sal IS NOT NULL;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

08-05.データー操作語（DML）【Join01--Equi Join, Non_Equi Join, Self Join】

■Joinとは？
　-２つ以上のテーブルを結合してデーターを検索する方法
　-通常２つ以上の行の共通値、Primary Key及びForeign Keyを利用して結合する
　-SELECT文でJoin結合するには最低１つのコラムがその２つのテーブルで共有されなければいけない

■Join方法と方式
　-方法：Equi Join（等結合、内部結合）、Nom-Equi Join、Self Join、Outer Join
　-方式：Nested Loop Join、Sort Merge Join、Hash Join

■Equi Join
　-一般的に使用されているEquality Condition（＝）による結合
　-Equi Joinの性能を上げるにはIndex機能を使用する

--deptテーブルとempテーブルを結合
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.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
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。


--INNER JOINを使用した結合
--「,」の代わりにINNER JOIN使用可能、INNERは省略可能。
　JOINの条件はON句に記載。

SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d
  3  INNER JOIN emp e
  4  ON d.deptno = e.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
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。



--NATURAL JOINでの結合
--NATURAL JOINは同一コラムを内部的にすべて結合するのでON句は省略可能
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d
  3  NATURAL JOIN emp e;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      RESEARCH
      7499 ALLEN      SALES
      7521 WARD       SALES
      7566 JONES      RESEARCH
      7654 MARTIN     SALES
      7698 BLAKE      SALES
      7782 CLARK      ACCOUNTING
      7788 SCOTT      RESEARCH
      7839 KING       ACCOUNTING
      7844 TURNER     SALES
      7876 ADAMS      RESEARCH

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      SALES
      7902 FORD       RESEARCH
      7934 MILLER     ACCOUNTING

14行が選択されました。



--JOIN～USINGを使用した結合
--NATURAL JOINはUSING文を使用することにより、コラムを選択し結合できる。
SQL> SELECT e.empno, e.ename, deptno
  2  FROM emp e
  3  JOIN dept d
  4  USING (deptno);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14行が選択されました。

■Non-Equi Join
　-テーブルのどのcolumnも結合するテーブルのcolumnと一致しない場合使用。
　-演算子は「=」以外を使う（BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN）
　-ほぼ使用しない。

--empテーブルとsalgradeテーブルのNon-Equi Join
SQL> SELECT e.ename, e.sal, s.grade
  2  FROM emp e, salgrade s
  3  WHERE e.sal
  4  BETWEEN s.losal
  5  AND s.hisal;

ENAME             SAL      GRADE
---------- ---------- ----------
SMITH             800          1
JAMES             950          1
ADAMS            1100          1
WARD             1250          2
MARTIN           1250          2
MILLER           1300          2
TURNER           1500          3
ALLEN            1600          3
CLARK            2450          4
BLAKE            2850          4
JONES            2975          4

ENAME             SAL      GRADE
---------- ---------- ----------
SCOTT            3000          4
FORD             3000          4
KING             5000          5

14行が選択されました。

SQL>

■Self Join
　-Equi Joinと同じだが、１つのテーブル内で結合するのが違い。
　-１つのテーブルに対して２つのaliasを使用し、FROM句に２つのテーブルを使用するように結合

--社員の担当マネージャを検索するSelf Join
SQL> SELECT e.ename, a.ename "Manager"
  2  FROM emp e, emp a
  3  WHERE e.empno = a.mgr;

ENAME      Manager
---------- ----------
FORD       SMITH
BLAKE      ALLEN
BLAKE      WARD
KING       JONES
BLAKE      MARTIN
KING       BLAKE
KING       CLARK
JONES      SCOTT
BLAKE      TURNER
SCOTT      ADAMS
BLAKE      JAMES

ENAME      Manager
---------- ----------
JONES      FORD
CLARK      MILLER

13行が選択されました。

SQL>

■Cartesian Product「直積結合」
　-AとB、２つのテーブルを結合する時、JOIN条件を省略しAテーブルの全ての行がBテーブルの全ての行と結合される事
　-可能な全ての行の結合が出力され、過負荷の危険もある
　-テーブルの数がNならCartesian Productを避けるためには最低N-1の等条件をSELECT文に入れ、各テーブルのコラムが最低一回は条件句に参照されなければいけない。
　-CROSS JOIN使用でCartesian Product値を得られる。

--CROSS JOINを使用してCartesian Product値を得る。
SQL> SELECT e.empno, e.ename, d.dname
  2  FROM dept d CROSS JOIN emp e;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7369 SMITH      ACCOUNTING
      7499 ALLEN      ACCOUNTING
      7521 WARD       ACCOUNTING
      7566 JONES      ACCOUNTING
      7654 MARTIN     ACCOUNTING
      7698 BLAKE      ACCOUNTING
      7782 CLARK      ACCOUNTING
      7788 SCOTT      ACCOUNTING
      7839 KING       ACCOUNTING
      7844 TURNER     ACCOUNTING
      7876 ADAMS      ACCOUNTING

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7900 JAMES      ACCOUNTING
      7902 FORD       ACCOUNTING
      7934 MILLER     ACCOUNTING
      7369 SMITH      RESEARCH
      7499 ALLEN      RESEARCH
      7521 WARD       RESEARCH
      7566 JONES      RESEARCH

.........

56行が選択されました。

SQL>