JOIN(OUTER join, Self join, Non equi join), ANSI 표준 JOIN(Natural join, JOIN USING, JOIN ON)

안녕 난 푸름이야·2023년 3월 21일
sql빅데이터&머신러닝
0

빅데이터&머신러닝 - SQL

목록 보기
5/14

Join에는 크게 5가지 유형이 존재한다. Cartesian product, Equi join, Outer join, Self join, Non Equi join. 이 중에서 어제 Cartesian product와 Equi join에 대해 배웠고 오늘은 나머지 유형들을 배웠다.

Join

Outer join

: 키 값이 일치되는 데이터 또는 키 값이 일치되지 않은 데이터도 출력하는 조인이다. Equi join은 키 값이 일치하는 데이터'만' 뽑아내기 때문에 키 값이 없는 데이터는 누락될 수 밖에 없다. 이를 보완하기 위해 나온 것이 바로 outer join이다. 오라클에서 Outer join은 (+)를 이용해서 표현하고 양쪽에 (+)를 수행하면 오류가 발생한다.(플러스 부호 표현은 오라클 한정이다. 추후 오라클과 ANSI 표준에 대해 기술하겠다.) 예를 들어보자.

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

employees 테이블과 departments 테이블에서 department_id라는 컬럼 명으로 join 한다는 뜻이다. 여기까지는 어제 배웠던 equi join과 같다.(키 값이 일치하는 값만 출력함) 그런데 이 쿼리문의 결과값을 살펴보면 데이터의 총 건수는 107건인데 여기서는 106건 밖에 나오지 않는다. 왜냐하면 사원 이름은 있는데 부서 id가 정해지지 않은 데이터, 즉 department_id에 null 값이 있기 때문에 데이터가 누락된 것이다. 이렇게 누락된 데이터를 뽑아낼 땐 아래와 같이 표현한다.

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

플러스 부호가 없는 쪽 테이블(employees)의 데이터를 전부 출력하겠다는 뜻이다. employees 테이블에서 누락된 데이터까지 출력되고, 결과값의 총 건수는 107건으로 나타난다.

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

플러스 부호의 위치만 바꾼 이 쿼리문은 어떨까? 플러스 부호가 없는 쪽 테이블, 즉 departments 테이블의 데이터를 전부 출력하겠다는 뜻이다. 그런데 이 쿼리문은 성립하지 않는 문장이다. 왜냐하면 departments 테이블의 부서 id를 보면 null 값이 없다. 누락되는 데이터가 없다는 소리다. 논리적으로 생각해봐도 부서 이름이 있으면 부서 id는 당연히 있다. 따라서 결과값은 outer join이 아닌 cartesian product로 나타난다.

일단 논리에 맞게 쿼리문을 던져보고 건수 검증을 통해 누락되는 데이터가 없는지 확인한 뒤, 우리가 출력하고자 하는 값에 따라 (+)를 적절히 사용한다면 데이터를 원하는 형태로 출력하는 것이 가능하다. 이게 바로 (오라클에서 사용하는) outer join이다.

테이블이 3개 이상일 때에도 outer join을 사용할 수 있다. 예를 들어보자.

1)
SELECT e.last_name, e.job_id, d.department_id, d.department_name
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)

아까 위의 예제에서 locations 테이블 하나를 더 추가해 총 3개의 테이블을 조인한다고 하자. 우선 e 테이블과 d 테이블을 조인한다고 했을 때 위 예제처럼 누락되는 데이터가 생긴다. 때문에 outer join을 한 번 걸어서 employees 테이블에서 누락되는 데이터를 뽑아낸다. 그런 다음,

2)
SELECT e.last_name, e.job_id, d.department_id, 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;

