SQL 6일차 17-22

수강날짜 20-07-27 (20-07-26 + 1)

깅의명 : 01 조인이란
깅의명 : 02 INNER 조인
깅의명 : 03 OUTER 조인
깅의명 : 04 SELF 조인
깅의명 : 05 FULL OUTHER조인
깅의명 : 06 CROSS 조인

조인이란

2개 이상의 테이블에 있는 정보 중 사용자가 필요한 집합에 맞게 가상의 테이블처럼 만들어서 결과를 보여주는 것이다.

조인의 종류

실습 환경 준비
코드

create table basket_a 
(
	ID INT primary key
  , FRUIT VARCHAR (100) not NULL
);

create table basket_b
(
	 ID INT primary key
   , FRUIT VARCHAR (100) not NULL
);


insert into basket_a
(ID, FRUIT)
values
(1, 'Apple'),
(2, 'Orange'),
(3, 'Banana'),
(4, 'Cucumber')
;
commit;


insert into basket_b
(ID, FRUIT)
values
(1, 'Orange'),
(2, 'Apple'),
(3, 'Watermelon'),
(4, 'Pear')
;

테이블

INNER 조인

특정 컬럼을 기준으로 정확히 매칭된 집합을 출력한다. INNER 조인은 대표적인 조인의 종류이다.

실습

select a.id ID_A, a.fruit FRUIT_A
     , b.id ID_B, b.fruit FRUIT_B
  from basket_a a 
 inner join basket_b b 
    on a.fruit = b.fruit;

결과

집합에 대한 교집합들의 결과들만을 조인한다.

실습1
customer 테이블과 payment 테이블 있는 CID를 이용하여 손님의 ID, 이름, 성, 이메일, 지불액수, 지불일자를 조회하라

select a.customer_id, a.first_name
     , a.last_name, a.email 
     , b.amount, b.payment_date 
  from customer a 
 inner join payment b
    on a.customer_id = b.customer_id;

실습2
위의 질문 결과에서 CID가 2인 사람에 대해서만 조회하라

select a.customer_id, a.first_name
     , a.last_name, a.email 
     , b.amount, b.payment_date 
  from customer a 
 inner join payment b
    on a.customer_id = b.customer_id
 where a.customer_id = 2;

실습3
3개의 테이블에서 실습 진행. 위에 거래가 되었을 때 그 거래를 진행한 스태프의 대한 정보를 같이 제공하라

select a.customer_id, a.first_name
     , a.last_name, a.email 
     , b.amount, b.payment_date 
     , c.first_name as S_FIRST_NAME
     , c.last_name  as S_LAST_NAME
  from customer a 
 inner join payment b
    on a.customer_id = b.customer_id
 inner join staff c 
    on b.staff_id  = c.staff_id ;

OUTER 조인

특정 컬럼을 기준으로 매칭된 집합을 출력하지만 한쪽의 집합은 모두 출력하고 다른 한쪽의 집합은 매칭되는 컬럼의 값 만을 출력한다.
매칭되지 않는 컴럼의 값은 NULL로 표현을 한다.

예제 - LEFFT OUTER 조인

select a.id ID_A, a.fruit FRUIT_A
     , b.id ID_B, b.fruit FRUIT_B
  from basket_a a 
 left join basket_b b 
    on a.fruit = b.fruit;

예제 - LEFFT ONLY 조인
차집합

select a.id ID_A, a.fruit FRUIT_A
     , b.id ID_B, b.fruit FRUIT_B
  from basket_a a 
 left join basket_b b 
    on a.fruit = b.fruit 
 where b.id is NULL;

예제 - RIGHT OUTER 조인
뒤에 조인 절 이후에 나온 애를 기준으로 OUTTER 조인

select a.id ID_A, a.fruit FRUIT_A
     , b.id ID_B, b.fruit FRUIT_B
  from basket_a a 
 right join basket_b b 
    on a.fruit = b.fruit;

SELF 조인

같은 테이블 끼리 특정 컬럼을 기준을 매칭 되는컬럼을 출력하는 조인이다. 즉 같은 테이블의 데이터를 각각의 집합으로 분류한 후 조인한다.

예제
예제 테이블

manager_id가 employee_id에 있다. 매니저 또한 직원이기 때문이다. 그럼 직원과 직원의 매니저를 같이 보고 싶으면 어떻게 하는가? 그때 사용하는 것이 SELF JOIN이다.

select 
	  e.first_name || ' ' || e.last_name as employee
  	, m.first_name || ' ' || m.last_name as manager
  from employee e 
 inner join employee m
    on e.manager_id = m.employee_id
 order by manager;

실습
SELF 조인 실습 - SELF LEFT OUTER 조인
최상위 관리자는 위의 상사(매니저)가 없다. 하지만 위의 결과에 따르면 매니저가 없어서 최상위 관리자는 쿼리의 결과로 나오지 않는다. 이를 해결하기 위해 OUTER 조인을 실시한다.

select 
	  e.first_name || ' ' || e.last_name as employee
  	, m.first_name || ' ' || m.last_name as manager
  from employee e 
 left join employee m
    on e.manager_id = m.employee_id
 order by manager;

최고 관리자인 Windy Hays가 출력된다!

SELF 조인 실습 - 부정형 조건
영화의 상영 시간은 같지만 서로 다른 영화를 출력하라

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; 

FULL OUTER 조인

INNER, LEFT OUTER, RIGHT OUTER 조인 집합을 모두 출력하는 조인 방식이다.
즉 두 테이블간 출력 가능한 모든 데이터를포함한 집합을 출력한다.
합집합이다.

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;

FULL OUTER 조인 실습 -ONLY OUTER 조인

합집합 - 교집합

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;

예제 테이블

employees 테이블

departments 테이블

FULL OUTER 조인 실습

select 
	  e.employee_name 
	, d.department_name
  from employees e
  full outer join departments d 
    on d.department_id = e.department_id ;

FULL OUTER 조인 실습 - RIGHT ONLY

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;

CROSS 조인

두개의 테이블의 CATESIAN PRODUCT 연산의 결과를 출력한다. 데이터 복제에 많이 쓰이는 기법이다.

예제 테이블

cross_t1

cross_t2

CROSS 조인 실습

select *
  from cross_t1
 cross join
	   cross_t2;

조인의 조건이 존재 하지 않는다! 그렇다면 결과는 어떻게 나올까?

조인 조건이 없기에 모든 가능한 경우를 뽑고 다른 테이블의 경우와 합쳐서 다음과 같이 표현한다.
이를 카티션 곱 연산이라고 한다.

모든 경우의 수를 뽑는 것이기 때문에 JOIN조건을 적지 않는다.

카티전 곱은 다음과 같이도 표현이 가능하다.

select *
  from cross_t1, cross_t2;

결과 값이 위와 같음을 알 수 있다.

CROSS 조인 사용법

select *
  from cross_t1, cross_t2;

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;

이렇게 복잡한 연산에서 사용이 가능하다.