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            aaron@gmail.com
Christy          aaron@gmail.com
Jason            jason@gmail.com
Eric             eric@gmail.com
John             aaron@gmail.com

I need the query to return:

Aaron            aaron@gmail.com
Christy          aaron@gmail.com
John             aaron@gmail.com

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)