[SQL] JOIN

조인의 개념

• 하나의 SELECT 문 안에서 여러 테으블로부터의 데이터를 사용하는 것

정규화

1. 관계형 데이터베이스는 중복을 최소화하고 갱신 작업의 이상현상을 제거하기 위하여 설계 시 수행하는 과정
2. 정규화의 결과 테이블에는 필요한 최소한의 정보를 저장하게 되어 보고서를 생성하려면 여러 테이블의 데이터를 사용해야 하는 경우 발생 ➡ 조인 필요

조인의 종류

1. 조인이 되는 두 테이블의 연관되는 데이터의 비교 방식
   1. 등가 조인 (Equi Join)
   2. 비등가 조인 (Non Equi Join)
2. 조인의 결과에 따른 분류
   1. Inner Join
   2. Outer  Join (Inner Join 결과에 제외된 행들도 포함해서 표시)
3. 조인에 사용되는 테이블 수에 의한 분류
   1. Self Join
   2. 3 Way Join

ANSI/ISO  SQL 표준 조인 구문

1. FROM 절의 두 테이블 이름 사이에 JOIN 방식과 함께 JOIN 절 입력
   1. Cross Join
   2. Natural Join
   3. USING 절을 사용하는 JOIN 구문
   4. ON 절을 사용하는 JOIN 구문
   5. LEFT / RIGHT / FULL OUTER  JOIN

 

CROSS JOIN

1. 두 테이블 간의 Cartesian Product과 동일한 결과
2. 두 테이블간의 상호 가능한 모든 조합을 결과로 반환
3. FROM 절의 두 테이블 사이에 CROSS JOIN 키워드를 지정
4. 두 테이블의 행수를 곱한 수를 결과로 반환
5. 구문

SELECT [table1.]column, [table2.]column
FROM table1 CROSS JOIN table2;

 

• y_emp와 y_dept를 CROSS JOIN하여 사원이름과 부서이름의 모든 가능한 조합을 결과로 생성

SELECT emp_name, dept_name
FROM y_emp CROSS JOIN y_dept;

 

NATURAL JOIN

1. JOIN 하는 두 테이블에 열 이름과 데이터 유형이 동일한 열을 각각 포함하고 있다면 그 열을 기준으로 자동으로 조인을 수행
   1. 두 테이블 모두에서 이름과 데이터 유형이 동일한 열에서만 가능
   2. 열의 이름은 같지만 데이터 유형이 다를 경우 NATURAL JOIN 구문에서 오류가 발생
2. 구문

SELECT [table1.]column, [table2.]column
FROM table1 NATURAL JOIN table2;

 

• y_dept와 y_loc 테이블을 NATURAL JOIN한 결과

SELECT dept_id, dept_name, city
FROM y_dept NATURAL JOIN y_loc
WHERE dept_id IN (100, 200);

 

USING 절을 사용하는 JOIN 문의 작성

1. 둘 이상의 열이 일치하는 테이블 간의 조인에서 사용할 열을 명시적으로 지정
2. 열 이름이 같지만 유형이 다른 경우에도 오류 없이 조인을 수행
3. 구문

SELECT [table1.]column, [table2.]column
FROM table1 JOIN table2
USING(column);

 

• y_emp, y_dept 테이블을 dept_id열을 기준으로 조인한 결과

SELECT emp_id, emp_name, dept_id, dept_name
FROM y_emp JOIN y_dept
USING (dept_id);

 

테이블의 접두어 사용

1. 조인되는 두 테이블에 동일한 이름의 열이 존재하는 경우
   1. 열 이름 앞에 테이블 이름을 지정하여 열을 검색할 테이블을 한정
   2. 테이블 접두어를 사용하지 않으면 조인되는 두 테이블에 모두 포함된 열의 경우 오류를 반환

테이블 별칭의 사용

1. 열 이름을 한정하기 위해 접두어 대신 테이블 별칭 사용
   1. 긴 이름의 테이블에 적합
   2. SQL 코드를 더 작게 유지
2. 별칭 사용 방법
   1. 테이블 이름 뒤에 공백이 온 후에 테이블 별칭 지정
   2. SELECT 문에서 테이블 이름을 대신하므로 의미있는 이름으로 지정
   3. 최대 30자까지 가능하지만 짧을수록 좋다.

 

• 테이블 접두어를 사용하여 조인

SELECT y_emp.emp_name, y_emp.mgr_id, dept_id, y_dept.dept_name
FROM y_emp JOIN y_dept
USING (dept_id);

 

• 테이블 별칭을 사용하여 조인
• 테이블 별칭을 썼는데 접두어로 별칭을 안쓰고 테이블 전체 이름을 쓰면 오류

SELECT e.emp_id, e.emp_name, dept_id, d.dept_name
FROM y_emp e JOIN y_dept d
USING (dept_id);

 

EQUI JOIN 과 NON EQUI JOIN

1. Equi Join 이란?
   1. 단순 조인 또는 내부 조인 (inner join)
   2. 두 테이블에서 연관이 있는 데이터에 대하여 동등비교를 수행
   3. 일반적으로 가장 많이 사용되는 조인방식
   4. 주로 기본키와 외래키 관계에 있는 두 테이블 간에 자주 사용
   5. NATURAL JOIN이나 USING절을 사용하는 조인은 Equi Join만 가능
2. Non Equi Join 이란?
   
   1. 조인 조건에 동등연산자(등호,=)가 아닌 연산자를 사용하여 두 테이블의 관계를 기술하는 조인

 

ON 절을 사용하는 등가 조인

