EXISTS 서브쿼리에서 별칭을 빼먹으면 테이블이 통째로 지워진다

운영 서버에서 특정 묶음의 데이터만 지워야 하는 DELETE 가 테이블 전체를 지우고 있었다. 원인은 서브쿼리 안에서 별칭 없이 쓴 열 이름 하나였다. 문법 오류도 경고도 없이 실행되기 때문에 눈으로 찾기 어렵다.

문제의 SQL

예제 테이블은 이렇다. CLOSE_LIST 는 마감 묶음(BATCH_NO)별로 지울 주문 번호를 담고 있고, ORDER_ITEM 에서 그 주문들만 지우려는 것이다.

DELETE
  FROM ORDER_ITEM
 WHERE EXISTS (
        SELECT 1
          FROM CLOSE_LIST B
         WHERE B.BATCH_NO = @BATCH_NO
           AND B.ORDER_ID = ORDER_ID   -- ORDER_ITEM.ORDER_ID 를 뜻했다
       );

의도는 “CLOSE_LIST 에서 이 묶음에 들어 있는 주문만 ORDER_ITEM 에서 지운다” 였다.

원인: 열 이름은 가까운 쪽부터 찾는다

서브쿼리 안에서 테이블을 밝히지 않은 열 이름은 서브쿼리 자신의 FROM 에 있는 테이블부터 찾는다. 거기 없을 때만 바깥 쿼리로 나간다. 이것은 SQL 표준 규칙이라 SQL Server 뿐 아니라 Oracle, MySQL, PostgreSQL 도 같다.

CLOSE_LIST 에도 ORDER_ID 열이 있으므로, 위 조건은 이렇게 해석된다.

AND B.ORDER_ID = B.ORDER_ID   -- ORDER_ID 가 NULL 이 아니면 항상 참

결과적으로 EXISTS 는 “이 묶음에 ORDER_ID 가 NULL 이 아닌 행이 하나라도 있는가” 가 되고, 바깥 행과 상관이 없어진다. 묶음에 행이 있으면 ORDER_ITEM 의 모든 행에 대해 참이 되어 전부 지워진다.

고치는 법

1. 바깥 테이블 이름을 밝힌다

DELETE
  FROM ORDER_ITEM
 WHERE EXISTS (
        SELECT 1
          FROM CLOSE_LIST B
         WHERE B.BATCH_NO = @BATCH_NO
           AND B.ORDER_ID = ORDER_ITEM.ORDER_ID
       );

2. DELETE 대상에 별칭을 준다 (SQL Server 문법)

Oracle 은 DELETE FROM ORDER_ITEM A WHERE ... 처럼 대상 테이블에 바로 별칭을 붙일 수 있지만, SQL Server 에서는 이렇게 쓰면 문법 오류가 난다. SQL Server 는 DELETE 다음에 별칭을 쓰고, 그 별칭을 두 번째 FROM 에서 정의한다. UPDATE 도 같다.

DELETE A
  FROM ORDER_ITEM AS A
 WHERE EXISTS (
        SELECT 1
          FROM CLOSE_LIST B
         WHERE B.BATCH_NO = @BATCH_NO
           AND B.ORDER_ID = A.ORDER_ID
       );

-- UPDATE 도 같은 모양
UPDATE A
   SET A.STATUS = 'CLOSED'
  FROM ORDER_ITEM AS A
 WHERE EXISTS (
        SELECT 1
          FROM CLOSE_LIST B
         WHERE B.BATCH_NO = @BATCH_NO
           AND B.ORDER_ID = A.ORDER_ID
       );

모든 열에 별칭을 붙이는 습관을 들이면 이번 같은 사고는 생기지 않는다. 별칭을 붙인 열이 그 테이블에 없으면 바로 오류가 나기 때문이다.

3. JOIN 으로 쓴다

SQL Server 는 DELETE ... FROM ... JOIN 도 지원한다. 대상 행이 여러 번 매칭되어도 한 번만 지워진다.

DELETE A
  FROM ORDER_ITEM AS A
  JOIN CLOSE_LIST AS B
    ON B.ORDER_ID = A.ORDER_ID
 WHERE B.BATCH_NO = @BATCH_NO;

