[MySQL] A 테이블에 있고 B 테이블에 없는 데이터 조회 및 삭제

데이터를 오래 쌓다 보면 부모 쪽 레코드는 지워졌는데 자식 쪽만 남아 있거나, 반대로 연결돼야 할 상대가 없는 고아 데이터가 생깁니다. 외래 키를 걸지 않고 운영하던 테이블이라면 거의 반드시 생기죠. 정리 작업을 하기 전에 먼저 "A에는 있는데 B에는 없는 행"부터 뽑아 봐야 합니다.

짝 없는 행 찾기 - LEFT OUTER JOIN + IS NULL

LEFT OUTER JOIN은 왼쪽 테이블(A)의 행을 전부 살리고, 오른쪽(B)에 맞는 짝이 없으면 B 쪽 컬럼을 NULL로 채웁니다. 그러니 조인 조건에 쓴 B의 컬럼이 NULL인 행만 남기면, 그게 곧 B에 짝이 없는 A의 행입니다. 아래 예는 collection_and_payment의 uid와 balhaengmul의 rUid를 연결했습니다.

SELECT *
FROM `collection_and_payment` A
LEFT OUTER JOIN `balhaengmul` B
ON A.uid=B.rUid
WHERE B.rUid IS NULL;

WHERE 조건에는 B 테이블에서 원래 NULL이 들어갈 수 없는 컬럼을 쓰는 게 안전합니다. 조인 키(rUid)를 쓰면 짝이 있을 때는 값이 반드시 채워져 있으니 헷갈릴 일이 없습니다.

바로 DELETE 하면 왜 오류가 나나

조회가 됐으니 이 결과로 지우면 될 것 같지만, MySQL은 DELETE 대상 테이블을 같은 문장의 서브쿼리 FROM에서 다시 읽는 것을 막습니다. 그대로 WHERE uid IN (SELECT A.uid FROM collection_and_payment A ...) 처럼 쓰면 "You can't specify target table ... for update in FROM clause" 오류(1093)가 납니다.

이걸 피하는 방법이 서브쿼리를 한 번 더 감싸서 임시 테이블(파생 테이블)로 만드는 것입니다. 바깥 SELECT * FROM (...) AS temp_table이 결과를 먼저 다 만들어 두기 때문에, 삭제 대상 테이블을 직접 참조하지 않는 형태가 됩니다. 파생 테이블에는 반드시 별칭(AS temp_table)을 붙여야 한다는 점도 놓치기 쉽습니다.

DELETE FROM `collection_and_payment` WHERE uid IN
(SELECT * FROM
(SELECT A.uid
FROM `collection_and_payment` A
LEFT OUTER JOIN `balhaengmul` B
ON A.uid=B.rUid
WHERE B.rUid IS NULL) AS temp_table);

MySQL에서는 조인을 그대로 쓰는 다중 테이블 DELETE 문법도 됩니다. 아래처럼 DELETE 뒤에 지울 테이블 별칭만 적어 주면 B는 건드리지 않고 A의 행만 지워집니다. 원문 방식과 결과는 같으니 편한 쪽을 쓰면 됩니다.

DELETE A
FROM `collection_and_payment` A
LEFT OUTER JOIN `balhaengmul` B
ON A.uid=B.rUid
WHERE B.rUid IS NULL;

지우기 전에 꼭 하는 것

저는 이런 정리 쿼리는 항상 SELECT로 건수부터 확인하고 나서 DELETE를 돌립니다. 조인 조건을 하나만 잘못 써도 멀쩡한 데이터가 통째로 "짝 없음"으로 잡히기 때문입니다. 운영 DB라면 대상 행을 백업 테이블에 INSERT ... SELECT로 떠 두거나, 트랜잭션을 걸고 삭제 건수를 본 뒤 커밋하는 편이 마음이 편합니다. 양쪽 테이블에 행이 많다면 uid, rUid에 인덱스가 있는지도 확인해 두세요. 인덱스가 없으면 조인 자체가 꽤 오래 걸립니다.