====== 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