SQL : 06 조인

_플레이데이터 빅데이터캠프 공부 내용 _5/27, 5/30  ~~ 작성 중

MySQL : 06 조인

조인은 연관성이 있는 테이블을 합치는 것이다.

이 처리를 위해 테이블 간의 관계성을 알아야 한다.

1.  테이블 간의 관계 의미

직원 테이블에 부서에 관한 DATE가 많이 포함되있을 때, 부서 이름, 부서 전화, 부서 주소 등..

이렇게 중복된 것이 많다면 부서 테이블을 따로 빼는 것이 좋을 것이다.

두 개 테이블로 쪼개서 두 테이블 간의 관계를 보고 참조하며 사용해보자.

 

그럼 소속부서를 식별할 수 있는 KEY가 필요한데, 이때 컬럼값 Primary Key값(PK)로 참조를 하는 것이다.

직원테이블은 부서 컬럼에 대해 참조할 테이블인 부서 테이블의 PK를 참조하여 데이터를 가진다.

이때, 참조한 PK값을 적은 직원 테이블의 컬럼값을 Foreign Key값(FK) 라고 한다.

그리고 소속부서 테이블의 date는 직원 테이블이 참조하는 date 이자 직원의 sub date 라 말할 수 있다.

 

이러한 테이블 관리의 장점은?

데이터 관리하기가 쉽다. 한 date가 여러 테이블에 있는데 date 가 바뀐다면 테이블마다 다 바꿔야 한다. 

그러나, 테이블 참조를 이용하면 한 테이블만 변경하면 된다.

 

이때, 그럼 조회를 할 때 어떻게 하냐?

부서테이블 데이터를 참조하는 직웝테이블의 직원 정보를 알기 위해

직원 조회, 부서 조회 두 번해야 한다. 이렇게 하면 비효율적일 것이다.

이를 한 번에 할 수 있게 하는 게 JOIN 이다.

 

2. Foreign Key 제약 조건

2.1 Foreign Key

: 한 테이블이 다른 테이블의 컬럼 값을 참조하는 것

- 부모 테이블 : 참조 되는 테이블

- 자식 테이블 : 참조 하는 테이블

 

2.2. DB 설계도

 - 관계선과 기호의 종류

** 중요성

DB 설계 엔지니어가 아니더라도 데이터를 이용할 개발자라면 DB 설계도를 읽을 줄 알아야 한다. 

코드 작성 이전, 테이블, 데이터, 컬럼 간의 관계, 참조 관계, 처리 방식, 구조 ... 등 을 이해하여야 한다.

2. 3. 테이블간의 관계를 만드는 테이블 생성 과정

부모 테이블을 먼저 만들고 자식 테이블 만들기

 - 부모 테이블 : 식별자 역할을 할  컬럼 즉, 참조가 될 컬럼을 PK 제약조건 지정하여 테이블 생성   

 - 자식 테이블 : create 구문 -> constraint 제약조건 구문 -> insert구문 순으로 구문 맞춰서 테이블 생성

 

2.4  자식 테이블 _  create 구문

(1) 전체 구문

- Foreign Key 컬럼 : 부모 테이블의 PK 컬럼을 참조하는 컬럼.

  부모 테이블의 PK (참조할 컬럼)의 데이터 타입과 동일하여야 한다.

  이름이 같을 필요는 없다.

 

[전체 구문]

CREATE TABLE emp(
    emp_id 	  INT PRIMARY KEY,
    emp_name  VARCHAR(20) NOT NULL, 
    job_id 	  VARCHAR(30),  -- FK JOB 테이블 참조 -- job 테이블의 job_id 값과 동일한 데이터 값을 지정한 것.!
    mgr_id 	  INT,  -- FK EMP 테이블 참조 SELF 참조
    hire_date DATE NOT NULL,
    salary 	  DECIMAL(7,2) NOT NULL,
    comm_pct  DECIMAL(2,2),
    dept_id   INT,    -- FK DEPT 테이블 참조
    
    CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL,
    CONSTRAINT fk_emp_job FOREIGN KEY(job_id) REFERENCES job(job_id) ON DELETE SET NULL,
    CONSTRAINT fk_emp_mgr FOREIGN KEY(mgr_id) REFERENCES emp(emp_id) ON DELETE SET NULL
);

