WITH DuplicateCity AS (
SELECT Name
FROM city
GROUP BY Name
HAVING COUNT(Name) > 1
)
SELECT *
FROM city
WHERE Name IN (
SELECT Name
FROM DuplicateCity
)
ORDER BY Name
| ID | Name | CountryCode | District | Population |
| 3,960 | Alexandria | USA | Virginia | 128,283 |
| 609 | Alexandria | EGY | Aleksandria | 3,328,196 |
| 1967 | Anyang | CHN | Henan | 420,332 |
| 2341 | Anyang | KOR | Kyonggi | 591,106 |
| 3,909 | Arlington | USA | Virginia | 174,838 |
| 3,845 | Arlington | USA | Texas | 332,969 |
| 3,853 | Aurora | USA | Colorado | 276,393 |
| 3,941 | Aurora | USA | Illinois | 142,990 |
| 3,546 | Barcelona | VEN | Anzoátegui | 322,267 |
| 654 | Barcelona | ESP | Katalonia | 1,503,451 |
| ... |
| Common Table Expressions (CTE) are an easy way to split a complex SQL query into multiple queries, which gives you more flexibility and manageability. This query is similar to the example with the IN operator. A subquery that selects duplicate cities is placed in a temporary table, which is then used in the main query. |