MySQL 데이터베이스 이해하기 /Join 연산

 Join 
 두개 이상의 테이블을 연결해서 질의하는 것

 

관리하게 편하게 하나였던 테이블을 나눔
=> 사용자들이 보일 때 하나로 보여주기 위한 join 두개 이상을 하나로

 

어떤 테이블에 join 하는지에 따라서 join 방식이 달라짐

 

 - Join  종류 
    1. 데이타 추출되는 것에 따라 
 Inner Join     

조인을 위해 사용하는 비교 조건에 맞는 데이타만 조회
조인 조건에 맞지 않은 데이타는 조회 되지 않는다. 
 join할 때  outer join으로 표시 하지 않으면 기본적으로 
Inner join으로 조회된다. 

   

Outer Join 

조인을 위해 사용하는 비교 조건에 맞지 않는 데이타도 조회된다. 
 
개발자의 편의성에 의하여 선택
- left outer join  : 비교 조건에 맞지 않은 왼쪽 테이블의 데이타도 조회됨
- right outer join : 비교 조건에 맞지 않은 오른쪽 테이블의 데이타도 조회됨
- full outer join  : 비교 조건에 맞지 않은 양쪽 테이블의 모든 데이타가 조회됨 // mysql은 지원 안함

 

    2. 비교 조건에 따라서 
  Equi join 

비교하는 두 컬럼의 데이타가 정확하게 일치할 경우 
join 조건으로 = 비교 연산자를 이용할 경우 

 

Non Equi join 

비교하는 두 컬럼의 데이타가 정확하게 일치하지 경우 
join 조건으로  >, < , >= , <=, != 등

 

    3. 비교 테이블에 따라서 
     self join 

 join할 테이블이 다른 테이블이 아닌 self(한개의 테이블로 join)
테이블을 구별하기 위해 반드시 alias를 사용해야 한다.

    
    4. Natural join // 실제 사용은 안함

두 테이블에서 동일한 컬럼명을 갖는 컬럼을 조인 조건으로 사용한다. 
 inner join과 같은 결과를 갖는다     

    5. 카티시안 곱 // 실제 사용은 안함

 조인 조건을 생략했을 때 두 테이블의 모든 row를 연결한 결과가 조회된다. 
 N * M의 수만큼 행이 조회된다. 

 

- join 활용

inner join

 DB 벤더 전용
  select [distinct]   *|컬럼명 [as alias]
  from   테이블명 [alias], 테이블명 [alias] , 
  [where  조건]
  [group by 컬럼명, .. [having 조건]]
  [order by 컬럼명 [asc|desc]], ...]
  
 ANSI query  // inner join은 굳이 쓰지 않음
  select [distinct]   *|컬럼명 [as alias]
  from   테이블명 [alias]
  join   테이블명 [alias]
  on 조인 조건 | using(조인 컬럼) //조건을 여기에
  -----------------
  [where  조건]
  [group by 컬럼명, .. [having 조건]]
  [order by 컬럼명 [asc|desc]], ...]
 
  using 
   - 조인을 위한 비교 컬럼명이 동일 할 때 사용 
   - alias 없이 사용해야 한다. => 다른 DB는 alias를 사용할 경우 error발생 
   - 비교 조건은 기본적으로 = 이 적용된다. 
  
on
   - 조인을 위한 비교 컬럼명이 다르거나 비교 조건이 = 이 아닐때 사용한다. 
   - 조인을 위한 비교 컬럼이 같을 경우 alias를 사용해서 구분한다.

 

 

- 상품번호, 상품명, 상품금액, 카테고리번호,(GOODS 테이블) 카네고리이름 (CATEGORY테이블) 을 조회한다.
- CNO 동일 이름이 있음 

 

*벤더 전용

select gno, brand, price , goods.cno, name
from goods, category
where goods.cno = category.cno
order by gno;

 

* 테이블에 alias를 적용

select gno, brand, price , g.cno, name
from goods g, category c
where g.cno = c.cno
order by gno;

 

*ansi query

select gno, brand, price , g.cno, name
from goods g
join category c
on  g.cno = c.cno
order by gno;

select gno, brand , price, cno ,name
from goods
join category
using (cno) -- 컬럼명이 동일 할 때 using에 컬럼명만 넣어서 알아서
order by gno;

 

=  전부 동일한 결과 발생

    inner join으로 5번에 cno가 없어 빠져있음을 알 수있다.

 

 outer join

  left join 
  select [distinct]   *|컬럼명 [as alias]
  from   테이블명 [alias] 
  left [outer] join   테이블명 [alias]
  on 조인 조건 | using(조인 컬럼)

  right join 
  select [distinct]   *|컬럼명 [as alias]
  from   테이블명 [alias] 
  right [outer] join   테이블명 [alias]
  on 조인 조건 | using(조인 컬럼)

 

기준이 되는 테이블을 어디에 놓느냐에 따라 바뀜

 

-- emp테이블을 기준으로 left join하면 deptno가 없는 사원도 조회됨.

select EMPNO,ENAME,JOB,DEPTNO,DNAME
from emp left join dept
using(deptno);

 

self join 

한개의 테이블로 join 
테이블에 alias를 이용해서 구별한다. 
=>테이블명이 전부 같으므로 alias를 사용해 줘야한다.

 

-- 사원번호, 사원이름, 업무, 급여, 상사번호, 상사이름 조회 

select e.empno, e.ename, e.job, e.sal, e.mgr, m.ename
from emp e , emp m
where e.mgr = m.empno;

 

-- 모든 사원에 대한 사원번호, 사원이름, 업무, 급여, 상사번호, 상사이름 조회
-- (상사가 없는 사원도 조회)

select e.empno, e.ename, e.job, e.sal, e.mgr, m.ename
from emp e 
left join emp m
on e.mgr = m.empno;

 

Natural join

-- 동일한 컬럼명을 조인 조건으로 사용 , equi join

select empno, ename, deptno, dname
from emp natural join dept;