[MySQL] 같은 컬럼을 가진 두 테이블 비교 업데이트

kihongsi·2022년 5월 8일
0

DB

목록 보기
3/3

동일한 필드를 가진 두 테이블을 비교하여 달라진 점을 변경하기 위해 CTE를 사용했다.

CTE(Common Table Expression)

서브쿼리로 쓰이는 파생테이블과 비슷한 개념
주로 복잡한 쿼리문에서 코드의 가독성과 재사용성에 유용함
(MySQL 8부터 지원)

사용법

WITH 테이블명 AS (쿼리문 ex) SELECT ...)

CTE를 만들 때 자기 자신을 참조하여 재귀 CTE로 사용할 수도 있음(계층적 구조에 유용)


두 테이블 비교하기

필드가 서로 같은 t1 테이블과 t2 테이블이 있다.
기존의 t1 데이터와 t2 데이터를 비교하여 변경된 데이터가 있을 시 (삭제, 추가된 데이터 제외) 이를 t1 테이블에 반영해야 한다.

WITH cte1 AS
(
	SELECT *
	FROM (
		SELECT *
		FROM t1
		UNION ALL
		SELECT *
		FROM t2
	) tmp
	GROUP BY unique_key, info1, info2, ...
	HAVING count(*)=1
)

서브쿼리를 사용해 두 테이블의 모든 데이터를 union all 키워드로 묶어준 후, 이 데이터에서 group byhaving count(*)=1구문을 사용해 중복된 데이터를 제거한다.
이렇게 되면 두 테이블 간의 동일한 데이터는 사라지고 unique한 데이터셋만 남게 된다.
이러면 현재 cte1 테이블에 t1 데이터와 t2 데이터가 둘다 있는 상태이다.

WITH cte2 AS
(
	SELECT DISTINCT t2.*
    FROM t2, cte1
    WHERE t2.unique_key=cte1.unique_key
)

cte1 테이블에서 t2 데이터만 추출하여 cte2로 만들어준다.

UPDATE t1 a
INNER JOIN t2 b
ON a.unique_key = b.unique_key
SET a.info1 = b.info1,
	a.info2 = b.info2,
    a.info3 = b.info3,
    ...

이 cte2를 바탕으로 t1 테이블의 정보를 업데이트해주어야 하기 때문에 특정 키를 기준으로 inner join 해준 후, 정보를 업데이트 하였다.
이렇게 하면 기존 t1 테이블에 존재했던 데이터 중 정보가 변경된 데이터만 골라 업데이트시킬 수 있다.

이렇게 어찌저찌 구현하긴 했지만 더 간단한 방법이 있을 것 같다.


서브쿼리 vs. CTE

1. CTE는 재귀적 호출이 가능함
직급, 계급 등과 같은 계층적 구조를 나타낼 때 자주 쓰인다.
(아직 안써봄)

2. CTE는 재사용이 가능함
한 쿼리에서 서브쿼리 대신 CTE로 만들어두면 여러 쿼리에서 이를 재사용할 수 있다. 따라서 같은 서브쿼리를 여러번 사용해야 하는 경우 CTE로 바꿔쓰면 좋다.

3. CTE는 서브쿼리보다 가독성이 좋음
코드가 복잡해질수록, 서브쿼리가 많아질수록 sql 코드를 이해하기 힘든데, 이 때 CTE를 사용하여 가독성을 높일 수 있다.

but, 서브쿼리가 필수적인 때도 있고, 몇몇 언어에서는(ex. MySQL8...) CTE를 캐시에 저장해 오히려 성능이 떨어질 수도 있다고 하므로 적절히 사용해야 할 것.



참고자료

Rewriting Subqueries as Joins
Subquery vs. CTE: A SQL Primer

0개의 댓글