SELECT WHERE IN (subquery) slow

Question

I've tried the solution here, but it doesn't work.

My table is like this:

   `Index`  uid   dept
...........................
      1    001   dept1
      2    001   dept2
      3    001   dept3
      4    002   dept2
      5    002   dept3
      6    002   dept4
      7    003   dept1
      8    003   dept5
      9    004   dept1
      10   004   dept6

I want to retrieve all the rows with a particular dept. That is, If I want to retrieve dept1, I want to retrieve all rows except uid=002, since there's no dept1 for uid=002.

The query string is slow even when using index:

SELECT id FROM table WHERE uid IN
(SELECT uid WHERE dept='dept1')

My previous version without using WHERE IN is as following:

Retrieves all the uid with dept=dept1 first.
Then use a for-loop for all uid retrieved in the first query.

This method is very fast for a small amount(100) of rows retrieved in the first query. However, it seems that it's not a good solution because it creates a lot of queries(each of them is extremely fast).

Answer 1

Try this one:

select a.id from Table1 a
inner join Table1 b on a.uid = b.uid and b.dept = 'dept1';

Demo: http://sqlfiddle.com/#!2/05774/4