[Database] 17. JOIN(조인) 정리 - INNER/LEFT/RIGHT/OUTER

 

1. JOIN (조인)

 

1. JOIN (조인) 개념

- 조인 : 관계를 맺고 있는 2개 이상의 테이블을 묶어서 하나의 결과 테이블을 만드는 것

- 종류 : INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, UNION 

- 데이터베이스는 데이터의 중복저장, 저장공간의 낭비등을 피하고 데이터의 무결성을 보장하기 위해 여러 개의 테이블

에 나누어 저장한다. 

- 이러한 정규화 과정에 의해 서로 관계를 맺고 있는 분리된 테이블을 일상적 질의를 처리하기 위해 조립하는 방법

 

2. JOIN (조인) 참고 사이트

https://sql-joins.leopard.in.ua 

SQL Joins Visualizer

 

 

2. 정규화와 비정규화

 

1. 비정규화(Denormalization 데이터 베이스화 전)

 

2. 정규화(Normalized 데이터 베이스화 후)

 

- 잘라서 저장하고 붙여서 사용하고 스마트 하게 하는 방법 = JOIN

- 실습테이블 : LECTURE.MEMBER / LECTURE.NOTICE

- 1 : N 관계

 

 

3. JOIN 종류

 

1. INNER JOIN

조인하기전 테이블

조인 후 테이블

 

실습 - MEMBER 중 게시글을 등록한 회원정보를 모두 조회하시오

SELECT ID, NAME, GENDER 
    FROM MEMBER;
    SELECT TITLE, WRITER_ID, HIT 
    FROM NOTICE;

    SELECT ID, NAME, GENDER 
    FROM MEMBER JOIN NOTICE ON ID = WRITER_ID;
    -- !  에러발생 -> 별칭 사용해야함
    -- Column 'ID' in field list is ambiguous ERROR
       -- join 의 디폴트 값은 inner join 이다!


    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.WRITER_ID, N.HIT 
    FROM 
           MEMBER AS M JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;

    -- ! ID와 WRITER_ID 중복 제거   
    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.HIT 
    FROM 
           MEMBER AS M JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;

 

2. LEFT OUTER JOIN

조인하기전 테이블

조인 후 테이블

 

실습 1 - employees 테이블과 Salaries 테이블 조인

SELECT E.emp_no, E.first_name, E.GENDER,
           S.to_date, S.salary
    FROM 
           employees AS E LEFT OUTER JOIN Salaries AS S 
           ON E.emp_no = S.emp_no
limit 10;

 

실습 2 - dept_emp 테이블과 departments 테이블 조인

SELECT DE.emp_no, DE.dept_no, 
              DP.dept_name
    FROM 
           dept_emp AS DE LEFT OUTER JOIN departments AS DP 
           ON DE.dept_no = DP.dept_no
limit 10;

 

실습 3 - MEMBER 중 게시글을 등록하지 않은 회원정보를 조회하시오

    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.HIT
    FROM 
           MEMBER AS M LEFT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;    

    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.HIT 
    FROM 
           MEMBER AS M LEFT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID
    WHERE  N.TITLE IS NULL;

 

3. RIGHT OUTER JOIN

조인하기전 테이블

조인 후 테이블

 

실습 - 비회원 작성자의 게시글을 조회하시오

	-- 답 아님
    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.HIT
    FROM 
           MEMBER AS M RIGHT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;

    -- ! 작성자 ID가 필요
    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.WRITER_ID, N.HIT 
    FROM 
           MEMBER AS M RIGHT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;

    -- [정답 코드] 
    -- ! 회원정보 불필요=> NULL 값만 뽑아주면 됨
    SELECT 
           N.TITLE, N.WRITER_ID, N.HIT 
    FROM 
           MEMBER AS M RIGHT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID
    WHERE  M.ID IS NULL;

 

4. FULL OUTER JOIN

조인하기전 테이블

조인 후 테이블

 

	SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.WRITER_ID, N.HIT 
    FROM 
           MEMBER AS M LEFT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID  

    UNION

    SELECT M.ID, M.NAME, M.GENDER,
           N.TITLE, N.WRITER_ID, N.HIT 
    FROM 
           MEMBER AS M RIGHT OUTER JOIN NOTICE AS N 
           ON M.ID = N.WRITER_ID;

* mysql은 full outer join을 지원하지 않으므로 union 이용할 것

 

실습 - 아래와 같이 테이블이 조회되도록 쿼리를 완성하시오 (단, employees DB사용)

 

방법 1)

SELECT E.emp_no, E.first_name, E.GENDER,
           S.to_date, S.salary, DE.DEPT_NO, DP.dept_name
FROM employees E LEFT OUTER JOIN Salaries S on E.emp_no = S.emp_no
                 LEFT OUTER JOIN dept_emp DE ON S.emp_no = de.emp_no
                 LEFT OUTER JOIN departments DP ON DE.DEPT_NO = DP.DEPT_NO
limit 50;

 

방법 2)

SELECT E.EMP_NO, E.FIRST_NAME, E.GENDER,
       S.TO_DATE, S.SALARY, DE.DEPT_NO, DP.DEPT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN SALARIES S 
                 LEFT OUTER JOIN DEPT_EMP DE ON S.EMP_NO = DE.EMP_NO
                 LEFT OUTER JOIN DEPARTMENTS DP ON DE.DEPT_NO = DP.DEPT_NO
     ON E.EMP_NO = S.EMP_NO
LIMIT 100;

 

방법 3)

-- 두 조인 테이블을 또다시 조인
select x.emp_no, x.first_name, x.GENDER, 
              x.to_date, x.salary, y.dept_no, y.dept_name
FROM
       (SELECT E.emp_no, E.first_name, E.GENDER,
              S.to_date, S.salary
              FROM 
              employees AS E LEFT OUTER JOIN Salaries AS S 
              ON E.emp_no = S.emp_no limit 50) X 
LEFT OUTER JOIN 
       (SELECT DE.emp_no, DE.dept_no, 
                     DP.dept_name
       FROM 
              dept_emp AS DE LEFT OUTER JOIN departments AS DP 
              ON DE.dept_no = DP.dept_no limit 50) Y
ON X.emp_no = Y.emp_no limit 50;