Find rows with identical columns by SQL
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;