How to get rows only contains multiple ID using SQL?

Question

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.

Answer 1

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;

Answer 2

You can try this:

SQL> select * from dummy;

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300
     3    200
     4    200

7 rows selected.

SQL> select * from dummy where id in (select id from dummy having count(*) > 1 group by id);

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300

SQL>