SQL- JOIN

본 포스트는 그루비에 있는 Oracle SQL Basic 요약본을 보고 정리한 내용임

조인(JOIN)

조인(Join)이란?

• 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
• 보통 둘 이상의 행들의 공통된값 Primary Key 및 Foreign Key 값을 사용하여 조인
• 두 개의 테이블을 SELECT 문장 안에서 조인하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유되어야함

Join 방법과 방식

• 조인의 방법:Equi Join(등가 조인, 내부 조인), Non-Equi Join, Self Join, Outer Join
• 조인의 방식 :Nested Loop Join, Sort Merge Join, Hash Join

Equi Join

• 가장 일반적으로 사용하는 Equality Condition(=)에 의한 조인이다.
• Equi join의 성능을 높이기 위해서는 Index의 기능을 사용하는 것이 좋다

--- dept 테이블과 emp 테이블을 조인하는 예제
SELECT e.empno, e.ename,d.dname
  FROM dept d, emp e
 WHERE d.deptno =e.deptno

콤마(,)대신 INNER JOIN을 사용할 수 있으며, INNER는 생략가능,JOIN조건은 ON절에 옴

--INNER JOIN절을 이용하여 조인하는 예제
SELECT e.empno, e.ename, d.dname
  FROM dept d
  INNER JOIN emp e
  		ON d.deptno = e.deptno

NATURAL JOIN을 사용하면 동일한 컬럼을 내부적으로 모두 조인하므로 ,ON절이 생략가능

--- JOIN~USING절을 이용하여 조인하는예제
SELECT e.empno, e.ename,deptno
  FROM emp e
  JOIN dept d
 USING (deptno);

NON-Equi JOIN

• 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을때 사용
  - 조인 조건은 동등(=)이외의 연산자를 갖는다
• BETWEEN AND,IS NULL,IS NOT NULL,IN, NOT IN
• 거의 사용하지 않음

---emp 테이블과 slagrade 테이블의 Non-Equi Join 예제
SELECT e.ename, e.sal, s.grade
 FROM emp e, salgrade s
 WHERE e.sal
 BETWEEN s.losal
   AND s.hisal;
   
ENAME             SAL      GRADE 
---------- ---------- ---------- 
SMITH             800          1 
JAMES             950          1 
ADAMS            1100          1 
...   
   

SELF JOIN

• Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다
• 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인

---사원의 매니저명을 조회하는 Self Join 예제
SELECT e.ename, a.ename ="MANAGER"
  FROM emp e ,emp a
  WHERE e.empno =a.mgr;
  
ENAME     Manager 
------- ---------- 
FORD      SMITH 
BLAKE     ALLEN 
BLAKE     WARD 
KING      JONES 
...  

Cartesian Product

• 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블의 모든 데이터가 반환되는 현상
• Cartesian Product는 조인조건을 정의하지 않은 경우 발생
• 테이블의 개수가 N이라면 Cartesian Product를 피하기 위해서는 적어도 N-1개의 등가 조건을 SELECT문안에 포함시켜야 하며 각 테이블의 컬럼이 적어도 한번은 조건절에 참조되도록해야함
• CROSS JOIN을 이용하면 Cartesian Product값을 얻을수 있다.

--- CROSS JOIN절을 이용하여 Cartesian Product값을 얻는 예제
SELECT e.empno, e.ename, d.dname
  FROM dept d CROSS JOIN emp e;

Outer Join(LEFT,RIGHT,FULL OUTER JOIN)

Outer Join이란?

• Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 데이터를 반환하지 못함
• 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용함
• Outer Join의 연산자는 "(+)"이다
• 조인시 값이 없는 조인측에 "(+)"를 위치 시킴
• Outer Join연산자는 표현식의 한편에만 올수있다.

Outer Join 예제

Equi Join과 Outer Join의 비교

--- Equi Join으로 부서 번호를 조회하는 예제
SELECT DISTINCT (e.deptno), d.deptno, d.dname
   FROM emp e,dept d
   WHERE e.deptno = d.deptno
DEPTNO     DEPTNO 
------ ---------- 
    10         10 
    20         20 
    30         30
    
--- Outer Join으로 부서 번호를 조회하는 예제
SELECT DISTINCT (e.deptno), d.deptno
  FROM emp e , dept d
 WHERE e.deptno(+) = d.deptno;
 DEPTNO  DEPTNO 
 ------- -------- 
     10       10 
     20       20 
     30       30 
              40

Outer Join을 사용하는 테이블에 추가로 조건절이 있다면 (+)연산자를 모두 해야 함

LEFT,RIGHT,FULL Outer Join

Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN,RIGHT OUTER JOIN, FULL OUTER JOIN을 지원

LEFT OUTER JOIN

LEFT OUTER JOIN은 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용

RIGHT OUTER JOIN

RIGHT OUTER JOIN은 왼쪽 테이블에 조인시킬 컬럼의 값이 없는 경우 사용

FULL OUTER JOIN

FULL OUTER JOIN은 양쪽 테이블 모두 Outer Join 걸어야 하는 경우에 사용

CROSS,INNER,NATURAL JOIN,USING ON

CROSS JOIN

Cartesian Product값을 얻을때 사용

INNER JOIN

• 일반조인시,(콤마)를 생략하고 INNER JOIN을 추가하고, WHERE절 대신 ON절을 사용하면됨
• INNER는 생략가능
• 아래의 두 조인의 결과값은 동일

---일반적인 SQL문장
SQL> SELECT e.empno, e.ename
	  FROM dept d, emp e
      WHERE d.deptno=e.deptno
      
--INNER JOIN SQL문장
SQL> SELECT e.empno, e.ename
	  FROM dept d INNER JOIN emp e
        ON d.deptno =e.deptno

NATURAL JOIN

• Equi Join과 동일하다고 보면됨
• 두 테이블의 동일한 이름을 가지는 컬럼은 모두 조인됨
• 동일한 컬럼을 내부적으로 찾게됨-> 테이블 Alias를 주면 오류발생
• 동일한 컬럼이 두개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어할 수 있음
• 아래의 두 조인의 결과 값은 같음

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

JOIN ~ USING

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

---일반적인 사용방법
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 bouus b ON (b.ename=e.ename)
      WHERE e.sal IS NOT NULL