Understanding Join Conditions and Join Types

Equi joins

조건절 Equality Condition( = )에 의하여 조인이 이루 집니다.
Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋습니다.

SQL>SELECT e.ename, d.dname 
    FROM emp e , dept d 
    WHERE e.deptno = d.deptno; 

- WHERE 절에 조인 조건을 작성하고 column명 앞에 테이블명을 적습니다.

Non-equijoins

Non-equijoin은 테이블의 어떤 column도 join할 테이블의 column에 일치하지 않을 때 사용하고 조인조건은 동등( = )이외의 연산자를 갖습니다.

'(BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN)'

SQL>SELECT e.ename, d.dname 
    FROM emp e, dept d 
    WHERE  e.sal  BETWEEN 3000 AND 4000; 

ENAME                DNAME 
----------------- -----------
SCOTT               ACCOUNTING
FORD                 ACCOUNTING
SCOTT               RESEARCH
FORD                 RESEARCH

Inner joins

일반 조인시 ,를 생략하고 INNER JOIN을 추가하고 WHERE절대신 ON절을 사용해야 합니다.
INNER는 생략 가능합니다.
아래의 2개의 쿼리의 결과는 동일하다.

--  INNER JOIN을 사용한 문장
SQL> SELECT e.empno, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno;
 
-- 일반적인 SQL 문장
SQL> SELECT e.empno, e.ename FROM dept d , emp e 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
7900	JAMES
7902	FORD
7934	MILLER
(14 rows)

Outer joins

equijoin 문장들의 한가지 제약점은 그것들이 조인을 생성하려 하는 두 개의 테이블의 두 개 컬럼에서 공통된 값이 없다면 테이블로부터 테이터를 Return하지 않는 다는 것입니다.
정상적으로 조인 조건을 만족하지 못하는 행들을 보기위해 outer join을 사용합니다.
Outer join 연산자 “( + )“입니다.
조인시킬 값이 없는 조인측에 ”( + )“를 위치 시킵니다.
Outer join 연산자는 표현식의 한 편에만 올 수 있습니다.
Outer join은 IN 연산자를 사용할 수 없고 OR 연산자에 의해 다른 하나의 조건에 연결될 수 없습니다.
Oracle9i 부터는 ANSI/ISO SQL표준인 'LEFT OUTER JOIN' , 'RIGHT OUTER JOIN', 'FULL OUTER JOIN'를 지원 합니다.
'OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.'

예제1) 일반 조인의 경우 

SQL> SELECT DISTINCT(a.deptno), b.deptno
     FROM emp a, dept b
     WHERE  a.deptno = b.deptno

DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30


예제2) out join을 했을 경우

SQL> SELECT DISTINCT(a.deptno), b.deptno
     FROM emp a, dept b
     WHERE  a.deptno(+) = b.deptno

 DEPTNO     DEPTNO
 -------     ----------
     10         10
     20         20
     30         30
                40

※ 다음의 쿼리를 한번 잘 보시기 바랍니다. 

SQL> SELECT DISTINCT(a.deptno), b.deptno
     FROM emp a, dept b
     WHERE a.deptno(+) = b.deptno
           AND a.ename LIKE '%%%';

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30

쿼리 결과를 잘 보면 out조인이 되지 않은 것을 알 수 있습니다.
위 쿼리를 out조인이 되기 위해서는 아래와 같이 고쳐야 합니다. 

SQL> SELECT DISTINCT(a.deptno), b.deptno
     FROM emp a, dept b
     WHERE a.deptno(+) = b.deptno
           AND a.ename(+) LIKE '%%%'

    DEPTNO     DEPTNO
---------- ----------
        10         10
        20         20
        30         30
                   40

OUT조인 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 똑같이 OUT조인 연산자인 (+)를 해주어야 합니다.

Data

SQL> select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
-------------------------------------------------------------------------
7369	SMITH	CLERK	7902	1980-12-17	800		20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975		20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850		30
7782	CLARK	MANAGER	7839	1981-06-09	2450		10
7788	SCOTT	ANALYST	7566	1987-04-19	3000		20
7839	KING	PRESIDENT		1981-11-17	5000		10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100		20
7900	JAMES	CLERK	7698	1981-12-03	950		30
7902	FORD	ANALYST	7566	1981-12-03	3000		20
7934	MILLER	CLERK	7782	1982-01-23	1300		10
(14 rows)

SQL> create table empinfo as  
     select deptno,count(1) as count from emp group by deptno;

