SQL Cook: 3. 7장 두 테이블에 같은 데이터가 있는지 확인하기(MariaDB)

Chappie: 코딩 좋아하는 사람·2022년 7월 12일
UNION ALLsql 차집합상관 서브쿼리
0

SQL_COOK

목록 보기
17/35
post-thumbnail

3.7장을 이해하는 데에 어려움을 많이 겪었는데, 집합으로 아이디어를 떠올려보니 생각보다 편하게 이해할 수 있었다.
SQL로 옮기기도 편하다면 얼마나 좋을까...

3. 7장 두 테이블에 같은 데이터가 있는지 확인하기(MariaDB)

Oracle에서는 집합 연산 MINUS와 UNION ALL을 사용하여 해답을 냈다.

MariaDB에서는 상관 서브쿼리 및 UNION ALL을 사용하여 다음을 찾아낸다.

  1. 뷰 V_CARD가 아닌 'EMP' 테이블 인라인 뷰 'EMP'의 행
  2. 'EMP' 테이블 인라인 뷰 'EMP'에 없는 뷰 'V_CARD'의 행

📌 상관 서브쿼리란?
서브쿼리와 메인쿼리가 서로 참조하는 쿼리를 말한다.
서브쿼리에서 메인쿼리의 칼럼을 이용하는 경우다.

일반적으로 쿼리는 서브쿼리의 결과를 메인쿼리에서 단순히 이용만 한다.

상관 서브쿼리에서는 서브쿼리가 메인쿼리의 값을 이용하여 값을 구해놓으면
그 값을 다시 메인쿼리에서 이용한다.

이 때 서브쿼리는 값을 확인하는 확인자 역할을 하게 된다.

📌 상관 서브쿼리의 실행 순서

  1. 바깥쪽 메인쿼리를 실행하여 행을 하나 읽는다
  2. 읽은 행의 값을 이용, 서브쿼리에 필요한 값을 넣고 서브쿼리를 수행한다
  3. 2의 결과로 메인 쿼리의 WHERE절을 평가 -> 읽은 행의 선택 여부 결정
  4. 다음으로 넘어간다

메인 쿼리에 남은 행이 없을 때까지 반복수행한다
정말 잘 설명되어 있는 곳 링크


쿼리 시작!

