[SQL 쿡북] 3. 다중 테이블 작업(13) p61

여러 테이블의 데이터를 결합하는 방법/ 조인은 SQL의 기초이고 집합 연산도 중요합니다. 이 책의 후반부에 나오는 복잡한 쿼리를 이해하려면 지금부터 조인 및 집합 연산을 시작해야 합니다.

 

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

UNION ALL사용

UNION ALL은 여러 행 소스의 행들을 하나의 결과셋으로 결합합니다. 모든 집합 연산과 마찬가지로 모든 SELECT 목록의 항목은 숫자와 데이터 유형이 일치해야 합니다. 중복을 필터링하려면 UNION 연산자를 사용

필수가 아니라면 쿼리에서 DISTINCT는 가능한 한 사용하지 않습니다. 이 원칙은 UNION에도 똑같이 적용됩니다. 꼭 필요한 경우가 아니면 UNION ALL  대신 사용하지 마세요.

 

3.2 연관된 여러 행 결합하기

https://helloworld92.tistory.com/34

INNER JOIN / OUTER JOIN / LEFT OUTER JOIN 차이 및 예제

조인이란 두 테이블의 행을 하나로 결합하는 작업입니다. 

내부 조인(inner join)의 한 형태인 동등 조인(equi-join)

 

3.3 두 테이블의 공통 행 찾기

조인을 수행할 때 올바른 결과를 반환하려면 조인하기에 어떤 열이 적절한지를 고려해야 합니다. 특히 행이 일부 열에 대해서는 공통된 값을 갖지만, 그 외 열에서는 다른 값을 가질 수 있는 경우에 유의하세요. 집합 연산으로 작업할 때 기본적으로 중복 행은 반환하지 않습니다.

 

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

차집합을 수행하는 함수가 있다면 유용합니다. DB2, PostgreSQL, SQL Server 및 Oracle은 모두 자칩합 작업을 지원합니다. DBMS에서 이를 지원하지 않을 때는 MySQL에 표시된 서브쿼리를 사용합니다.

MySQL 해법을 사용할 때 고려할 사항은 중복 행 제거입니다. DEPTNO가 키 필드가 아니라면 다음과 같이 DISTINCT를 사용하여 EMP에서 누락된 각 DEPTNO 값이 한 번만 나오도록 할 수 있습니다.

 

SQL에서 'TRUE or NULL'은 TRUE이지만 'FALSE or NULL'은  NULL 입니다!

 IN 술어를 사용할 때와 논리 OR 평가 및 null 값이 포함될 때 이 점을 염두에 두어야 합니다.

 

NOT IN 및 null 문제를 방지하려면 NOT EXISTS와 함께 서브쿼리를 사용하세요.

 

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

null에 대한 외부 조인(outer join) 및 필터를 사용합니다.

외부 조인 후 일치하지 않는 행만 유지하는 방식으로 작동합니다. 이러한 종류의 작업을 안티 조인(anti join)이라고도 합니다.

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

(scott/ adams 컬럼은 mysql상의 데이터 추가 문제로 넣지 않음)

 

외부 조인을 사용하여 원래 쿼리의 데이터의 손실 없이 추가 정보를 얻을 수 있습니다. 먼저 EMP 테이블을 DEPT 테이블에 조인하여 모든 사원과 그들이 일하는 부서의 위치를 파악한 다음, EMP_BONUS 테이블에 외부 조인하여 보너스 날짜가 있으면 이를 반환합니다. 

 

 

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

