[SQL] JOIN

21 Jul 2018

Reading time ~5 minutes

JOIN

2개 이상의 테이블에서 데이터를 가져오는 방법

특별한 조건없이 조인을 해야할 경우, DESC을 사용하여 동일한 이름의 컬럼을 찾아 조인한다.

JOIN의 확인(건수 검증)

JOIN을 사용할 때 건 수 검증을 통해 확인을 하는 습관을 가지자

테이블이 많을 경우, 건 수 검증은 한번에 이루어지는게 equi join과 outer join을 사용하여 여러번 이루어 질 수 있다.

1쪽 집합과 m쪽 집합
- 1쪽 집합: row간의 중복이 없는 테이블
- m쪽 집합: row간의 중복이 있는 테이블
- Ex. 1쪽 집합과 m쪽 집합을 Join을 하면 1*m개가 발생된다.
Cartesian Product
- JOIN 조건이 생략된 경우
- JOIN 조건이 잘못 만든 경우
- 첫번째 테이블의 모든 행이 2번째 테이블의 모든 행에 조인 된다.

SELECT last_name, department_name
FROM employees, departments;

--cartesian product이 발생 (row는 107(컬럼1)*27(컬럼2)만큼 발생)
--건수 검증으로 JOIN이 잘 되어있는지 확인하자.

JOIN의 테이블 별칭

SELECT last_name, department_name
FROM employees, departments
WHERE department_id = department_id;

>ORA-00918: column ambiguously defined -- Semantic error

문법(Syntax)을 우선적으로 확인하고 후에 의미(Semantic) 분석이 제대로 되었는지 확인한다. (FROM절의 테이블 확인 -> SELECT절의 Column과 TABLE을 비교해서 찾음 -> WHERE절…) 위 절은 Semantic error가 발생하였으므로, 다음 같이 컬럼의 모호성을 해결해야 한다.

SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

시멘틱 검사에서는 빨라졌지만, 코드가 늘어나서 메모리 사용량이 증가함 -> 별칭을 써보자

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

별칭을 사용하므로서 메모리 사용량이 줄어들었다. JOIN을 사용할 시 테이블의 별칭을 사용하자

JOIN의 유형 (4가지)

Equi join (=Inner join, Simple join, 등가조인)
Self join
Outer join
Non-equi join (=비등가조인)

Equi join

서로 다른 테이블에 있는 행들의 JOIN KEY값이 일치(=)되는 데이터만 뽑을 때 쓰는 기능

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id --조인조건술어
AND e.department_id = 10; --비조인조건술어

--위 코드는 다음과 같이 바꿀 수 있음

SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE d.department_id = 10
AND e.department_id=10;

테이블 갯수가 n개면 조인조건술어는 n-1개가 필요

Ex.사원 정보로 근무하는 도시 정보를 확인하기
--3개의 테이블 JOIN -> 2개의 조인조건술어 필요

SELECT e.last_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id; 

Self join

자기 자신의 테이블에서 JOIN하는 경우(Equi join의 한 경우)

Ex. 사원들의 상관 이름을 찾는 경우

SELECT w.last_name, m.last_name
FROM employees w, employees m --테이블 별칭을 사용
WHERE w.manager_id = m.employee_id;

자신을 join할 경우 테이블 별칭을 사용하여 구분
사용하지 않으면 Sementic error 발생
왜? 어느 테이블의 컬럼인지 파악하지 못한다.

Outer join

Equi join의 한계를 보완한 Join. KEY값이 일치되는 데이터를 포함하여, KEY값이 일치되지 않는 경우에도 join으로 추출 할 때 사용

Join 중 유일하게 Oracle 전용 join
조인조건술어에 (+)를 사용하여 표현함
(+)는 기준으로 생각하는 컬럼의 반대편에 사용

다음과 경우에 사용할 수 있음

부서 배치 받지 않은(null) 사원의 경우
보너스를 주려고 하는데, KEY값이 일치되지않아 못받는 사람이 생길 수 있는 경우

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

SELECT e.last_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+);

하지만, 양쪽에 (+)를 사용할 수 없다.