SQL> insert into empinfo values (50,1);

SQL> select * from empinfo;

DEPTNO	COUNT
--------------
30	6
20	5
10	3
50	1
(4 rows)

SQL> select * from dept;

DEPTNO	DNAME	        LOC
--------------------------------
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	        CHICAGO
40	OPERATIONS	BOSTON
(4 rows)

LEFT OUTER JOIN

1. 왼쪽 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.

SQL> SELECT e.deptno, d.deptno 
     FROM empinfo e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

DEPTNO	DEPTNO_1
----------------
30	30
20	20
10	10
50

RIGHT OUTER JOIN

오른쪽에 테이블에 조인시킬 컬럽의 값이 없는 경우 사용합니다.

SQL> SELECT e.deptno, d.deptno 
     FROM empinfo e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

DEPTNO	DEPTNO_1
----------------
30	30
20	20
10	10
	40

FULL OUTER JOIN

양쪽 테이블에 다 outer join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 합니다.

SQL> SELECT e.deptno, d.deptno 
     FROM empinfo e FULL OUTER JOIN dept d ON e.deptno = d.deptno;

DEPTNO	DEPTNO_1
----------------
30	30
20	20
10	10
50	
	40

Self joins

Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다름니다.
같은 테이블에 대해 두 개의 alias를 작성함으로 FROM절에 두 개의 테이블을 사용하는 것과 같이 합니다.

SQL> SELECT concat(a.ename,'  ') ||' : \'|| b.sal  급여
      FROM emp a, emp b
     WHERE a.empno = b.empno;

급여
-------------------
SMITH   : \800
ALLEN   : \1600
WARD   : \1250
JONES   : \2975
MARTIN   : \1250
BLAKE   : \2850
CLARK   : \2450
SCOTT   : \3000 

이름과 급여를 연결시켜서 보여줍니다.

CROSS JOIN

Cartesian product 값을 얻을때 사용 합니다.

SQL> SELECT ename FROM emp CROSS JOIN dept order by ename;

ENAME
------
ADAMS
ADAMS
ADAMS
ADAMS
ALLEN
ALLEN
ALLEN
ALLEN
BLAKE
BLAKE
BLAKE
BLAKE
CLARK
CLARK
CLARK
CLARK
FORD
FORD
FORD
FORD
JAMES
JAMES
JAMES
JAMES
JONES
JONES
JONES
JONES
KING
KING
KING
KING
MARTIN
MARTIN
MARTIN
MARTIN
MILLER
MILLER
MILLER
MILLER
SCOTT
SCOTT
SCOTT
SCOTT
SMITH
SMITH
SMITH
SMITH
TURNER
TURNER
TURNER
TURNER
WARD
WARD
WARD
WARD
(56 rows)

NATURAL JOIN

Equijoin과 동일하다고 보시면 됩니다.
두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 됩니다.
동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 합니다.
동일한 컬럼이 두개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어 할 수 있습니다.
아래의 두 SQL 문장을 비교해 보세요.(동일한 결과를 얻을 수 있습니다)

-- NATURAL JOIN을 사용한 SQL 문장.
SQL> SELECT empno, ename, deptno FROM emp NATURAL JOIN dept;
 
-- Oracle9i 이전에 일반적인 SQL 문장
SQL> SELECT e.empno, e.ename, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno;

JOIN ~ USING

NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되었는데 USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있습니다.
USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 합니다.

-- 일반적인 사용 방법
SQL> SELECT e.empno, e.ename, deptno FROM emp e JOIN dept d USING(deptno)

ON 구문

조인 조건을 지정 할 수 있습니다.
모든 논리 연산 및 서브쿼리를 지정할 수 있습니다.

-- 테스트를 위해 scott유저에서 아래 insert문장을 실행시켜 주세요.
SQL> INSERT INTO bonus(ename, job, sal) VALUES('SMITH','CLERK',500);
 
-- ON절 사용 예제 (multi-table joins)
SQL> SELECT e.empno, e.ename, e.sal
     FROM emp e JOIN dept d ON (e.deptno=d.deptno)
                JOIN bonus b ON (b.ename = e.ename)
     WHERE e.sal IS NOT NULL;

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

목차

Understanding Join Conditions and Join Types

Equi joins

Non-equijoins

Inner joins

Outer joins

Data

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Self joins

CROSS JOIN

NATURAL JOIN

JOIN ~ USING

ON 구문