select *
    from (
  select e.empno,e.ename,e.job,e.mgr,e.hiredate,
         e.sal,e.comm,e.deptno, count(*) as cnt
    from emp e
   group by empno,ename,job,mgr,hiredate,
           sal,comm,deptno
         ) e
   where not exists (
  select null
    from (
  select v.empno,v.ename,v.job,v.mgr,v.hiredate,
         v.sal,v.comm,v.deptno, count(*) as cnt
    from v
   group by empno,ename,job,mgr,hiredate,
            sal,comm,deptno
         ) v
    where v.empno     = e.empno
       and v.ename    = e.ename
       and v.job      = e.job
       and coalesce(v.mgr,0) = coalesce(e.mgr,0)
       and v.hiredate = e.hiredate
       and v.sal      = e.sal
       and v.deptno   = e.deptno
       and v.cnt      = e.cnt
       and coalesce(v.comm,0) = coalesce(e.comm,0)
  )
     union all
    select *
      from (
    select v.empno,v.ename,v.job,v.mgr,v.hiredate,
           v.sal,v.comm,v.deptno, count(*) as cnt
      from v
    group by empno,ename,job,mgr,hiredate,
             sal,comm,deptno
          ) v
    where not exists (
   select null
     from (
   select e.empno,e.ename,e.job,e.mgr,e.hiredate,
          e.sal,e.comm,e.deptno, count(*) as cnt
     from emp e
    group by empno,ename,job,mgr,hiredate,
             sal,comm,deptno
          ) e
    where v.empno     = e.empno
      and v.ename     = e.ename
      and v.job       = e.job
      and coalesce(v.mgr,0) = coalesce(e.mgr,0)
      and v.hiredate  = e.hiredate
      and v.sal       = e.sal
      and v.deptno    = e.deptno
      and v.cnt       = e.cnt
      and coalesce(v.comm,0) = coalesce(e.comm,0)
  )

상관 서브쿼리 및 UNION ALL을 사용하여 뷰V가 아닌 EMP 테이블의 행과 EMP 테이블이 아닌 뷰 V의 행을 찾습니다.

 

1. EMP 테이블의 행 가운데, 뷰V에 존재하지 않는 행을 찾습니다.

2. EMP 테이블에 없는 뷰 V의 행과 1번 행을 결합(UNION ALL)합니다.

 

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

데카르트 곱(cartesian product)을 피하려면 일반적으로 n-1 규칙을 적용합니다. (n은 FROM 절의 테이블 수를 나타내고 n-1은 데카르트 곱을 피하는 데 필요한 최소 조인 수를 나타냅니다.)

 

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

조인에서 집계를 계산할 때는 주의해야 합니다. 조인 때문에 중복이 반환될 때는 보통 다음과 같은 두 가지 방법으로 집계 함수에 따른 오산을 방지

1. 집계 함수에 대한 호출에서 DISTINCT 키워드를 사용하면 계산할 때 각 값의 고유한 인스턴스만 사용합니다.

2. 조인하기 전에 먼저 (인라인 뷰에서) 집계를 수행하여 집계 함수에 의한 잘못된 계산을 피할 수 있습니다. 결합하기 전에 집계가 이미 계산되어 문제를 사전에 방지할 수 있기 때문입니다.

부서 10의 모든 급여 합계가 먼저 계산되고 그 행을 EMP 테이블에 조인한 다음 EMP_BONUS 테이블에 조인합니다.

 

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

EMP_BONUS에 외부 조인한 다음 부서 10의 각 급여에 대해서만 합계를 수행합니다.

 

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

여러 테이블에서 누락된 데이터를 동시에 반환하려고 합니다. 사원이 없는 부서를 찾고자 DEPT 테이블에서 EMP 테이블에 없는 행을 반환하려면 외부 조인이 필요합니다.

 

OPERATIONS 부서는 해당 부서에 사원이 없는데도 반환됩니다. EMP 테이블이 DEPT 테이블에 외부 조인되었기 때문입니다. 

 

두 테이블에서 누락된 데이터를 반환하기

명시적으로 FULL OUTER JOIN 명령문을 사용하여 일치하는 행과 함께 두 테이블에서 누락된 행을 반환합니다. MySQL에는 아직 FULL OUTER JOIN이 이 없으므로 두 개의 다른 외부 조인의 결과를 UNION 합니다.

 

 

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

COALESCE 함수는 전달된 값 목록에서 null이 아닌 첫 번째 값을 반환합니다. null 값이 발견되면 0으로 대체되고 워드의 커미션과 비교됩니다.

 

3.13 마치며

조인은 데이터베이스 쿼리에서 중요한 부분입니다. 원하는 항목을 찾으려면 보통 두 개 이상의 테이블을 함께 조인합니다.

 

https://github.com/devcys22/SQLCookbook.git

GitHub - devcys22/SQLCookbook: SQL쿡북을 학습합니다.