Homepage von Daniel Murygin

This is an article about how find rows in a table with one or more identical columns by SQL. The use case is to find entries in an account table with the same email address or with the same first name and surname. The trick is to join the table to itself.

Find all rows with the same email address:

SELECT left.*
FROM account left
INNER JOIN account right ON left.email = right.email
AND left.id != right.id;

Find all rows with the same first name and the same surname:

SELECT left.*
FROM account left
INNER JOIN account right ON left.firstname = right.firstname
AND left.name = right.name
AND left.id != right.id;