[강의명]
패스트캠퍼스 SQL 100제 + 자격증 완성 온라인 완주반
[수강목록]
20. 조인과 집계 데이터 - 04. SELF조인
21. 조인과 집계 데이터 - 05. FULL OUTER조인
22. 조인과 집계 데이터 - 06. CROSS 조인
[강의내용]
20. 조인과 집계 데이터 - 04. SELF조인
같은 테이블끼리 특정 컬럼을 기준으로 매칭되는 컬럼을 출력하는 조인이다.
즉, 같은 테이블의 데이터를 각각의 집합으로 분류한 후 조인한다.
[샘플데이터 확인]
employee 테이블을 생성한다.
관계도로 표현하면 아래와 같다.
[실습1]
각 직원의 상위관리자를 조회화는 쿼리를 작성한다.
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE,
M.FIRST_NAME || ' ' || M .LAST_NAME MANAGER
FROM EMPLOYEE E
INNER JOIN EMPLOYEE M ON M .EMPLOYEE_ID = E.MANAGER_ID
ORDER BY MANAGER;
INNER JOIN을 통해 같은 테이블을 한번 더 연결한다.
Windy Hays는 최상위관리자라서 INNER JOIN에서는 나타나지 않았다.
[실습2]
LEFT JOIN으로 전체 직원이 나오게 추출한다.
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE,
M.FIRST_NAME || ' ' || M .LAST_NAME MANAGER
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON M .EMPLOYEE_ID = E.MANAGER_ID
ORDER BY MANAGER;
[실습3]
dvdrental 데이터셋으로 돌아와서 부정형 조건을 실습해본다.
film테이블과 film테이블을 SELF JOIN하여 영화상영시간은 동일한 서로 다른 영화의 집합을 출력한다.
SELECT F1.TITLE,
F2.TITLE,
F1.LENGTH
FROM FILM F1
INNER JOIN FILM F2 ON F1.FILM_ID <> F2.FILM_ID
AND F1.LENGTH = F2.LENGTH;
SELF JOIN은 동일한 테이블이지만 각각의 다른 집합으로 구성해놓고
그 안에서 자신이 원하는 정보를 추출하는 것이라 할 수 있다.
21. 조인과 집계 데이터 - 05. FULL OUTER조인
INNER JOIN + LEFT + RIGHT JOIN 집합을 모두 출력하는 조인방식이다.
즉 두 테이블간 출력가능한 모든 데이터를 포함한 집합을 출력한다.
아래의 벤다이어그램을 보면 이해가 쉽다.
[실습1]
fruit 샘플데이터셋을 통해 간단히 확인해본다.
SELECT A.ID ID_A,
A.FRUIT FRUIT_A,
B.ID ID_B,
B.FRUIT FRUIT_B
FROM BASKET_A A
FULL OUTER JOIN BASKET_B B ON A.FRUIT = B.FRUIT;
쿼리 결과를 살펴보면 INNER JOIN, LEFT JOIN, RIGHT JOIN이 차례로 실행된 것을 볼 수 있다.
[실습2]
이번에는 ONLY OUTER JOIN을 확인해본다. FULL - INNER의 결과이다.
SELECT A.ID ID_A,
A.FRUIT FRUIT_A,
B.ID ID_B,
B.FRUIT FRUIT_B
FROM BASKET_A A
FULL OUTER JOIN BASKET_B B ON A.FRUIT = B.FRUIT
WHERE A.ID IS NULL
OR B.ID IS NULL;
[실습3]
더 깊이 들여다보기 위해 살짝 더 큰 샘플데이터(departments, employees)를 준비한다.
FULL JOIN을 실행하면,
SELECT E.EMPLOYEE_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;
WHERE ~ IS NULL 조건을 추가해 소속된 직원이 없는 부서, 소속된 부서가 없는 직원을 추출할 수 있다.
SELECT E.EMPLOYEE_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE E.EMPLOYEE_NAME IS NULL;
Production부서에는 직원이 없다.
SELECT E.EMPLOYEE_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE D.DEPARTMENT_NAME IS NULL;
Mcqueen은 부서가 없다.
22. 조인과 집계 데이터 - 06. CROSS 조인
두개의 테이블의 카테시안 곱(Cartesian Product) 연산의 결과를 출력한다. 데이터 복제에 많이 쓰이는 기법이다.
카테시안 곱이란 두 집합의 곱집합 이며 아래 사진으로 이해하면 쉽다.
각기 다른 원소를 가진 n(A) = 3, n(B) = 3 일때 곱집합의 갯수는 3 * 3 = 9 라고 이해하면 될 듯 싶다.
[샘플데이터 확인]
CROSS JOIN 실습을 위해 샘플데이터를 생성했다.
[실습1]
CROSS JOIN을 걸어보자
SELECT *
FROM CROSS_T1
CROSS JOIN CROSS_T2
ORDER BY LABEL;
(A, B) CROSS JOIN (1, 2, 3) => 총 6개의 결과값이 도출되었다.
이는 단순히 FROM절에 두개의 테이블을 기입하는 결과와도 동일하다.
연결할 수 있는 컬럼이 없어 INNER JOIN을 사용하지는 못하지만 아래와 같이 INNER JOIN처럼 표현 가능하다.
SELECT *
FROM CROSS_T1, CROSS_T2
ORDER BY LABEL;
결과는 동일함을 알 수 있다.
[실습2]
CROSS JOIN을 통해 만든 집합을 통해 이러한 연산도 가능하다.
SELECT LABEL,
CASE WHEN LABEL = 'A' THEN sum(score)
WHEN LABEL = 'B' THEN sum(score) * -1
ELSE 0
END AS calc
FROM CROSS_T1
CROSS JOIN CROSS_T2
GROUP BY LABEL
ORDER BY LABEL;
