sql - How to Select Every Row Where Column Value is NOT Distinct

Question

I need to run a select statement that returns all rows where the value of a column is not distinct (e.g. EmailAddress).

For example, if the table looks like below:

CustomerName     EmailAddress
Aaron            [email protected]
Christy          [email protected]
Jason            [email protected]
Eric             [email protected]
John             [email protected]

I need the query to return:

Aaron            [email protected]
Christy          [email protected]
John             [email protected]

I have read many posts and tried different queries to no avail. The query that I believe should work is below. Can someone suggest an alternative or tell me what may be wrong with my query?

select EmailAddress, CustomerName from Customers
group by EmailAddress, CustomerName
having COUNT(distinct(EmailAddress)) > 1

Answer 1

This is significantly faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)