IN 에도 같은 함정이 있다

열 이름을 잘못 써도 오류가 나지 않는 경우가 IN 에서도 생긴다. 예를 들어 CLOSE_LIST 의 열 이름이 실제로는 ORD_ID 인데 이렇게 썼다고 하자.

DELETE
  FROM ORDER_ITEM
 WHERE ORDER_ID IN (SELECT ORDER_ID          -- CLOSE_LIST 에는 ORDER_ID 가 없다
                      FROM CLOSE_LIST
                     WHERE BATCH_NO = @BATCH_NO);

CLOSE_LIST 에 ORDER_ID 가 없으니 서브쿼리의 ORDER_ID 는 바깥 ORDER_ITEM.ORDER_ID 로 해석된다. 서브쿼리 단독으로 실행하면 “열 이름이 잘못되었다” 는 오류가 나는데, 바깥 쿼리와 같이 실행하면 오류 없이 돌아가고 이 묶음에 행이 있으면 역시 전체가 지워진다. 여기서도 SELECT B.ORDER_ID FROM CLOSE_LIST B 처럼 별칭을 붙였다면 바로 오류가 났을 것이다.

NOT IN 과 NULL

반대 방향(목록에 없는 행 지우기)은 NOT IN 보다 NOT EXISTS 가 안전하다.

-- 위험: 서브쿼리 결과에 NULL 이 하나라도 있으면 한 건도 안 걸린다
DELETE A
  FROM ORDER_ITEM AS A
 WHERE A.ORDER_ID NOT IN (SELECT B.ORDER_ID FROM CLOSE_LIST B);

-- 안전
DELETE A
  FROM ORDER_ITEM AS A
 WHERE NOT EXISTS (SELECT 1
                     FROM CLOSE_LIST B
                    WHERE B.ORDER_ID = A.ORDER_ID);

X NOT IN (1, 2, NULL) 은 X <> 1 AND X <> 2 AND X <> NULL 이고, 마지막 비교가 UNKNOWN 이라 전체가 참이 될 수 없다(기본값인 ANSI_NULLS ON 기준). 이번에는 아무것도 안 지워지는 쪽이라 덜 위험해 보이지만, 같은 패턴을 SELECT 에 쓰면 “누락된 데이터가 없다” 는 잘못된 결론을 내리게 된다. NOT EXISTS 는 NULL 과 비교한 행을 “일치하지 않음” 으로 처리해서 의도대로 동작한다.

운영 데이터를 지우기 전에

이번 일 뒤로 운영 DB 에서 DELETE/UPDATE 를 할 때는 이렇게 한다.

BEGIN TRAN;

DELETE A
OUTPUT deleted.ORDER_ID           -- 지워지는 행을 눈으로 확인
  FROM ORDER_ITEM AS A
 WHERE EXISTS (
        SELECT 1
          FROM CLOSE_LIST B
         WHERE B.BATCH_NO = @BATCH_NO
           AND B.ORDER_ID = A.ORDER_ID
       );

SELECT @@ROWCOUNT AS DELETED_ROWS; -- 예상 건수와 비교

-- 맞으면 COMMIT, 아니면 ROLLBACK
-- COMMIT;
-- ROLLBACK;

같은 WHERE 를 먼저 SELECT COUNT(*) 로 돌려 건수를 본다
트랜잭션을 연 채로 오래 두면 잠금이 걸리니 확인은 빠르게 끝낸다
서브쿼리 안의 모든 열에 테이블 별칭을 붙인다

다시 쓰며 바로잡은 점

열 이름이 서브쿼리 쪽으로 해석되는 것은 SQL Server 만의 동작이 아니라 SQL 표준 규칙임을 밝혔다
SQL Server 에서 DELETE/UPDATE 대상에 별칭을 쓰는 정식 방법(DELETE A FROM ORDER_ITEM AS A)을 추가했다. 원래 글은 테이블 이름을 직접 쓰는 방법만 적었다
같은 원리로 생기는 IN 서브쿼리 함정과 NOT IN 의 NULL 문제를 덧붙였다