Mysql – How to get rows only contains multiple ID using SQL

MySQLmysql-5.6

Let say I have this table:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300
3  | 200
4  | 100

I am expecting to have this result:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300

I want to get only rows that have multiple id log, regardless of the foo value.

Best Answer

Variant 1 (The Censored DBA's solution with syntax correction):

SELECT * 
FROM tablename
WHERE id IN ( SELECT id 
              FROM tablename
              GROUP BY id
              HAVING COUNT(*) > 1);

Variant 2 (subquery in FROM clause):

SELECT t1.* 
FROM tablename t1
JOIN ( SELECT id 
       FROM tablename
       GROUP BY id
       HAVING COUNT(*) > 1 ) t2 ON t1.id = t2.id;

The index by id is safe for to increase performance in both variants.

Variant 3. To improve performance you can try to use temporary table:

CREATE TEMPORARY TABLE temp (id INT PRIMARY KEY) ENGINE=Memory;

INSERT INTO temp
SELECT id 
FROM tablename
GROUP BY id
HAVING COUNT(*) > 1;

SELECT t1.* 
FROM tablename t1
JOIN temp t2 ON t1.id = t2.id;

DROP TEMPORARY TABLE temp;
Related Question