[ 쿼리 ] Advanced SQL for Data Professionals (10) - Checking if Tables are The Same

이 포스트는 Medium에 올라온 다음 아티클을 참고하여 정리한 글입니다.

Advanced SQL for Data Professionals

---

이번 포스트에서 정리할 Advanced SQL 케이스는 Checking if Tables are The Same 이다.

 

데이터를 다루다보면, 특히 테스팅 또는 디버깅 코드를 다루는 경우 테이블들을 비교해야 하는 일이 자주 있다.

작은 테이블이라면 바로 할 수 있겠지만 100개가 넘는 필드를 가진 테이블들을 비교하는 것은 쉽지 않다.

이럴 경우 EXCEPT 기능이 도움이 될 수 있다.

 

먼저 데이터를 입력한다.

CREATE TABLE Employees
(
  EmployeeID int,
  FirstName varchar(20),
  LastName varchar(20),
  ManagerID int 
);

CREATE TABLE raw_Employees
(
  EmployeeID int,
  FirstName varchar(20),
  LastName varchar(20),
  ManagerID int 
);

INSERT INTO Employees VALUES (1, 'Harper', 'Westbrook', NULL);
INSERT INTO Employees VALUES (2, 'Liam', 'Carrington', 1);
INSERT INTO Employees VALUES (3, 'Evelyn', 'Radcliffe', 1);
INSERT INTO Employees VALUES (4, 'Mason', 'Albright', 2);
INSERT INTO Employees VALUES (5, 'Isla', 'Whitman', 2);
INSERT INTO Employees VALUES (6, 'Noah', 'Sterling', 3);
INSERT INTO Employees VALUES (7, 'Ruby', 'Lennox', 3);
INSERT INTO Employees VALUES (8, 'Caleb', 'Winslow', 5);
INSERT INTO Employees VALUES (9, 'Avery', 'Sinclair', 6);
INSERT INTO Employees VALUES (10, 'Oliver', 'Beckett', 6);

INSERT INTO raw_Employees VALUES (1, 'Harper', 'Westbrook', NULL);
INSERT INTO raw_Employees VALUES (2, 'Liam', 'Carrington', 1);
INSERT INTO raw_Employees VALUES (3, 'Evelyn', 'Radcliffe', 1);
INSERT INTO raw_Employees VALUES (4, 'Mason', 'Albright', 2);
INSERT INTO raw_Employees VALUES (5, 'Isla', 'Whitman', 2);
INSERT INTO raw_Employees VALUES (6, 'Noah', 'Sterling', 3);
INSERT INTO raw_Employees VALUES (7, 'Ruby', 'Lennox', null);
INSERT INTO raw_Employees VALUES (8, 'Caleb', 'Winslow', 5);
INSERT INTO raw_Employees VALUES (9, 'Avery', 'Sinclair', 6);
INSERT INTO raw_Employees VALUES (10, 'Oliver', 'Beckett', 6);

 

거의 동일하지만 EmployeeID가 7인 데이터의 ManagerID만 다른 두개의 테이블 Employees와 raw_Employees를 준비했다.

# 이 쿼리는 MySQL에서 실행되지 않는다
SELECT * FROM Employees
EXCEPT
SELECT * FROM raw_Employees;

 

이번에도 어떤 엔진에서 사용되는 문법인지 설명은 되어있지 않지만 쿼리의 결과가 오른쪽 이미지와 같이 나온다고 한다.

 

MySQL에서는 위에서 설명된 EXCEPT 기능이 지원되지 않는다.

위의 EXCEPT는 차집합을 구하는 기능이기 때문에 MySQL에서는 LEFT JOIN으로 다음 쿼리로 동일한 기능을 구현할 수 있다.

SELECT a.EmployeeID, a.FirstName, a.LastName, a.ManagerID
FROM Employees AS a
LEFT JOIN raw_Employees AS b
ON a.EmployeeID=b.EmployeeID
WHERE (b.EmployeeID IS NULL
OR IFNULL(a.FirstName,999) <> IFNULL(b.FirstName,999)
OR IFNULL(a.LastName,999) <> IFNULL(b.LastName,999)
OR IFNULL(a.ManagerID,999) <> IFNULL(b.ManagerID,999)
);

 

꽤 지저분한 쿼리다.

더 좋은 방법이 있을 것 같지만 일단은 이렇다.

MySQL에서 EXCEPT 기능 관련해서 검색 해봤지만 차집합이라고 생각해서 A테이블에는 있지만 B테이블에는 없는 경우만 생각해서 정리해놓은 글들이 많았다.

위에서 EXCEPT가 수행한 기능은 A에도 있고 B에도 있는 ID의 데이터지만 한 필드값만 다른 데이터 찾기다.

이와 동일한 결과를 내려면 OR로 모든 필드값을 비교해야 하고 <>만으로 비교하면 한쪽 테이블에 필드값이 있지만 다른 테이블에는 해당 필드의 값이 NULL인 경우가 찾아지지 않아서 IFNULL까지 써야 한다.

---

 

여기까지 10개나 포스팅 할 줄은 몰랐던 Medium 아티클 정리 글을 마친다.