(2) CONSTRAINT 제약조건 구문

CONSTRAINT 제약조건이름 FOREIGN KEY (자식테이블의 컬럼) REFERENCES 부모테이블(PK컬럼) [ON 설정]

-  제약조건 이름

:   제약조건타입의 약자_테이블_컬럼        # 뭐든 상관없으나 보통 이렇게 작성.

 

-  ON 설정

 자식 테이블로부터 참조 당하는 부모 테이블의 row는 삭제, 수정 할 수 없다.  에러

 이에 대한 두 가지 설정 방법이 있다. 이 설정 비워두면 부모테이블 못 지움. 

    - ON DELETE : 참조하는 부모테이블의 행이 삭제되었을 때 어떻게 처리할 것인지 설정

    - ON UPDATE : 참조하는 부모테이블의 참조 컬럼값이 변경되면 어떻게 처리할 것인지 설정 

 

___처리방식___

     1. 부모 테이블의 참조 row 삭제 시 자식 테이블의 참조 row 삭제 

         => FOREIGN  KET 설정 시, ON DELETE CASCADE 설정

 CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE CASCADE

 

     2. 부모 테이블의 참조 row 삭제 시 참조하는 자식 테이블의 컬럼 값을 NULL로 설정.

            => FOREIGN  KET 설정 시, ON DELETE SET NULL 설정

CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(dept_id) ON DELETE SET NULL

           - 이 방법은 FK 컬럼값이 NULL을 가질 수 있는 데이터 타입일 때만 가능하다.

 

(4) 부모 table drop 테이블 삭제 시 : FK 외래키 제약조건 제거

  부모테이블을 그냥 drop 삭제할 수 없다. 

    - > 부모 테이블 drop 전에 MySQL의 Foreign key 적용이 안되도록 설정해줘야 한다.

set foreign_key_checks = 0; -- foreign key 해제 설정
DROP table 삭제할 테이블;
set foreign_key_checks = 1; -- foreign key 적용 설정

3. JOIN 

 3.1 테이블 합치는 것에 의미 

데이터를 테이블 별로 분산시켜 생성했다.

조회 select 시 여러 테이블에서 데이터를 들고 오는데 이때 하나의 sql문으로 처리되어야 한다.
이를 위해서 JOIN 구문이 필요한 것이다. JOIN 구문을 통해 세 테이블을 합쳐서 합친 것을 from 하면 된다. 

 

근데 합치는데 어떤 테이블은 30행 어떤테이블은 107행 어떤 테이블은 50 행 인데 어떻게 합치냐.

애초에 이렇게 테이블 자체를 그냥 합치는 것이 아니다.

==> JOIN 관계가 있는, 참조 관계가 있는, 두 테이블의 특정 컴럼의 값들이 같은 행끼리 합치는 것이다.

흔히, 자식테이블의 특정 컬럼(fk) 값에 해당하는(일치하는) 부모테이블의 컬럼(pk) 값의 행을 '같은 행'으로 합치는 것이다. 

예를 들어, emp_id 100 인 직원의 모든 정보를 조회한다고 하자.
emp의 직업 id가 100인 행을 찾는다.  그렇다면, job 테이블의 직원 id 100인 행이랑만 합치면 되는 것이다.

emp의 부서 id는 90인 행을 찾는다.  그렇다면, dept 테이블의 부서 id가 90인 행이랑만 합치면 되는 것이다.

 

아무거나 붙이는 게 아니라 이렇게 관계가 있는 데이터끼리 붙어야 하는 것.

보통, 일반적으로 부모-자식 테이블을 연결해서 조회할 때 JOIN을 쓰이지만