그럼 어떻게 이것을 표현할까?
UNION 연산자를 사용!
UNION: 중복되는 값을 제외하고 모두 합침

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

하지만 부하가 심해 사용하는 것을 지양한다.

Non-equi join

테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용

조인 조건은 동등( = )이외의 연산자를 갖는다.
BETWEEN AND, IN, NOT IN 등의 연산자와 함께 쓰임

/*
--급여 등급 TABLE 생성

DROP table job_grades purge;

CREATE TABLE job_grades
( grade_level varchar2(3),
  lowest_sal  number,
  highest_sal number);

INSERT INTO job_grades VALUES ('A',1000,2999);
INSERT INTO job_grades VALUES ('B',3000,5999);
INSERT INTO job_grades VALUES ('C',6000,9999);
INSERT INTO job_grades VALUES ('D',10000,14999);
INSERT INTO job_grades VALUES ('E',15000,24999);
INSERT INTO job_grades VALUES ('F',25000,40000);
commit;
*/


SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

JOIN의 ANSI 표준 (3가지)

Natural join

두 테이블에서 이름이 같은 모든 컬럼을 기반으로 조인한다.
동일한 이름을 가진 컬럼이 서로 다른 데이터 타입을 가지면 오류가 발생한다.

SELECT last_name, department_name
FROM employees NATURAL JOIN departments;

조인조건술어를 쓰지 않아도 Oracle이 알아서 Cartesian product를 방지하여 join을 해준다.
하지만 위 결과는 원하는 Active set이 나오지 않음
- -> 동일한 컬럼 이름이 여러개 일 수도.
동일한 컬럼이름이 하나인 경우 사용하는 것이 좋음
제약이 많이 자주 사용하지 않는다.

Join using 절

기준 컬럼을 정하여 조인한다.
USING절에 사용된 컬럼 이름 앞에 접두어 사용하면 안된다.

SELECT last_name, department_id, department_name 
FROM employees JOIN departments
USING(department_id);

SELECT e.last_name, department_id, d.department_name 
FROM employees e JOIN departments d
USING(department_id)
WHERE department_id in (20,30);

--USING 절에 사용된 기준 컬럼은 테이블 별칭을 사용해서는 안됌

JOIN USING 절도 제약이 있어서 자주 사용되지 않음

Join on 절

두 테이블을 JOIN하고 ON 절을 기준으로 두 테이블을 조인한다.

SELECT e.last_name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

--조인조건술어: Oracle에서는 WHERE을 사용했지만, ANSI표준은 ON을 사용.

JOIN ON 절에서 3개의 테이블을 JOIN 하는 예시

SELECT e.last_name, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE e.department_id in (20,30); --WHERE에서 비조인조건술어를 기술

SELF JOIN과 NON-EQUI JOIN을 JOIN ON 절로 바꾸어 보자.

Ex. Self join

SELECT e.last_name, e.salary, l.grade_level
FROM employees e JOIN job_grades l
ON e.salary >= l.lowest_sal
AND e.salary <= l.highest_sal
AND e.department_id = 30
AND e.salary >= 3000;

Ex. Non-equi join

SELECT w.last_name, m.last_name
FROM employees w JOIN employees m
ON w.manager_id = m.employee_i;

ANSI 표준으로 자주 사용되니 익혀두도록 하자

Left/Right/Full outer join

Outer JOIN의 ANSI 표준

Left/Right outer join

Ex. Left outer join 예시

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

--아래는 ORACLE의 OUTER JOIN. 비교해보자.

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

Ex. Right outer join 예시

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

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

ANSI 표준으로 테이블 3개를 Join할 때는?

--ANSI 표준 outer join

SELECT e.last_name, d.department_name, l.city
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
LEFT OUTER JOIN locations l
ON d.location_id = l.location_id;

--Oracle outer join

SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+); 

Full outer join

Oracle에서 Outer join은 양쪽에 (+)를 사용못하여 UNION을 사용하지만 좋지않다는 것을 알았다. 그래서 Full outer join을 사용!

Ex. Full outer join 예시

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

sql, join sql