[SQL] 조인 - Inner, Outer, Full, Cross, Natural, Self

조인(Join)이란?

• 2개 이상의 테이블을 사용자가 필요한 집합에 맞게 가상 테이블처럼 만들어 보여주는 것

조인의 종류

• Inner 조인: 교집합으로, 정확히 중복되는 요소만 출력
• Outer 조인
  • Left Outer: Left는 전부 가져오고, Right에서는 Left에 있는 것만 가져옴
  • Right Outer: Right는 전부 가져오고, Left에서는 Right에 있는 것만 가져옴
  • Full(Outer) 조인: 합집합으로, Inner + Left + Right Outer 조인 모두 출력
• Cross 조인: 조인되는 두 테이블의 곱집합을 출력
• Natural 조인: 각 테이블에 동일한 이름의 컬럼을 기준으로 알아서 조인이 이뤄짐
• Self 조인: 동일한 테이블 출력

 

Self 조인 - 자체 조인

• 같은 테이블을 마치 2개의 테이블을 사용하는 것처럼 조인

---------- # Self + Inner: NULL과 매칭되는 값은 제외
SELECT
      E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE  -- || '문자열' ||
    , M.FIRST_NAME || ' ' || M.LAST_NAME MANAGER
  FROM
      EMPLOYEE E                                   -- EMPLOYEE에 EMPLOYEE 조인
INNER JOIN EMPLOYEE M                              
  ON M .EMPLOYEE_ID = E.MANAGER_ID                 -- Id 기준으로 컬럼 조인
ORDER BY MANAGER
;

---------- # Self + Left Outer: NULL값까지 포함해서 모두 출력됨
SELECT
      E.FIRST_NAME || ' ' || E.LAST_NAME EMPLOYEE
    , M.FIRST_NAME || ' ' || M .LAST_NAME MANAGER
  FROM
      EMPLOYEE E                                   -- EMPLOYEE에 EMPLOYEE 조인
LEFT OUTER JOIN EMPLOYEE M 
  ON M .EMPLOYEE_ID = E.MANAGER_ID
ORDER BY MANAGER
;

---------- # Self + 부정형 <>:
SELECT
       F1.TITLE
     , F2.TITLE
     , F1.LENGTH
  FROM
      FILM F1
INNER JOIN FILM F2                                 -- FIML 셀프 조인
  ON F1.FILM_ID <> F2.FILM_ID                      -- FilmId가 서로 다르고
  AND F1. LENGTH = F2.LENGTH                       -- 상영시간은 동일한
;

---------- # 셀프 조인하지 않고 테이블 하나만 쓸 경우 오류
SELECT * 
  FROM film f1
WHERE f1.LENGTH = f1.length  
  AND F1.FILM_ID <> f1.FILM_ID
;

 

Inner 조인 - 교집합

• 특정 컬럼 기준, 정확히 매칭된 요소만 출력하는 대표적인 조인 방법

---------- # 이너조인 조건 없는 경우
SELECT * 
  FROM CROSS_T1, CROSS_T2
ORDER BY LABEL 
;

---------- # 이너조인 + ON 조건
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                 -- Basket_A와 Basket_B를
  ON A.FRUIT = B.FRUIT                -- Fruit 기준으로 이너 조인
;

---------- # 2개 테이블 이너조인 (고객1:결제M)
SELECT
       A.CUSTOMER_ID
     , A.FIRST_NAME
     , A.LAST_NAME
     , A.EMAIL
     , B.AMOUNT
     , B.PAYMENT_DATE
  FROM CUSTOMER A 
INNER JOIN PAYMENT B                  -- Customer A와 Payment B 테이블을
  ON A.CUSTOMER_ID = B.CUSTOMER_ID    -- Customer_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                  -- Customer A와 Payment B 테이블을
  ON A.CUSTOMER_ID = 2                -- Customer_ID 2 기준으로 이너 조인
;

---------- # 3개 테이블 이너조인 (고객1:결제M:직원1)
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 조인 - 집합본인

• 특정 컬럼 기준, 한쪽은 모두 출력하고 다른 한쪽은 매칭되는 요소만 출력

---------- # Left Join: 왼쪽에 있는 A는 다, B는 A와 중복되는 부분만 나옴
SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A
LEFT OUTER JOIN BASKET_B B            -- OUTER 생략 가능
  ON A.FRUIT = B.FRUIT                -- Fruit 기준으로 레프트 조인
;

---------- # Left Only: 왼쪽 A에서 B가 Null인것 제외
SELECT
      A.ID    AS ID_A
    , A.FRUIT AS FRUIT_A
    , B.ID    AS ID_B
    , B.FRUIT AS FRUIT_B
  FROM
      BASKET_A A
LEFT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
WHERE B.ID IS NULL                  -- A와 B가 교집합되지 않는 부분
;

