[ 쿼리 ] Advanced SQL for Data Professionals (3) - Finding New Records or Records that Don't Exist

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

Advanced SQL for Data Professionals

---

이번 포스트에서 정리할 Advanced SQL 케이스는 Finding New Records or Records that Don't Exist이다.

2개의 데이터셋 또는 테이블을 비교해 한쪽에는 있고 다른 쪽에는 없는 데이터를 찾아내는 방법이라고 생각하면 될 것 같다.

 

다음과 같은 방법을 소개한다.

- EXISTS 활용 Finding New Records or Records that Don't Exist

- LEFT JOIN 활용 Finding New Records or Records that Don't Exist

 

예시 데이터를 생성하고 시작한다.

CREATE TABLE 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);

CREATE TABLE raw_Employees
(
  EmployeeID int,
  FirstName varchar(20),
  LastName varchar(20),
  ManagerID int 
);
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', 3);
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);
INSERT INTO raw_Employees VALUES (11, 'Avery', 'Sinclair', 6);
INSERT INTO raw_Employees VALUES (12, 'Oliver', 'Beckett', 6);

 

입력된 데이터를 보면 Employees 테이블에는 10건의 데이터가 있고 raw_Employees 테이블에는 동일한 데이터들에 2건의 데이터가 추가되어있다.

---

[ EXISTS 활용 Finding New Records or Records that Don't Exist ] 

SELECT *
FROM raw_Employees a 
WHERE NOT EXISTS (
	SELECT 1 
	FROM Employees b
	WHERE a.EmployeeID = b.EmployeeID
);

 

쿼리를 실행하면 오른쪽과 같은 결과가 나온다.

 

 

raw_Employees 테이블에는 있지만 Employees 테이블에는 없는 데이터가 조회된 상황이다.

EXISTS 뒤쪽에 쓰인 쿼리에서 raw_Employees 테이블의 EmployeeID 값을 받아다가 Employees 테이블의 EmployeeID 값과 비교해서 같으면 1, 같지 않으면 NULL을 출력(아마도?) 하는 상황 같다.

EXISTS 앞에 NOT이 붙어서 raw_Employees 테이블의 EmployeeID 값 중에 Employees 테이블의 EmployeeID 값과 같지 않은 데이터 2건이 참으로 조회 대상이 된다.

 

EXISTS의 문법과 쓰임세가 IN과 유사한 것 같다. IN도 NOT IN과 같이 쓰일 수 있다.

IN의 뒤쪽에는 SELECT 결과 또는 직접 써준 값의 리스트가 들어가서 어떤 필드의 값이 IN 뒤쪽에 쓰인 값들 중에 있는지 체크한다.

위의 쿼리를 IN으로 바꿔서 사용해보면 다음과 같이 쓸 수 있다. 조회 결과는 위의 쿼리 결과와 동일하다.

SELECT *
FROM raw_Employees a 
WHERE a.EmployeeID NOT IN (
	SELECT b.EmployeeID
	FROM Employees b
);

 

어떤 쿼리가 더 효율적인지는 모르겠지만 지금 하는 실무에서는 더 직관적으로 이해되는 IN을 더 많이 쓰는 편이다.

---

[ LEFT JOIN 활용 Finding New Records or Records that Don't Exist ] 

SELECT a.*
FROM raw_Employees a 
LEFT JOIN Employees b 
ON a.EmployeeID = b.EmployeeID
WHERE b.EmployeeID IS NULL;

위 쿼리의 결과도 이전 케이스의 조회 결과와 같다.

 

LEFT JOIN을 활용한 케이스는 지금 하는 실무에서 정말 많이 사용하고 있다.

지금 케이스처럼 한쪽 테이블에만 없는 데이터를 찾을 때도 많이 쓰이고 서로 다른 테이블 사이에 겹치는 데이터를 찾을 때도 쓰인다. 이렇게 쓰일 때 기준이 되는 테이블을 왼쪽에 두고 LEFT JOIN 해서 JOIN한 필드가 IS NOT NULL인 데이터를 찾으면 INNER JOIN 보다 시간이 덜 걸린다.

INNER JOIN은 로그 데이터처럼 1:1의 경우가 아닐 가능성이 있을 때 쓰기에 부담스러운 면이 있다.

---

 

참고한 Medium 아티클은 이후 앞서 설명한 케이스에서 NOT EXISTS를 EXISTS로 바꿔서 사용하면 두 테이블 사이에 겹치는 데이터를 조회할 수 있다는 것을 설명하는데 굳이 싶어서 여기에는 따로 정리하지 않는다.

 

다음 포스트에서는 Filling Gaps in Data Using SQL을 정리한다.