Hola,
No estoy seguro de estar de acuerdo, pero en fin, las dos consultas cumplen con la condicion.
Código:
mysql> select * from t1;
+------+------+
| aid | eid |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 5 | 2 |
+------+------+
8 rows in set (0.00 sec)
Código:
mysql> explain
-> select distinct a.aid from t1 a
-> where exists
-> (select count(*) from t1 b
-> where eid in (1,2) and a.aid = b.aid
-> having count(*) > 1
-> );
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 8 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+------------------------------+
2 rows in set (0.00 sec)
Código:
mysql> explain
-> SELECT T2.aId
-> FROM t1 t2
-> INNER JOIN
-> (SELECT aid
-> FROM t1 WHERE eid IN(1)
-> ) AS t2 USING(aid)
-> WHERE T2.eid IN(2) ;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)
Saludos