이 관계가 아니더라도 업무적으로 연결된, 관련성이 있는 값이라면 이때도 JOIN을 사용한다.

 

 

3.2 JOIN 개념

 : 2개 이상의 테이블에 있는 컬럼들을 합쳐서(join) 가상의 테이블을 만들어 조회(select)하는 방식을 말한다.

 : 서로 관계가 있는(부모-자식 관계테이블, 이 관계가 아니더라도 업무적으로 관계가 있는 테이블)

    여러 테이블의 퍼져있는 값을 모아 select 하는 것.

 

- 테이블을 합치는, 가상의 테이블을 만드는 과정이니 당연히 FROM 절에서 한다. 

 

JOIN 개념 다시 정리하자면,

조회하고 싶은 데이터가 emp 테이블과 dept 테이블 두 테이블에 있다.

이를 하나의 sql문으로 해야 한다.

근데 select는 한 테이블 대상밖에 안된다. 그렇다면 emp 테이블과 dept 테이블을 합쳐서 가상의 테이블을 만들자.
==> 조건이 맞는 행을 들고 와,

       부모 테이블 해당 행과 자식 테이블 해당 행끼리 맞춰서 합친

       새로운 테이블을 만들는 것!

 

3.2.1. 조인연산

: 어떤 행들의 값이 같은 것끼리 합칠 것인지 설정하는 구문.

 - 일반적으로 부모테이블과 자식테이블을 연결해서 조회하는 경우가 많으며 

   이 경우 부모테이블의 PK와 자식테이블의 FK 컬럼의 값이 같은 행들을 JOIN 한다.

 

(1) 조인 연산에 따른 조인종류 Equi join

 

(2) 조인 연산에 따른 조인종류 non-equi join

 

 

3.2.2. 조인의 종류
(1) Inner Join 
        - 양쪽 테이블에서 조인 연산 조건을 만족하는 행끼리만 합친다. 

-- INNER JOIN : a와 b 테이블을 합칠건데, 조인조건(연산)을 만족하는 행끼리만 합칠거야.

FROM  테이블a INNER JOIN 테이블b ON 조인조건(연산)  
              JOIN 테이블b ON 조인조건(연산)

 

- inner 은 생략 가능. 합칠 테이블 여러 개면 이어서 하면 됨. 

- inner에서는 소스 테이블 선정이 중요하지는 않지만, 상황에 맞게 소스 테이블을 선정해 앞에 적어준다.

- 일단 테이블 합치고 나서 가상 테이블을 불러와 쿼리대로 처리를 하는 것.

-- 직원_ID(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary), 담당업무명(job.job_title), 소속부서이름(dept.dept_name)을 조회
-- 담당업무명과 소속부서이름은 직원테이블이 아닌 직업, 부서 테이블에 있으므로 
-- job_id와 dept_id 값을 참조하여 조회한다.
select e.emp_id, e.emp_name, e.salary, j.job_title, d.dept_name
from emp e join job j on e.job_id = j.job_id
           join dept d on e.dept_id = d.dept_id;

-- TODO 부서별 급여(salary)의 평균을 조회. 부서이름(dept.dept_name)과 급여평균을 출력. 급여 평균이 높은 순서로 정렬. 
-- dept_name은 Unique key 컬럼이 아님.
-- 시애틀에 있는 마케팅 부서, 산프란에 있는 마케팅 부서 이렇게 데이터가 있을 수도 있다.
-- > dept_id와 같이  group by로 묶어준다.

select d.dept_name, avg(salary) "급여평균"
from emp e join dept d on e.dept_id = d.dept_id
group by d.dept_id, d.dept_name 
order by 2 desc;

 