---------- # Right Join: 오른쪽 B는 다 나옴
SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A
RIGHT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
;

---------- # Right Only: 오른쪽 B에서 A가 Null인것 제외
SELECT
       A.ID AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
       BASKET_A A
RIGHT OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
WHERE A.ID IS NULL                   -- A가 Null인 부분 제외
;

 

Full 조인 - 합집합

• 테이블간 출력 가능한 모든 데이터를 포함해 출력
• Inner + Left Outer + Right Outer

---------- # Full Outer
SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B
     , B.FRUIT AS FRUIT_B
  FROM
      BASKET_A A                     -- BASKET_A테이블과 BASKET_B
FULL OUTER JOIN BASKET_B B           
  ON A.FRUIT = B.FRUIT                -- FRUIT 컬럼 기준 조인
;  

---------- # Full Outer Only: 교집합(inner) 제외한 합집합
SELECT
       A.ID    AS ID_A
     , A.FRUIT AS FRUIT_A
     , B.ID    AS ID_B 
     , B.FRUIT AS FRUIT_B
  FROM
      BASKET_A A
FULL OUTER JOIN BASKET_B B 
  ON A.FRUIT = B.FRUIT
WHERE  A.ID IS NULL                  -- right outer이면 왼쪽이 null
  OR  B.ID IS NULL                   -- left outer이면 오른쪽이 null
;

---------- # 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         
;

---------- # Full Outer + Left Only: 소속된 부서가 없는 직원만 출력
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_ID IS NULL
;

 

Cross 조인 - 곱집합

• 두개의 테이블의 카테시안 곱(Catesian Product) 연산의 결과로 출력 가능한 모든 조건을 다 출력
• 데이터 복제에 많이 쓰이는 기법으로, 조인 조건인 on을 쓰지 않는다

SELECT
      *
 FROM 
      CROSS_T1                       -- CROSS_T1과 CROSS_T2를 조인
CROSS JOIN 
      CROSS_T2
ORDER BY LABEL       
;

---------- # 응용
SELECT LABEL, 
  CASE WHEN LABEL = 'A' THEN sum(score)       -- 라벨A면 score더해라
       WHEN LABEL = 'B' THEN sum(score) * -1  -- 라벨B면 score더하고 -1곱
       ELSE 0                                 -- A와 B도 아니면 0을 출력
       END AS calc                            -- 끝내고 결과를 calc로 출력
 FROM CROSS_T1
CROSS JOIN 
      CROSS_T2
GROUP BY LABEL
ORDER BY LABEL
;

 

Natural 조인 - 자동조인

• 각 테이블에서 공통되는 컬럼으로 알아서 자동으로 조인해줌
• NATURAL 조인은 INNER 조인의 또 다른 SQL 작성 방식으로, 조인 컬럼을 명시하지 않아도 되서 SQL문 자체가 간소해짐.
• 하지만 예상치 못하게 데이터가 안 나오는 경우가 많아서 실무에서는 잘 사용하지 않음.

---------- # Natural: 동일하게 가지고 있는 CATEGORY_ID컬럼을 기준으로 INNER조인
SELECT
      *
 FROM
      PRODUCTS A
NATURAL JOIN 
      CATEGORIES B
;

---------- # Natural + Inner
SELECT
     A.CATEGORY_ID
   , A.PRODUCT_ID
   , A.PRODUCT_NAME
   , B.CATEGORY_NAME
 FROM
     PRODUCTS A                       -- PRODUCTS와 CATEGORIES 조인
INNER JOIN 
     CATEGORIES B 
ON (A.CATEGORY_ID = B.CATEGORY_ID)    -- CATEGORY_ID컬럼을 기준으로
;

---------- # 위와 동일한 방식의 Inner 조인 표현식
SELECT
     A.CATEGORY_ID
   , A.PRODUCT_ID
   , A.PRODUCT_NAME
   , B.CATEGORY_NAME
  FROM
       PRODUCTS A 
     , CATEGORIES B 
WHERE A.CATEGORY_ID = B.CATEGORY_ID 
;

---------- # Natural: 두 테이블간 동일한 이름의 컬럼 없을 경우, 데이터 안 나옴
SELECT
       *
  FROM
       CITY A
NATURAL JOIN 
       COUNTRY B
;

---------- # Inner: INNER 조인으로 ON절에 조인 컬럼을 명시하였고, 의도한대로 출력
SELECT *
  FROM
   		CITY A 
INNER JOIN COUNTRY B 
  ON (A.COUNTRY_ID = B.COUNTRY_ID)
;

---------- # 위와 동일
SELECT 
	   *
  FROM
       CITY A 
     , COUNTRY B 
WHERE A.COUNTRY_ID = B.COUNTRY_ID
;

 

Reference

• Fast Campus, 모두를 위한 SQL/DB 올인원 패키지 온라인 강의