1. ON 절 사용 시 열의 이름이 다른 두 테이블 간의 Equi Join도 가능
   1. 여러 가지 조인 조건을 ON 절에 직접 지정
   2. WHERE 절의 다른 검색조건과 조인조건 분리
   3. 사용자 입장에서 코드를 이해하기 용이함
2. 구문

SELECT [table1.]column, [table2.]column
FROM table1 JOIN table2
ON (table1.column = table2.cloumn);

 

• ON 절에 조인조건을 작성하여 y_emp 테이블과 y_dept 테이블을 dept_id 열을 기준으로 조인

SELECT d.dept_id, d.dept_name, d.loc_id, l.city
FROM y_dept d JOIN y_loc l
ON (d.loc_id = l.loc_id);

 

• 조인문에도 행을 선택하는 WHERE 절을 쓸 수 있으며 USING 또는 ON 절 다음에 쓴다.

SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name
FROM y_emp e JOIN y_dept d
ON (e.dept_id = d.dept_id)
WHERE e.salary > 650;

 

ON 절을 사용하는 여러가지 조인

1. 비등가 조인(NON EQUI JOIN)
   1. ON절에는 임의의 연산자가 사용이 가능하므로 비등가 조인도 가능
   2. 주로 BETWEEN 연산자가 많이 사용
2. 자체 조인(SELF JOIN)
   1. 하나의 테이블을 두 번 검색해서 조인
   2. FROM 절에 지정한 테이블 두 개가 실제 동일한 테이블
3. 3 Way 조인
   1. 세 개의 테이블을 조인하는 것을 의미
   2. 세 개의 테이블을 조인하여 더 많은 테이블을 조합하여 다양한 결과를 생성

 

• y_emp 테이블의 salary 열과 pay_grade의 low_pay, high_pay 열을 비등가 조인하여 각 사원의 급여 등급을 표시한다.

SELECT e.emp_id, e.emp_name, e.salary, p.pay_level
FROM y_emp e JOIN pay_grade p
ON e.salary BETWEEN p.low_pay AND p.high_pay;

 

• 각 사원의 관리자 사원번호와 함께 관리자의 이름을 함께 출력하는 SELF JOIN

SELECT e.emp_id, e.emp_name, e.mgr_id, m.emp_name
FROM y_emp e JOIN y_emp m
ON (e.mgr_id = m.emp_id);

 

• y_emp, y_dept, y_loc 세 개의 테이블을 조인하여 사원이름과 소속부서 이름 및 부서가 있는 도시 이름을 함께 검색

SELECT emp_name, dept_name, city
FROM y_emp JOIN y_dept
ON (y_emp.dept_id = y_dept.dept_id)
JOIN y_loc
ON (y_dept.loc_id = y_loc.loc_id);

 

INNER JOIN 과 OUTER JOIN의 개념

1. INNER JOIN (내부 조인)
   1. 조인하는 열에 대해 양쪽 테이블 모두에서 일치하는 행만 반환
   2. 조인 조건에 만족하는 값을 갖지 못한 행은 결과에서 누락
   3. 예 : 전체 사원은 33명인데 y_emp 테이블과 y_dept 테이블을 조인하면 부서가 NULL인 사원은 결과에서 제외되어 32명의 사원만 결과로 반환
2. OUTER JOIN (외부 조인)
   1. INNER JOIN의 결과와 함께 INNER JOIN 시 누락된 행을 함께 표시
   2. 예 : y_emp 테이블과 y_dept 테이블을 OUTER JOIN 시 부서가 NULL인 사원 또는 사원이 배치되지 않아서 누락된 부서정보를 INNER JOIN의 결과와 함께 반환

 

OUTER JOIN

1. LEFT OUTER JOIN
   1. INNER JOIN의 결과와 함께 JOIN 구문을 기준으로 일치하는 행이 없는 왼쪽 테이블의 행을 반환하는 조인
2. RIGHT OUTER JOIN
   1. 동일한 방식으로 일치하는 행이 없는 오른쪽 테이블의 행을 반환하는 조인
3. FULL OUTER JOIN
   1. 두 가지 결과를 함께 반환하는 조인
4. 구문 : FROM 절에 LEFT / RIGHT / FULL OUTER JOIN을 명시하고 ON 절에는 조인 조건만 지정

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

 

• INNER JOIN의 결과와 함께 JOIN 키워드를 중심으로 왼쪽 테이블에 해당하는 y_emp에서 누락되었던 행을 반환하는 LEFT OUTER JOIN의 예제이다. 결과에는 INNER JOIN 시 누락되었던 부서가 배정되지 않아 부서번호가 NULL인 사원정보가 포함된다.
• 부서번호나 부서이름이 NULL 이라서 나오지 않던 사람들도 포함되어 나온다.

SELECT e.emp_name, e.dept_id, d.dept_name
FROM y_emp e LEFT OUTER JOIN y_dept d
ON (e.dept_id = d.dept_id);

 

• y_emp와 y_dept를 RIGHT OUTER JOIN 하여 근무하는 사원이 한 명도 없어 INNER JOIN 시 누락된 부서 정보를 확인

SELECT e.emp_name, d.dept_id, d.dept_name
FROM y_emp e RIGHT OUTER JOIN y_dept d
ON (e.dept_id = d.dept_Id);

 

• y_emp 테이블과 y_dept 테이블을 FULL OUTER JOIN 하여 INNER JOIN의 결과에 양쪽 테이블의 모든 누락된 행을 함께 표시

SELECT e.emp_name, e.dept_id, d.dept_name
FROM y_emp e FULL OUTER JOIN y_dept d
ON (e.dept_id = d.dept_id);