부모 테이블은 지워졌는데 자식 테이블에 데이터가 그대로 남아 있는 경우가 생각보다 자주 생깁니다. 외래키를 걸지 않고 운영하던 테이블이거나, 프로그램에서 삭제 순서를 잘못 처리했을 때 그렇습니다. 여기서는 채팅 기록 테이블 chat의 str_id가 회의 테이블 meeting의 multi_key를 가리킨다고 보고, 회의가 사라진 채팅만 골라 보고 지우는 쿼리를 정리합니다.
짝 없는 행 찾기
LEFT OUTER JOIN은 왼쪽 테이블의 행을 전부 살리고, 오른쪽에서 조건에 맞는 행이 없으면 오른쪽 컬럼을 모두 NULL로 채웁니다. 그러니 조인한 뒤 오른쪽 키가 NULL인 행만 남기면 "A에는 있는데 B에는 없는" 데이터가 됩니다.
SELECT A.str_id
FROM chat A
LEFT OUTER JOIN meeting B
ON A.str_id=B.multi_key
WHERE B.multi_key IS NULL;WHERE 절에 쓰는 컬럼은 오른쪽 테이블에서 원래 NULL이 들어갈 수 없는 컬럼이어야 합니다. 조인 키나 기본키를 쓰면 안전합니다. 만약 B의 일반 컬럼 중에 원래부터 NULL이 들어 있는 컬럼으로 조건을 걸면, 짝이 있는 행까지 섞여 나옵니다.
삭제 전에는 꼭 이 조회부터 돌려서 건수와 내용을 눈으로 확인합니다. 지울 대상이 예상보다 훨씬 많다면 조인 컬럼의 타입이나 콜레이션이 서로 달라서 매칭이 안 되는 건 아닌지 먼저 의심해 보세요.
그대로 DELETE에 넣으면 왜 안 되나
조회가 됐으니 DELETE FROM chat WHERE str_id IN (위 SELECT)로 쓰고 싶어지는데, MySQL은 이걸 거부합니다. 수정·삭제 대상 테이블을 같은 문장의 서브쿼리 FROM에서 다시 읽는 걸 막기 때문이고, 이때 나오는 게 1093번 오류 "You can't specify target table 'chat' for update in FROM clause"입니다.
해결 방법은 서브쿼리를 한 번 더 감싸서 파생 테이블(임시 테이블)로 만드는 것입니다. 바깥쪽 SELECT * FROM (...) AS temp_table이 결과를 먼저 만들어 두기 때문에, MySQL 입장에서는 더 이상 chat을 직접 읽는 게 아니게 됩니다.
DELETE FROM chat WHERE str_id IN
(SELECT * FROM
(SELECT A.str_id
FROM chat A
LEFT OUTER JOIN meeting B
ON A.str_id=B.multi_key
WHERE B.multi_key IS NULL) AS temp_table);파생 테이블에는 AS temp_table처럼 별칭이 반드시 있어야 합니다. 빼먹으면 "Every derived table must have its own alias" 오류가 납니다.
다른 작성 방법
MySQL은 다중 테이블 DELETE도 지원해서, 조인을 바로 삭제문에 쓰는 방법도 있습니다. 동작은 위 쿼리와 같습니다.
DELETE A
FROM chat A
LEFT OUTER JOIN meeting B
ON A.str_id=B.multi_key
WHERE B.multi_key IS NULL;DELETE A처럼 지울 테이블의 별칭을 앞에 적어 줘야 chat쪽만 삭제됩니다. NOT EXISTS로 쓰는 사람도 많은데, 어느 쪽이든 결과는 같으니 읽기 편한 걸 고르면 됩니다.
데이터가 많은 테이블이라면 chat.str_id와 meeting.multi_key 양쪽에 인덱스가 있는지 확인하세요. 인덱스가 없으면 조인이 전체 스캔으로 돌아 한참 걸리고, 그동안 테이블이 잠길 수 있습니다. 운영 DB에서는 삭제 전에 대상 행을 백업 테이블로 INSERT ... SELECT 해 두면 실수했을 때 되돌리기 쉽습니다.