(2) Outer Join
        - 한쪽 테이블의 행들을 모두 불러오고, 다른 쪽 테이블은 조인 조건을 만족하는 행만 합친다.

          조인조건을 만족하는 행이 없는 경우 NULL을 합친다.

         - 목적 : 조인 연산 조건을 만족하지 않더라도 소스테이블의 데이터 전부를 보고 싶을 때

         - 종류 

           Left Outer Join : 구문상 소스 테이블이 왼쪽  -- 소스가 어디에 있는 지 알려준다.

           Right Outer Join : 구문상 소스 테이블이 오른쪽

           Full Outer Join :  둘다 소스 테이블 (Mysql은 지원하지 않는다. - union 연산을 이용해서 구현)

 

- 소스테이블 : 조인 시 조회 기준이 되는 테이블로 조회하려는 주(main) 정보를 가지고 있는 테이블.

                       내가 먼저 읽어야 한다고 생각하는 테이블. outer join 시 이 테이블의 모든 행 모두가 불려오는 것.

- 타켓테이블 : 조인 시 Source table 데이터의 추가 정보를 가지고 있는 테이블로 보조(sub) 정보를 가지고 있는 테이블

                       소스를 읽은 후 소스에 조인할 대상이 되는 테이블. 붙여지는 테이블

== > 조인 시 Source table 데이터(행)은 모두 사용하고

        target table의 데이터(행)은 조인 조건을 만족하는 행만 나오도록 한다.

 

         -  구문

from 테이블a [LEFT | RIGHT] OUTER JOIN 테이블b ON 조인조건
- OUTER는 생략 가능.

-- join : inner join
-- left join: left outer join
-- right join : right outer join

-- 어떤 것이 소스테이블이냐 에 따라 테이블 아예 달라짐. 그래서 소스 타켓 구분을 잘 짓어야 한다.
select *
from emp e left join dept d on e.dept_id = d.dept_id
order by emp_id;

select *
from emp e right join dept d on e.dept_id = d.dept_id
order by emp_id;

 

어떤 상황에서 Outer JOIN 이 필요할까?

 

from emp e join dept d on e.dept_id = d.dept_id 이렇게 inner join을 한다고 하자.

이때, 만약 부서가 없는 애들. 즉 e.dept_id가 null인 데이터가 있을 수 있다.

on 조건에 따라 두 id 값이 같아야 하는데

d.dept_id는 pk이므로 not null unique key인데 얘네는 null 값이 당연히 없다. 

그렇다면 null인 부서가 없는 애들은 조인할 때 빠져버리는 것이다.

부서 미배치일 뿐 이 직원 데이터도 필요할 수가 있는데 ,, inner join으로 인해 모든 정보가 안 뜰 수도 있는 것이다.

 

이때, 우리는 Outer Join을 사용한다.
특정 부서에 소속된 직원의 정보 이상으로, 부서에 소속되지 않은 null인 애들도 보고 싶다면 outer로 해야한다.

 

나는 이 테이블의 정보를 다 봐야겠다라면 Outer join의 소스테이블로 지정하고 쿼리문을 작성해야 한다.

모든 컬럼의 정보가 있든 없든 일단 이 테이블의 데이터(행)을 전부 보겠다라는 것.

일단, 소스 데이터 다 볼건데 조건에 맞춰 타켓 테이블 붙인다. 그리고 만약 없는 정보는 NULL 처리를 한다.

조건만 만족하는 특정부서에 해당하는 값을 볼 때, NotNull 일때  즉 null의 상황이 나올리가 없을 때는 inner 을 쓴다.

-- 전체! 모든 직원의 id(emp.emp_id), 이름(emp.emp_name), 부서_id(emp.dept_id)를 조회하는데
-- 부서_id가 80 인 직원들은 부서명(dept.dept_name)과 부서위치(dept.loc) 도 같이 출력한다.
-- (부서 ID가 80이 아니면 null이 나오도록)

-- 행선택 조건이면 where, 조인 조건은 from에 / inner은 어디에 넣어도 상관없지만 outer 일때는 조건을 어디 붙일 지 생각해야 함.

