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