LeetCode 196. Delete Duplicate Emails SQL Solution

Problem

LeetCode SQL Problem

196. Delete Duplicate Emails

Person table

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com
4	emily@example.com
5	phil@example.com
6	bob@example.com

Solution - Group By + Common Table Expression

MySQL

Delete Duplicate Emails

-- Generate a KEEP list
-- When there are duplicate emails, we'll keep the record with the minimum Id value
WITH IDsToKeep
AS (
	SELECT Min(Id) AS Id
	FROM Person
	GROUP BY Email
	)
-- Delete records with Id not in the KEEP list
DELETE
FROM Person
WHERE Id NOT IN (
		SELECT Id
		FROM IDsToKeep
		)

TSQL

Delete Duplicate Emails

-- Generate a KEEP list
-- When there are duplicate emails, we'll keep the record with the minimum Id value
WITH IDsToKeep
AS (
	SELECT Min(Id) AS Id
	FROM Person
	GROUP BY Email
	)
-- Delete records with Id not in the KEEP list
DELETE
FROM Person
WHERE Id NOT IN (
		SELECT Id
		FROM IDsToKeep
		)

Query Output

Id	Email
1	john@example.com
2	bob@example.com
4	emily@example.com
5	phil@example.com

Solution - Self Join

MySQL

Delete Duplicate Emails

-- Use Self Join to detect duplicate Person Ids that have duplicat emails.
-- When there are duplicate emails, we'll keep the record with the minimum Id value
DELETE P1
FROM Person AS P1
INNER JOIN Person AS P2 ON P1.Id > P2.Id
	AND P1.Email = P2.Email

TSQL

Delete Duplicate Emails

-- Use Self Join to detect duplicate Person Ids that have duplicat emails.
-- When there are duplicate emails, we'll keep the record with the minimum Id value
DELETE P1
FROM Person AS P1
INNER JOIN Person AS P2 ON P1.Id > P2.Id
	AND P1.Email = P2.Email

Query Output

Id	Email
1	john@example.com
2	bob@example.com
4	emily@example.com
5	phil@example.com