[SQL 쿡북] Chapter 3 - 2 : 다중 테이블 작업

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

dept 테이블의 deptno 값이 40인 데이터는 emp 테이블에는 없으므로 다음과 같은 결과 셋을 나타낸다. 

[결과셋]

차집합을 수행하는 함수를 이용하면 유용하다.  

 

# DB2, PostgreSQL, SQL Server

EXCEPT은 비교할 데이터 유형 및 값 개수가 일치해야 한다. 또한 중복 항목을 반홚지 않으며 NOT IN을 사용하는 서브쿼리와 달리 null이 문제가 되지 않는다. 즉, 연산자 이후의 쿼리 에 없는 상위 쿼리에서 행을 반환한다.

 

# Oracle

EXCEPT과 같지만 언어만 다르다. MINUS 를 사용하면 된다.

 

#MYSQL

서브 쿼리는 모든 deptno를 반환하고, NOT IN을 사용할 때는 null에 유의해야한다. IN 과 NOT IN은 OR 연산이며, TURE or NULL은 TURE 이지만 , FALSE or NULL 은 NULL 이라는 것을 알아야한다. sql 진리표를 참고하면 이해에 도움이 된다.

따라서 만약에 서브쿼리가 10, 50, NULL을 함께 반환한다면 아래와 같은 의미가 된다. 

deptno가 10이어서 서브쿼리 안의 논리가 TURE로 반환하여도 NOT이기 때문에 다시 FALSE가 되어서 쿼리가 행을 반환하지 않는다. 하지만 IN은 서브쿼리에 포함된 값이 있으면 결과가 도출된다.

 

NOT IN 의 null 문제를 방지하려면 NOT EXISTS와 함께 서브쿼리를 사용하면 된다. 다음 예는 null 행에 영향을 받지 않는 해법이다.

위와 같이 하면, 같은 번호를 가진 경우 TRUE를 서브쿼리가 반환하고 NOT EXISTS 가 FALSE로 평가시키기 때문에, 번호가 같지 않은 dept 테이블에만 있는 번호를 가져올 수 있게되어 처음과 같은 결과셋을 나타낸다. 

 

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

공통 키가 있는 두 테이블에서 한 테이블의 모든 행을 포함하여, 공통 열과 일치하거나 일치하지 않을 수 있는 다른 행을 함께 반환하려고 한다. 그런 다음 일치하는 것이 없는 행만 남겨둔다.

 

# DB2, PostgreSQL, SQL Server, MYSQL

null에 대한 아우터 조인 및 필터를 사용한다. (OUTER라는 키워드는 선택 사항이다.)

[결과셋]

안티 조인이라고도 하는데, deptno이 같은 것끼리 연결해주지만 dept 테이블은 공통 열과 일치하지 않을 수 있는 행을 함께 반환하는 것이다. 이해가 쉽게 null을 필터링 해주지 않은 결과 셋은 다음 과 같다.

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

select * from emp_bonus

[결과셋]

emp 테이블을 dept 테이블에 조인하여 deptno가 일치하는 부서의 위치를 파악한 다음, emp_bonus 테이블을 외부 조인한 결과를 나타낸다.

이 결과셋을 스칼라 서브쿼리 (SELECT 목록에 있는 서브쿼리)를 사용하여 외부 조인을 흉내낼 수 있다.

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

뷰의 데이터가 emp 테이블과 정확히 같은지를 확인하려고 한다. 집합 연산 EXCEPT와 UNION ALL을 이용하여 뷰 V에서 EMP 테이블과 다른 점을 찾고, EMP테이블에서 뷰 V와 다른 점을 찾아서 합친다.

select * from V

# DB2, PostgreSQL

[결과셋]

이 내용은 전편 Chapter3-1 과 내용이 중복된다. Oracle은 MINUS를, 나머지는 NOT EXISTS로 응용하면 된다.

 

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

from 절에 있는 테이블 간의 조인을 사용하여 중복을 방지해야한다. 데카르트 곱을 피하려면 n-1 규칙을 이용해야 하는데, n은 FROM 절의 테이블 갯수이다. 최소한 n-1개의 조인이 필요하다는 뜻이다.

 

[잘못된 쿼리]

부서 10번인 emp 테이블의 행은 3개이므로, dept의 전체 행이 4개 이므로 12개의 행을 반환하게 된다.  FROM 절에 두개의 테이블이 있으니 하나 이상의 조인 절이 필요하므로 추가해준다.

 

[옳은 쿼리]

 

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

조인을 쓸 때 집계를 하면 주의해야한다. 결합하기 전에 이미 계산되어 문제가 발생할 수 있기 때문이다. 책은 두가지 해법을 제시하고 있다.

 

1) DISTINCT 사용

# DB2, Oracle, SQL Server

위와 같이 쓸 수 있지만 대체 해법으로 SUM OVER을 사용 할 수 있다.

 

 

2) 조인하기 전에 먼저 (인라인 뷰에서) 집계를 수행하여 잘못된 계산을 피하기

 

3.10 집계 시 외부 조인 수행하기 

위와 연장 선으로 아우터 조인을 이용하여 emp 테이블의 부서 10의 모든 사원을 포함한다.

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

FULL OUTER JOIN 을 이용하여 일치하는 행과 함께 두 테이블이 누락된 모든 행을 반환한다. MTSQL에서는 지원하지 않으므로 UNION 을 사용해야한다.

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

 

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

COALESCE 와 같은 함수를 이용하여 null 값을 실젯값으로 변환한다.

null 값을 0으로 대체하고, 사원 이름이 'WARD'의 커미션보다 적은 모든 사원을 emp 테이블에서 찾을 수 있게 되는 것이다.