SQL to find DUPLICATE records in the database table.
Below is shown the "PhoneInfo" table:
| Thomas Hardy |
171-555-7434 |
| Christina Berglund |
171-555-3453 |
| Hari Kumar |
171-555-1717 |
| Peter Franken |
089-087-7310 |
| Thomas Hardy |
171-555-7434 |
| Ann Devon |
069-534-6421 |
| Hari Kumar |
171-555-1717 |
SQL to Find DUPLICATE Records
SQL Query to retrieve all the Duplicate Records from the "PhoneInfo" Table.
Text Editor below has the SQL Query, click the "View Result" button, to see the result.
Select Ph.PersonName, Ph.Phone
From PhoneInfo Ph
Where Ph.PersonName IN
(Select PersonName from PhoneInfo
GROUP BY PersonName
HAVING COUNT(PersonName) > 1)
ORDER BY Ph.PersonName;
Note: SQL SUBQUERY returns the names of the persons repeated in the PersonName column.
Using this the main query brings out all the records repeated which has that name.
SQL to Find Records that are not repeated
SQL Query to Retrieve all the Records from the "PhoneInfo" Table, that are not repeated.
Select Ph.PersonName, Ph.Phone
From PhoneInfo Ph
Where Ph.PersonName NOT IN
(Select PersonName from PhoneInfo
GROUP BY PersonName
HAVING COUNT(PersonName) > 1)
ORDER BY Ph.PersonName;
Note: SQL SUBQUERY returns all the duplicate records containing the names of the persons repeated in the PersonName column.
Using the
NOT IN keyword in the where clause retrieves unique records.