With a self-join, how do you find every row in users that shares an email with another row?

## Two different questions that both say "duplicates" "Find the duplicates" splits into two requests that need different queries: 1. *Which values are duplicated?* — one row per offending email, usually with a count. 2. *Which rows are duplicates?* — the actual rows, with their primary keys, because you intend to inspect or clean them. A self-join answers the second directly, and that is why interviewers reach for it: the aggregate formulation loses the primary keys, and you need those to do anything about the problem. ## The core pattern ```sql SELECT DISTINCT a.user_id, a.email FROM users AS a JOIN users AS b ON a.email = b.email AND a.user_id <> b.user_id; ``` Read the `ON` clause as two independent jobs. `a.email = b.email` defines what "duplicate" means — the key you are deduplicating on. `a.user_id <> b.user_id` prevents each row matching itself; without it, every row pairs with itself on equal email and the query returns the entire table, which is the single most common mistake in this pattern. ## Counting the output rows If k rows share an address, the join emits k(k-1) rows for it: each of the k rows pairs with the k-1 others. So a row that appears twice in the table shows up once in the raw output, a row that appears three times shows up twice, and so on. That is why `DISTINCT` belongs here and why raw `COUNT(*)` over this join is not the number of duplicate rows. If you want counts, group afterwards on the duplicate key rather than counting join rows. ## Choosing which copies to report Swapping the inequality for an ordering predicate turns "all rows involved" into "all rows except one survivor per group": ```sql -- every copy except the lowest user_id for each email SELECT DISTINCT a.user_id, a.email FROM users AS a JOIN users AS b ON a.email = b.email AND a.user_id > b.user_id; ``` A row survives this predicate only if some other row with the same email has a smaller key — that is, only if it is not the minimum of its group. This is the shape you want when the plan is "keep the earliest registration, review the rest". The choice of survivor is a business decision: lowest id, earliest `created_at`, the row with a non-null phone number. Whatever it is, the predicate has to encode it explicitly, and it must be deterministic or the result changes between runs. ## Multi-column duplicate keys Duplication is rarely on one column. Extend the equality across the whole key: ```sql SELECT DISTINCT a.order_id FROM orders AS a JOIN orders AS b ON a.customer_id = b.customer_id AND a.order_date = b.order_date AND a.total_cents = b.total_cents AND a.order_id <> b.order_id; ``` Every column of the intended natural key gets an equality; the key inequality stays exactly one predicate. ## The NULL caveat Equality never holds between two NULLs, so rows whose duplicate key is NULL never pair up and never appear in the result, no matter how many of them there are. If "two rows with no email" counts as a duplicate for you, the equality has to be written to treat NULLs as equal — standard SQL spells that `a.email IS NOT DISTINCT FROM b.email`. Decide deliberately; the default is that NULL keys are invisible to this query. ## Contrast with the aggregate formulation ```sql SELECT email, COUNT(*) AS copies FROM users GROUP BY email HAVING COUNT(*) > 1; ``` This is shorter, gives you the counts, and reads well in a report — but it returns emails, not users. The two are complementary: use the aggregate to size the problem ("how many addresses are affected, and how badly"), and the self-join to pull the rows you will actually work on. You can also combine them by joining `users` to that grouped set, which some people find clearer than the self-join; both are legitimate and an interviewer usually just wants you to name the tradeoff. ## What good answers include The self-match predicate and why it is there; the multiplicity of the output and the `DISTINCT`; the difference between reporting all copies and all-but-one; and the reminder that finding duplicates only proves the constraint that should have prevented them is missing.