select e.emp_id, e.emp_name, e.dept_id, d.dept_name, d.loc
from emp e left join dept d on e.dept_id = d.dept_id and d.dept_id=80 
-- id 가 80 인 것만 붙여.! 근데 지금 outerjoin이니깐 소스테이블 모든 행이 온다.
-- 그러므로 안 붙인 빈공간은 null 로 채우게 됨
--  join 조건은 여러 개 와도 된다 . and 연산자로.

order by 3;

 

- left join 이니깐 소스테이블은 다 나오는 게 맞다.

근데  on 조건에 따라 타켓테이블 중 행, 데이터가 달라지는 것.  안 붙인 곳은 null 처리.

 

- count(*) 집계할 때 이는 행의 갯수를 세는 것이다. 

outer join 으로 인해 null 값을 가진 행까지 모두 불러오기에 이때 주의해서 작성해야한다.

-- TODO: 부서 ID(dept.dept_id), 부서이름(dept.dept_name)과 그 부서에 속한 직원들의 수를 조회. 
-- 직원이 없는 부서는 0이 나오도록 조회하고 직원수가 많은 부서 순서로 조회.
-- count(*) : 행수
select d.dept_id, d.dept_name, 
	  count(e.emp_id) "직원수" 
 --     count(*) "직원수" -- 이건 행을 세는 함수라 null로 된 값이여도 한 행으로 카운트 해서 1로 출력.
from dept d left join emp e on d.dept_id = e.dept_id
group by d.dept_id, d.dept_name
order by 3 desc;

 

** 소스와 타켓 차이를 이해할 수 있어야 한다. 둘을 생각하고 쿼리 짜야함.

다 보려고 하는 것이 소스 테이블.

처리는 일단, 먼저 소스 테이블 쫘악 붙이고 그 다음 타켓은 조건에 맞게 붙이고 해당 값이 없어 조건에 맞지 않더라도  null로 처리하는 것.

 

 

 

 

(3) self 조인

: 물리적으로 하나의 테이블을 두개의 테이블처럼 조인하는 것. 자신이 자신을 참조.

  한 테이블의 컬럼이 자신의 컬럼을 참조 받아야 할 때 마치 두 테이블이 따로 있다고 생각하고 join 구문을 작성한다.

 

ex) 상사의 정보 - 부하의 정보 : 이 두가지가 한 테이블에 있다. 이 부모자식 관계가 한 테이블에 있음.

- > 각 직원 정보와 함께 해당 직원의 상사에 대한 정보 또한 한 행에서 보고 싶다.

- > 상사 emp 테이블과 부하직원 emp 테이블 두 가지가 있다고 생각해야 한다.

      마치 두 개가 있는 것 처럼 생각하고 이를 조인시킨다. 

-- 직원 ID가 101인 직원의 직원의 ID(emp.emp_id), 이름(emp.emp_name), 상사이름(emp.emp_name)을 조회
select e.emp_id, e.emp_name, m.emp_name
from emp e join emp m on e.mgr_id = m.emp_id
where e.emp_id = 101;
-- e : 부하 직원의 emp table. 부하직원에 대한 데이터
-- m : 상하 직원의 emp table. 상사직원에 대한 데이터

 

 

(4) Cross Join
        - 두 테이블의 곱집합을 반환한다. 
        - (a테이블 1과 b 테이블과 1) / ( 1과 2) / (2과1) / (2와 2) 이렇게 모든 행의 곱 조건을 다 해보는 것. 
        - 우린 조회할 때, 한 대상에 대한 데이터여야 한다. 근데  이렇게 하면 대부분의 상황에서 오류.

        - 이 개념은 있지만 쓸 일은 없다. 거의 연산이 잘못된 경우.

SELECT * 
  FROM t1 CROSS JOIN t2;

select * from emp cross join dept;  
-- emp 107행  dept 27행
-- select 107*27; 107 * 27 값이랑 같다. 이렇게 대부분의 상황에서 곱집합 할 일이 없다.

 

---- 예제로 더 깊게 보기----

 

- 부모자식이여야만 조인하는 것이 아니다. 아니더라도 업무적으로 관계가 있다면 조인할 수 있다.

