[MySQL] Leetcode 196 - DELETE문을 사용하여 중복값 삭제하기

문제: 중복된 이메일을 삭제하고 중복된 이메일 중 가장 작은 id를 가지는 이메일만 남겨라

         = 중복된 이메일들 중 가장 작은 id만 제외하고 삭제

 

3가지 풀이:

[1] WHERE절에 조건걸기

DELETE p1
FROM Person p1, Person p2
WHERE p1.email = p2.email 
AND p1.id > p2.id

Person 테이블

id	email
1	john@example.com
2	bob@example.com
3	john@example.com

 

WHERE절(p1.email = p2.email)을 적용한 테이블 모양

p1.id	p1.email	p2.id	p2.email
3	john@example.com	1	john@example.com
1	john@example.com	1	john@example.com
2	bob@example.com	2	bob@example.com
3	john@example.com	3	john@example.com
1	john@example.com	3	john@example.com

 

여기서 'DELETE p1'은

WHERE절의 조건에 해당하는 행을 p1 테이블에서 삭제한다

 

여기서 조건은 p1.email = p2.email 이면서 p1.id > p2.id 이다.

조건을 그렇게 건 이유는

중복된 이메일은 n개의 id값을 가지기 때문에 같은 이메일값끼리 id 대소 비교를 할 수 있다.

하지만 중복되지 않은 단일 값의 이메일은 id값이 1개이기 때문에

같은 이메일은 id역시 같은 값을 가지기 때문에 대소 비교가 불가능하다.

 

목적은 중복된 값을 삭제하면서 최솟값의 id를 가지는 이메일을 남기는 것이기 때문에

'p1.email = p2.email 이면서 p1.id > p2.id' 조건을 적용함으로써

중복된 이메일 값들에 대해 최소 id를 제외한 다른 값들을 추려낼 수 있다.

 

따라서 'DELETE p1'은

WHERE절이 적용된 아래의 테이블에서 빨간색 행을 p1에서 삭제한다

 

p1.id	p1.email	p2.id	p2.email
3	john@example.com	1	john@example.com
1	john@example.com	1	john@example.com
2	bob@example.com	2	bob@example.com
3	john@example.com	3	john@example.com
1	john@example.com	3	john@example.com

 

삭제 후 Person 테이블은 아래와 같다

id	email
1	john@example.com
2	bob@example.com

목적 달성ㅇ!

 

+) p2가 아닌 p1을 삭제하는 이유

p2를 쓰고 싶다면 조건에서 부등호 방향이 반대가 되어야 한다.

 

+) p1, p2 둘 다가 아닌 p1만을 삭제하는 이유

만약 'DELETE p1, p2'라면

WHERE절의 조건에 해당하는 행을 p1, p2 테이블에서 삭제한다

p1.id	p1.email	p2.id	p2.email
3	john@example.com	1	john@example.com
1	john@example.com	1	john@example.com
2	bob@example.com	2	bob@example.com
3	john@example.com	3	john@example.com
1	john@example.com	3	john@example.com

위와 같은 행이 삭제되는데,

p1, p2 둘 다 같은 테이블에서 삭제가 이뤄지므로 Person 테이블은 아래와 같아진다

id	email
2	bob@example.com

그러니까 중복값이 없는 값만 남게 되는 것이다.

 

[2] 서브쿼리

[1]번과 동일한 조건을 WHERE절에 걸고 그 결과를 FROM절에 넣어서 테이블처럼 가져다 쓰는 방법이다

DELETE FROM Person
WHERE id IN(
    SELECT * 
    FROM(
        SELECT p1.id 
        FROM Person AS p1, Person AS p2
        WHERE p1.email = p2.email
        AND p1.id > p2.id 
    ) AS t
)

 

만약에 SELECT * FROM() 안에 안 넣고

바로 아래처럼 바로 IN() 안에 넣으면

DELETE FROM Person
WHERE id IN(
    SELECT p1.id 
    FROM Person AS p1, Person AS p2 
    WHERE p1.Email = p2.Email
    AND p1.id > p2.id 
)

'You can't specify target table 'Person' for update in FROM clause'라는 에러가 발생한다

 

이유를 알아보니

MySQL은 UPDATE 나 DELETE을 할 때 자기 테이블의 데이터를 바로 사용하지 못한다고 한다

(오라클에서는 동작한다고 함)

그래서 SELECT * FROM()을 씌워서 사용해야 한다

 

IN()이 아닌

NOT IN()으로 표현하면 아래와 같다

'WHERE p1.email = p2.email AND p1.id > p2.id'이라는 조건을 생각해내지 않아도 되기 때문에 zz

이게 제일 직관적이고 쉬운 방법인 듯

DELETE FROM Person
WHERE id NOT IN (
    SELECT p.del_id
    FROM(
        SELECT email
             , MIN(id) AS del_id
        FROM Person
        GROUP BY email
    ) AS p
)

 

IN()에서는

WHERE절에 삭제할 데이터의 조건을 넣었다면

 

NOT IN()에서는

남길 데이터를 제외한 값을 삭제해 버리는 것이다

 

이 문제에서는 중복값 중에서 최솟값을 가지는 id만을 남기기 때문에 

최소값을 가지는 id를 간편하게 MIN(id)로 표현할 수 있다

그리고 MIN() 값을 제외한 값을 제거하면 중복값이 전부 삭제된다

 

[3] 조인

[1]번과 유사하다. 차이점이라면

'WHERE p1.email = p2.email AND p1.id > p2.id'에서 

'p1.email = p2.email'을 셀프 이너조인으로 표현하고

WHERE절에는 'p1.id > p2.id'만 남기는 것이다.

DELETE p1
FROM Person p1
	 INNER JOIN Person p2 ON p1.email = p2.email
WHERE p1.id > p2.id

 

note:

MySQL에서 테이블명은 대소문자를 구분하지만 컬럼명은 대소문자를 구분하지 않는다

 

 

문제 출처:

https://leetcode.com/problems/delete-duplicate-emails/

Delete Duplicate Emails - LeetCode

 

참고:

[1] https://leetcode.com/problems/delete-duplicate-emails/solutions/55553/simple-solution/

 

본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.