[SQL] SQL Cookbook - 03.다중 테이블 작업

 

1. 행 집합을 다른 행 위에 추가하기

UNION : 중복된 행을 제거하고 두 개 이상의 쿼리 결과를 하나로 합친다.

UNION ALL : 중복된 행을 제거하지 않고 두 개 이상의 쿼리를 결과를 합친다.

/*EMP 테이블에 있는 부서 10의 사원명 및 부서 번호와 함께, DEPT 테이블에 이쓴ㄴ 각 부서명 및 부서 번호를 표시.*/

# UNION ALL
select ename as ename_and_dname, deptno
from emp
   where deptno = 10
   union all 
  select '---------', null
    from t1
   union all
  select dname, deptno
    from dept

 

2. 연관된 여러 행 결합하기

JOIN : 두 개 이상의 테이블을 연결하여 관련된 데이터를 함께 가져온다.

/*e.DEPTNO와 d.DEPTNO가 같은 행 반환*/
# JOIN
select e.name, d.loc
	from emp e inner join dept d
     on (e.deptno = d.deptno)
    where e.deptno = 10

 

3. 두 테이블의 공통 행 찾기

INTERSECT : 두 개 이상의 SELECT 문의 결과에서 교집합을 찾아내는 역할을 한다. 즉 공통으로 존재하는 행만 반환.

# MySQL, SQLServer
select e.empno, e.ename, e.job, e.sal, e.deptno
	from emp e, V
   where e.ename = V.ename
    and e.job = V.job
    and e.sal = V.sal
    
# Oracle, PostgreSQL 
select empno, ename, job, sal, deptno
	from emp
   where (ename, job, sal) in (
   	select ename, job, sal, from emp
   intersect
    select ename, job, sal from V
)

 

4. 한 테이블에서 다른 테이블에 존재하지 않는 값 검색하기

except(minus) : 두 개의 쿼리 결과에서 차집합을 찾아낸다. 즉 첫 번재 쿼리 결과에서 두 번째 쿼리 결과를 뺀 값을 반환.

# PostgreSQL - except
select deptno from dept
 except
select eptno from emp

# Oracle - minux
select deptno from dept
 minus
select deptno from emp

# MySQL - in
select deptno
	from dept
   where deptno not in (select deptno from emp)

 

5. 다른 테이블 행과 일치하지 않는 행 검색하기

select d.*
	from dept d left outer join emp e
     on (d.deptno = e.deptno)
   where e.deptno is null

 

6. 다른 조인을 방해하지 않고 쿼리에 조인 추가하기

/*외부 조인을 사용하여 원래 쿼리의 데이터의 손실 없이 추가 정보를 얻을 수 있다.*/
select e.ename, d.loc, eb.received
	from emp e join dept d
     on (e.deptno = d.deptno)
    left join emp_bouns eb
     on (e.empno = eb.empno)
  order by 2

 

7. 두 테이블에 같은 데이터가 있는지 확인하기

# PostgreSQL
(
 select empno, ename, job, mgr, hiredate, sal, comm, deptno,
 	count(*) as cnt
   from V
   group by empno, ename, job, mgr, hiredate, sal, comm, deptno
  except
  select empno, ename, job, mgr, hiredate, sal, comm, deptno,
    count(*) as cnt
   from emp
   group by empno, job, mgr, hiredate, sal, comm, deptno
  )
  union all
  (
  select empno, ename, job, mgr, hiredate, sal, comm, deptno,
 	count(*) as cnt
   from emp
   group by empno, ename, job, mgr, hiredate, sal, comm, deptno
  except
  select empno, ename, job, mgr, hiredate, sal, comm, deptno,
    count(*) as cnt
   from V
   group by empno, job, mgr, hiredate, sal, comm, deptno
)

 

8. 데카르트 곱 식별 및 방지하기

/*부서 위치와 함께 부서 10의 각 사원명을 반환*/
select e.ename, d.loc
	from emp e, dept d
   where e.deptno = 10
    and d.deptno = e.deptno

 

9. 집계를 사용할 때 조인 수행하기

join 시 열의 중복으로 인해 sum()함수의 결과가 제대로 조회되지 않을 경우.

# MySQL, PostgreSQL - DISTINCT
select deptno,
	sum(distinct sal) as total_sal,
    sum(bonus) as total_bonus
  from (
 select e.empno,
 		e.ename,
        e.sal,
        e.deptno,
        e.sal * case when eb.type = 1 then .1
        	     when eb.type = 2 then .2
                     else .3
                and as bonus
   from emp e, emp_bonus eb
   where e.empno = eb.empno
    and e.deptno = 10]
    	) x
  group by deptno

 

10. 집계 시 외부 조인 수행하기

9번의 문제와 유사하지만, 부서 10의 모든 사원이 포함되도록 EMP_BONUS에 외부조인.

select deptno,
	sum(distinct sal) as total_sal,
    sum(bonus) as total_bonus
 from (
select e.empno,
	   e.ename,
       e.sal,
       e.deptno,
       e.sal * case when eb.type is null then 0
       				when eb.type = 1 then .1
                    when eb.type = 2 then .2
                    else .3 and as bonus
  from emp e left outer join emp_bonus eb
  	on (e.empno = eb.empno)
 where e.deptno = 10
 	)
 group by deptno

 

11. 여러 테이블에서 누락된 데이터 반환하기

전체 외부 조인을 사용하여 일치하는 행과 함께 두 테이블에서 누락된 행을 반환

# FULL OUTER JOIN
select d.deptno, d.dname, e.ename
 from dept d full outer join emp e
 	on (d.deptno = e.deptno)

 

12. 연산 및 비교에서 null 사용하기

이름이 'WARD'인 사람보다 commission이 낮은 사람을 조회하려 할 때 commission이 NULL인 사원도 포함.

COALESCE(?, ?) : 첫번째 값이 null이면 두번째 값으로 대체한다.

# COALESCE
select ename, comm
 from emp
where coalesce(comm, 0) < (select comm
			    from emp
                           where ename = 'WARD')

 

 

 

 

reference.

[SQL Cookbook] - 한빛미디어