Oracle SQL - JOIN

SQL 문법 기본 순서

SELECT

FROM
WHERE

ORDER BY

 

---

Oracle SQL - JOIN

JOIN

둘 이상의 테이블을 합쳐 하나의 큰 테이블로 만드는 방법
필요성

• 관계형 모델에서는 데이터의 일관성이나 효율을 위하여 데이터의 중복을 최소화 (정규화)
• Foreign Key를 이용하여 참조
• 정규화 된 테이블로부터 결합된 형태의 정보를 추출할 필요가 있음
• 예) 직원의 이름과 직원이 속한 부서명을 함께 보고 싶다면?

카티젼 프로덕트

  - 두 테이블에서 그냥 결과를 선택한다면...

• SELECT ename, dname from emp, dept
• 결과 : 두 테이블 행들의 가능한 모든 쌍이 추출
• 일반적으로 사용자가 원하는 결과가 아님

  - Cartesian Product

Cartesian Product : 조합 가능한 모든 레코드 출력

  - Cartesian Product를 막기 위해서는 올바른 JOIN 조건을 WHERE 절에 부여해야 함

  - 양쪽 테이블로부터 조합 가능한 모든 쌍이 선택되기 때문에 Cross Join이라 불리기도 함

-- 두 테이블로 부터 모든 레코드를 추출 : Cartision product or Cross join
SELECT first_name, emp.department_id, dpt.department_id, department_name
FROM employees emp, departments dpt
ORDER by first_name;

 

Simple Join

 

SELECT t1.col1, t1.col2, t2.col1 …
FROM Table1 t1, Table2 t2
WHERE t1.col3 = t2.col3

• FROM 절에 필요한 테이블을 모두 적는다.
• 컬럼 이름의 모호성 (어느 테이블에 속하는지 불명확) 을 피하기 위해 Table 명에 alias를 사용 (테이블 이름으로 직접 지칭도 가능)
• 적절한 Join 조건을 WHERE 절에 부여 (일반적으로 테이블 개수 -1 개의 조인 조건이 필요)
• 일반적으로 PK와 FK간의 = 조건이 붙는 경우가 많음

Join의 처리

Flow

-- 테이블 조인을 위한 조건을 부여할 수 있다.
SELECT first_name, emp.department_id, dpt.department_id, department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;

Join의 종류

용어

• Cross Join (Cartesian Product) : 모든 가능한 쌍이 나타남
• Inner Join : Join 조건을 만족하는 튜플만 나타남
  • Theta Join : 조건(Theta)에 의한 조인
  • Equi-Join : Theta Join & 조건이 Equal (=)
  • Natural Join : Equi-Join & 동일한 컬럼명 합쳐짐
• Outer Join : 조건을 만족하지 않는 튜플(짝이 없는 튜플)도 null과 함께 나타남
• Self Join : 자기 자신과 조인 ( 관리자 : 관리자 ID & 사원 ID 동시보유)

SQL:1999 Syntax (Oracle 9i)

FROM 절에서 바로 Join을 명시적으로 정의

SELECT table1.column, table2.column
	FROM table1
		[CROSS JOIN table2] |
		[NATURAL JOIN table2] |
		[JOIN table2 USING (column_name)] |
		[JOIN table2
			ON(table1.column_name = table2.column_name)] |
		[LEFT|RIGHT|FULL OUTER JOIN table2
			ON (table1.column_name = table2.column_name)];

EX)

SELECT * FROM emp JOIN dept USING (deptno);
SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;
SELECT * FROM emp RIGHT OUTER JOIN dept ON (emp.deptno = dept.deptno);

on <- join의 WHERE절

 

Equi-Join

[연습] hr.employees and hr.departments
employees와 departments를 department_id를 기준으로 Join 하여 first_name, department_id, department_name을 출력해 봅시다

  - 총 몇 건의 ROW가 검색되는지 확인해 봅시다
     - null은 조인되지 않음을 확인합니다.
     - 부서를 배정받지 못한 사원(department_id 가 NULL) 은 누구인지 확인해 봅시다

-- 총 몇명의 사원이 있는가?
SELECT count(*) FROM employees;  -- 107명

SELECT first_name, emp.department_id, department_name
FROM employees emp , departments dpt
WHERE emp.department_id = dpt.department_id;  -- 106명

-- department_id 가 NULL인 사원 확인
SELECT * FROM employees
WHERE department_id IS NULL;

Theta Join

정의

• 임의의 조건을 Join 조건으로 사용
• Non-Equi Join이라고도 함
• Equal(=) 이외의 연산자를 사용하여 Join Condition을 작성한 경우를 일컬음

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal

Outer Join

정의
    - Join 조건을 만족하지 않는(짝이 없는) 튜플의 경우 Null 을 포함하여 결과를 생성
    - 모든 행이 결과 테이블에 참여
종류
    - Left Outer Join : 왼쪽의 모든 튜플은 결과 테이블에 나타남

    - Right Outer Join : 오른쪽의 모든 튜플은 결과 테이블에 나타남
    - Full Outer Join : 양쪽 모두 결과 테이블에 참여
표현 방법
    - NULL이 올 수 있는 쪽 조건에 (+)를 붙인다
       - 어느 쪽에 붙이느냐에 따라 의미가 변하므로 올바른 위치에 붙여야 한다

Left Outer Join

왼쪽 테이블의 모든 row를 결과 테이블에 나타냄

 

ANSI SQL의 예 (표준)

SELECT e.department_id, e.first_name, d.department_name
	FROM employees e LEFT OUTER JOIN departments d
 	 ON e.department_id = d.department_id ;

> LEFT는 employees 를 의미한다.

 

Oracle SQL의 예

SELECT e.department_id, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) ;

Right Outer Join

오른쪽 테이블의 모든 row를 결과 테이블에 나타냄

ANSI SQL의 예 (표준)

SELECT e.department_id, e.first_name, d.department_name
	FROM employees e RIGHT OUTER JOIN departments d
 	 ON e.department_id = d.department_id ;

Oracle SQL의 예

SELECT e.department_id, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;

Full Outer Join

ANSI SQL의 예 (표준)

SELECT e.department_id, e.first_name, d.department_name
	FROM employees e FULL OUTER JOIN departments d
 	 ON e.department_id = d.department_id ;

 

Self Join

정의
   - 자기 자신과 Join
   - 동일한 테이블 명이 2번 이상 사용되므로 Alias를 사용할 수밖에 없음

SELECT *
FROM EMP E1, EMP E2
WHERE E1.EMPNO = E2.MGR