# 외부쿼리 영역: 서브쿼리로부터 모든 열을 반환한다
select *
	from (	# 인라인 뷰 'EMP'의 모든 칼럼을 조회하여 그룹화했다 
    		# cnt라는 열이 추가된 것 이외엔 동일하다
            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절의 조건이 존재하지 않는 자료에 한한다
    where not exists (
    			# 서브쿼리로부터 가져오는 칼럼이 NULL이어야 한다?
    			select null 
            		from (
                    	# 뷰 V 에서도 모든 칼럼을 조회하여 그룹화했다
                        # cnt라는 열이 추가된 것 이외엔 동일하다
            			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)
         ) # coalesce 함수는 해당 칼럼의 NULL값을 두 번째 파라미터로 대체한다

코드가 무진장 엄청나게 길다.
하지만 여기까지가 딱 절반이다.
...
그리고 전체적으로 보면 볼 만하다.
천천히 해석해본다.

  • 외부쿼리

    • SELECT * FROM (서브쿼리1) WHERE not exists { 서브쿼리2 (서브쿼리3) }

      • 서브쿼리1
        'EMP' 테이블 인라인 뷰 'EMP'를 각 칼럼별로 그룹화하여 count를 추가했다
        이 인라인 뷰의 이름은 e로 정한다

      • 서브쿼리3
        뷰 'V'로부터 서브쿼리1과 동일한 방법으로, 각 컬럼별로 그룹화하여 count를 추가했다

      • 서브쿼리2
        서브쿼리3의 결과셋에서 WHERE절의 조건에 의해 NULL값이 되는 행을 가져온다


결과셋으로 구경하면 조금 더 낫다

# 서브쿼리1 결과셋
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;
>>
+-------+--------+-----------+------+------------+------+------+--------+-----+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | cnt |
+-------+--------+-----------+------+------------+------+------+--------+-----+
|  7369 | SMITH  | CLERK     | 7902 | 2005-12-17 |  800 | NULL |     20 |   1 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2006-02-20 | 1600 |  300 |     30 |   1 |
|  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |   1 |
|  7566 | JONES  | MANAGER   | 7839 | 2006-04-02 | 2975 | NULL |     20 |   1 |
|  7654 | MARTIN | SALESMAN  | 7698 | 2006-09-28 | 1250 | 1400 |     30 |   1 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2006-05-01 | 2850 | NULL |     30 |   1 |
|  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |   1 |
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |   1 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2007-12-09 | 3000 | NULL |     20 |   1 |
|  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |   1 |
|  7844 | TURNER | SALESMAN  | 7698 | 2006-09-08 | 1500 |    0 |     30 |   1 |
|  7876 | ADAMS  | CLERK     | 7788 | 2008-01-12 | 1100 | NULL |     20 |   1 |
|  7900 | JAMES  | CLERK     | 7698 | 2006-12-03 |  950 |    0 |     30 |   1 |
|  7902 | FORD   | ANALYST   | 7566 | 2006-12-03 | 3000 | NULL |     20 |   1 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |   1 |
+-------+--------+-----------+------+------------+------+------+--------+-----+

기존 'EMP' 테이블 인라인 뷰 'EMP'에 'CNT'열이 추가되었을 뿐이다.

# 서브쿼리3 결과셋
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
+-------+--------+----------+------+------------+------+------+--------+-----+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno | cnt |
+-------+--------+----------+------+------------+------+------+--------+-----+
|  7369 | SMITH  | CLERK    | 7902 | 2005-12-17 |  800 | NULL |     20 |   1 |
|  7499 | ALLEN  | SALESMAN | 7698 | 2006-02-20 | 1600 |  300 |     30 |   1 |
|  7521 | WARD   | SALESMAN | 7698 | 2006-02-22 | 1250 |  500 |     30 |   2 |
|  7566 | JONES  | MANAGER  | 7839 | 2006-04-02 | 2975 | NULL |     20 |   1 |
|  7654 | MARTIN | SALESMAN | 7698 | 2006-09-28 | 1250 | 1400 |     30 |   1 |
|  7698 | BLAKE  | MANAGER  | 7839 | 2006-05-01 | 2850 | NULL |     30 |   1 |
|  7788 | SCOTT  | ANALYST  | 7566 | 2007-12-09 | 3000 | NULL |     20 |   1 |
|  7844 | TURNER | SALESMAN | 7698 | 2006-09-08 | 1500 |    0 |     30 |   1 |
|  7876 | ADAMS  | CLERK    | 7788 | 2008-01-12 | 1100 | NULL |     20 |   1 |
|  7900 | JAMES  | CLERK    | 7698 | 2006-12-03 |  950 |    0 |     30 |   1 |
|  7902 | FORD   | ANALYST  | 7566 | 2006-12-03 | 3000 | NULL |     20 |   1 |
+-------+--------+----------+------+------------+------+------+--------+-----+

뷰 'V'에도 'CNT'가 추가되었을 뿐 큰 변화는 없다.

  • 서브쿼리2의 결과 해석
    • 서브쿼리1의 결과셋과 서브쿼리3의 결과셋에서, 'CNT'를 제외한 모든 칼럼의 값이 같은 경우가 아니라면('NULL'이라면) 그 행을 반환한다
      • 서브쿼리3의 결과셋에는 'DEPTNO'의 값이 10인 행이 없는 것을 볼 수 있다
        따라서 서브쿼리1의 결과셋 중, 'DEPTNO'의 값이 10인 행은 모두 반환될 것이다
      • 서브쿼리3의 결과셋에는 'CNT'의 값이 2인 행을 볼 수 있다
        이는 서브쿼리1의 결과셋에서는 볼 수 없는 행이다
        따라서 서브쿼리1의 결과셋 중, 'CNT'의 값이 1인 행이 반환될 것이다(FROM절은 서브쿼리1의 결과셋을 대상으로 하고 있다)
>>
+-------+--------+-----------+------+------------+------+------+--------+-----+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | cnt |
+-------+--------+-----------+------+------------+------+------+--------+-----+
|  7521 | WARD   | SALESMAN  | 7698 | 2006-02-22 | 1250 |  500 |     30 |   1 |
|  7777 | IREN   | TESTER    |    0 | 2022-07-03 | 9000 | 9000 |     10 |   1 |
|  7782 | CLARK  | MANAGER   | 7698 | 2006-06-09 | 2450 | NULL |     10 |   1 |
|  7839 | KING   | PRESIDENT | NULL | 2006-11-17 | 5000 | NULL |     10 |   1 |
|  7934 | MILLER | CLERK     | 7782 | 2007-01-23 | 1300 | NULL |     10 |   1 |
+-------+--------+-----------+------+------------+------+------+--------+-----+

그렇게 반환되었다.
여기까지가 코드의 중간 부분이다.

이제 여기에 UNION ALL을 통해 서로 정반대의 쿼리를 통해 결과셋을 불러오고 쌓아준다.

전체적 해법은 동일하다

  1. 'EMP' 테이블 인라인 뷰 'EMP'의 행 가운데, 뷰 'V'에는 존재하지 않는 행을 찾아낸다
  2. 뷰 'V'의 행 가운데, 'EMP' 테이블 인라인 뷰 'EMP'에는 없는 행을 찾아내고 1번 결과와 결합한다

쉽게 설명해보자면 서로 공통되는 행은 각자 빼고, 각각 남은 행을 합치는 원리다.
순수 쿼리로 하니까 뒤지게도 늘어지는 것을 볼 수 있다.

어떻게 이런 해법을 생각해내는지 정말 궁금하다.
천재들인가?

이번 포스팅의 볼륨은 적지만 읽느라 엄청 고생했다
전반적인 그림을 보는 눈을 키워야겠다.

다음 포스팅에서 반대 상황의 쿼리를 해석해보고
3. 7장을 정리하도록 해야겠다.

오래도 끌고 있다...

profile
Chappie: 코딩 좋아하는 사람
Can do this all day
이전 포스트

SQL Cook: 3. 7장 두 테이블에 같은 데이터가 있는지 확인하기(Oracle)

다음 포스트

SQL Cook: 3. 7장 두 테이블에 같은 데이터가 있는지 확인하기(종합)

0개의 댓글