조인을 수행할 테이블이 3개니까 조인 조건 술어는 3-1=2개여야 한다. 그래서 AND로 locations 테이블을 하나 더 조인했다. 조인을 한번 더 했기 때문에 마찬가지로 건수 검증을 해줘야 한다. 1)까지의 건수는 107건이었다. 그런데 2)의 결과는 또 다시 106건 밖에 출력되지 않는다. 왜냐하면 departments 테이블에서 누락되는 데이터가 존재하기 때문이다.(부서 id가 null이기 때문에 당연히 부서 이름도 null로 뜰 것)

3)
SELECT e.last_name, e.job_id, d.department_id, 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(+);

따라서 전체 결과건수가 107건이 나오게 하려면 AND 절에 걸린 조인 조건 술어에도 outer join을 걸어서 누락되는 데이터를 뽑아내야 한다.

위의 Join의 결과를 이해하려면 아래와 같은 다이어그램을 가지고 이해하는 것이 좋다. WHERE 절에서 outer join을 한 번 걸어주었고 그 아래 d.location_id는 하나의 조인결과집합으로 해석하여 그 한 덩어리에 또 한 번 outer join을 걸어준다고 생각하면 이해하기 쉽다. d.location_id에 (+)가 붙지 않는 이유도 이와 같은 맥락이라고 보면 된다. (그렇게 되면 결과적으로 AND d.location_id(+) = l.location_id(+); 이런 식으로 양쪽에 (+)를 붙이는 것과 같은 의미이기 때문에)

Self join

: 자신의 테이블을 참조할 때 사용하는 조인.

SELECT w.employee_id, w.last_name, m.employee_id, m.last_name
FROM employees w, employees m		-- w는 일반직원 테이블, m은 관리자 테이블
WHERE w.manager_id = m.employee_id;

예를 들면, 관리자들도 본인들의 고유한 employee_id가 있으므로 일반직원 정보(w.manager_id)에 그 일반직원을 케어하는 관리자 정보를 덧붙이려면 관리자의 사원정보(m.employee_id)를 붙이면 된다.

SELECT
	w.employee_id "사원번호",
    w.last_name "이름",
    m.employee_id "관리자 사원번호",
    m.last_name "관리자 이름"
FROM employees w, employees m
WHERE w.manager_id = m.employee_id(+);

관리자는 없지만 employee_id는 있는 데이터(ex.CEO는 상사가 없다!)가 있을 수 있으므로 누락된 데이터를 써준다고 하면 outer join을 해준다.

Non equi join(비등가 join)

: equi join (=) 할 수 없는 다른 비교연산자를 사용하는 조인의 유형. 값을 범위로 조인하려는 경우 많이 사용된다.

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

연봉의 범위를 등급으로 표시한 테이블을 employees 테이블에 조인한 경우다. 일반적으로 테이블끼리 조인할 때, 같다(=)라는 등호를 사용하여 조인했는데 이렇게 예제처럼 범위로 조건이 걸리는 경우엔 non equi join한다고 표현한다.

Cartesian product, Equi join, Self join, Non equi join은 어디서든 전부 문법이 똑같다. 그런데 Outer join은 오라클과 ANSI 표준(American National Standards Institute)에서 각각 쓰임이 달라진다.

ANSI 표준이란 미국 표준을 의미한다. 여러가지 제약사항도 많고 모호한 부분도 있어서 사용하기 어렵지만 다양한 곳에서 ANSI 표준을 이용하고 있다. 그리고 금융권 등 오라클 체제를 유지하고 있는 기업체들도 많기 때문에 오라클과 ANSI 표준에서 쓰이는 문법 두 가지를 모두 알아야할 필요가 있다.

우선 ANSI에서의 Join 문법을 알아보도록 하자

ANSI-SQL JOIN 문법

Natural join

: Equi join. 조인 조건 술어를 자동으로 만들어준다. 단, 양쪽 테이블의 동일한 이름의 "모~든" 컬럼을 기준으로 조인 조건 술어를 만들기 때문에 컬럼 이름이 동일한게 1개일 때만 내추럴조인을 쓰는 것이 좋다. 그리고 양쪽 테이블 동일 이름의 컬럼의 타입이 서로 다를 경우 오류가 발생한다.

