CODEWITHSUNDEEP
mysql
Kristian Rafteseth
Kristian Rafteseth

Reputation: 2032

How to select SQL rows where a spesific column is not the same value as another row?

I have a table with some columns, one of these are "email". I want to select the rows in this table, where there is no duplicate value in "email".

Meaning if the table was like this:

id - email
10 - [email protected]
11 - [email protected]
12 - [email protected]
13 - [email protected]

The query would return only id 11 and 13, as 10 and 12 are duplicates.

Upvotes: 0

Views: 61

Answers (2)

John Woo
John Woo

Reputation: 263683

I'll recommend the query that uses JOIN.

SELECT *
FROM tableName
WHERE email IN
    (
        SELECT email
        FROM    tableName
        GROUP BY email
        HAVING COUNT(*) = 1
    )

or using JOIN

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT email
            FROM    tableName
            GROUP BY email
            HAVING COUNT(*) = 1
        ) b ON a.email = b.email

for better performance, you use define an index on column email

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT *
FROM Emails
WHERE email NOT IN(SELECT email
                   FROM emails
                   GROUP BY email
                   HAVING COUNT(email) > 1);

This will give you:

| ID |         EMAIL |
----------------------
| 11 |  [email protected] |
| 13 | [email protected] |

SQL Fiddle Demo

Upvotes: 1

Related Questions