부모자식관계와 조인을 이해할 같은 개념으로 생각하지 말기.

이때, 업무적 엮여있는 관계를 생각해서 구문(on 조건부분)을 작성해줘야 한다.

조건을 만족하는 행들을 합치는 행위 자체가 조인.

-- salary 등급(salary_grade.grade)이 1인 직원들이 부서별로 몇명있는지 조회. 
-- 직원수가 많은 부서 순서대로 정렬.

select d.dept_name, count(*) "급여등급이 1인 직원 수"
from emp e join salary_grade s on e.salary between s.low_sal and s.high_sal
           join dept d on d.dept_id = e.dept_id
where s.grade =1
group by d.dept_id, d.dept_name
order by 1 desc;

 

- 직접적으로 부모자식 연결 안 된 테이블도 조인해야 할 때가 있다.

그렇다면 중간 연결된 테이블 통해서 연결해줘야 함.

-- TODO : 주문 ID가 4 (orders)인 주문의 주문 고객의 이름, 주소, 우편번호, (cust)/ 주문일, 주문상태, 총금액, (orders)/주문 제품이름, 제조사, 제품가격, (prod)/판매가격, 제품수량(order_items)을 조회.

select c.cust_name, c.address, c.postal_code,
		o.order_date, o.order_status, o.order_total,
        p.product_name, p.maker, p.price,
        oi.sell_price, oi.quantity
from orders o join customers c  on c.cust_id = o.cust_id
              join order_items oi on o.order_id = oi.order_id
              join products p on oi.product_id = p.product_id 
              -- orders과 products는 부모 자식 안 이어져 있지만
              -- 중간 다리 테이블 oi 의 데이터을 사용하여 조건 만들어줘서 원하는 행 붙이기 
where o.order_id = 4;

 

- 여러 테이블을 붙인 케이스일 때 소스케이스를 먼저 두고 그다음 타켓 붙인 테이블을 상상한다.

그 상태에서 조건에 맞춰 다음 타켓을 붙이고 또 다음 조건에 맞춰 타켓을 붙인다고 생각하자.

-- TODO 2003년~2005년 사이에 입사한 모든 직원의 id(emp.emp_id), 이름(emp.emp_name),급여(emp.salary), 입사일(emp.hire_date), 업무명(job.job_title) -> emp job
-- 상사이름(emp.emp_name), 상사의입사일(emp.hire_date) -> emp
-- 직원과 상사의 소속부서이름(dept.dept_name), 직원과 상사의 부서위치(dept.loc)를 조회. ->dept

-- 셀프 조인에서 들고온 상사의 테이블까지 합친 테이블에서, 부서 테이블에서 상사의 id와 동일하다는 조건을 두고 일치하는 행을 들고 오는 것. 

select e.emp_id, e.emp_name, j.job_title, e.salary, e.hire_date "직원 입사일", m.emp_name "상사이름", m.hire_date "상사의 입사일", d.dept_name, dm.dept_name "상사의 부서명", d.loc, dm.loc"상사의 근무지" 
from emp e left join job j on j.job_id = e.job_id
            left join dept d on e.dept_id = d.dept_id
            join emp m on e.mgr_id = m.emp_id
            left join dept dm on m.dept_id = d.dept_id
where year(e.hire_date) between 2003 and 2005;

 

- 집계함수와 outer join 함께 쓰일 때 더 주의하기. 

- 어떤 값을 집계할 지 깊게 생각해봐야 한다.

-- TODO : 제품 ID가 200인 제품이 2017년에 몇개 주문되었는지 조회.
select sum(quantity) "총 주문 수량", count(*) "주문량"
from order_items oi join orders o on oi.order_id = o.order_id
where product_id = 200 and year(o.order_date) = 2017;

-- TODO : 제품분류별 총 주문량을 조회 (카테고리 별로 몇 개 주문되었는지)
select p.category, ifnull(sum(quantity), 0) "총주문량"
from products p left join order_items oi on p.product_id = oi.product_id
group by p.category;