SELECT d.department_name, l.city
FROM departments d NATURAL JOIN locations l;

JOIN USING

: Equi join. 조인 조건의 기준 컬럼을 지정한다. 단, 양쪽 테이블의 컬럼 이름이 동일한 경우 사용한다. USING절에 사용된 기준 컬럼에는 테이블 지정을 하지 않아야 오류가 발생하지 않는다.

  • 오라클 전용

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

  • ANSI 표준

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

JOIN USING은 중첩해서 사용할 수도 있다.

SELECT e.last_name, d.department_name, department_id, location_id, l.city, c.country_name
FROM employees e JOIN departments d
USING(department_id)
JOIN locations l
USING(location_id)
JOIN countries c
USING(country_id)
WHERE department_id IN (20,30);

JOIN ON

: equi joi, non equi join, self join, outer join 모두 사용 가능하다. ON절을 이용해서 조인 조건 술어를 직접 만들어서 사용한다. JOIN USING과 다르게 컬럼의 모호성을 없애기 위해 컬럼에 접두어를 꼭 붙여주어야 한다. JOIN ON은 ANSI 표준 중에서 가장 많이 사용하는 문법이다. JOIN USING과 JOIN ON은 FROM 절에 걸리는 문법이기 때문에 선행될 수밖에 없다. 따라서 WHERE 절과 같이 조건을 제한하려면 JOIN을 사용하고 난 다음에 후술하는 것이 옳다.

  1. Equi join
  • 오라클 전용

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

  • ANSI 표준

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

  1. Non equi join
  • 오라클 전용

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

  • ANSI 표준

SELECT e.employee_id, e.salary, j.grade_level, d.department_name
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
JOIN departments d
ON e.department_id = d.department_id;

  1. Self join
  • 오라클 전용

SELECT w.employee_id, w.last_name, m.employee_id, m.last_name
FROM employees w, employees m
WHERE w.manager_id = m.employee_id;

  • ANSI 표준

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

  1. Outer join
    JOIN ON 절에서 Outer join은 특이하게도 세 가지로 나뉜다.

1) LEFT OUTER JOIN

  • 오라클 전용
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e, departments d
    WHERE e.department_id = d.department_id(+);

  • ANSI 표준
    SELECT e.last_name, e.job_id, d.department_id, d.department_name -- ANSI 표준
    FROM employees e LEFT OUTER JOIN departments d
    ON e.department_id = d.department_id;

2) RIGHT OUTER JOIN

  • 오라클 전용
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e, departments d
    WHERE e.department_id(+) = d.department_id;

  • ANSI 표준
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e RIGHT OUTER JOIN departments d
    ON e.department_id = d.department_id;

3) FULL OUTER JOIN

  • 오라클 전용
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e, departments d
    WHERE e.department_id(+) = d.department_id(+);

논리상으로 보면 full outer join을 할 때 (+)를 양쪽에 붙여야 할 것 같지만, 문법상으로는 양쪽의 (+)를 수행하면 오류가 발생하므로 위의 쿼리문은 잘못된 예시이다.

  • ANSI 표준
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

양쪽에 (+)를 수행하고 싶을 땐 ANSI 표준의 FULL OUTER JOIN을 하면 된다.

  1. Cartesian product
  • 오라클 전용
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e, departments d;
  • ANSI 표준
    SELECT e.last_name, e.job_id, d.department_id, d.department_name
    FROM employees e CROSS JOIN departments d;

CROSS JOIN은 ANSI 표준에서 카티시안 곱을 발생시키는 문법이다.

profile
안녕 난 푸름이야
푸름이의 우당탕탕 코딩생활
이전 포스트

그룹함수, GROUP BY, HAVING, JOIN(Cartesian product, Equi join)

다음 포스트

서브쿼리(중첩, 상호연관, 단일행, 여러행, HAVING), OR · AND 진리표, INLINE VIEW

0개의 댓글