mysql> CREATE TABLE animal(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));

mysql> CREATE TABLE animal_food(id INT AUTO_INCREMENT PRIMARY KEY, food VARCHAR(20), animal_id INT);

mysql> INSERT INTO animal VALUES (1, 'Cape Gerbil'),(2,'Chacma Baboon'),(3,'Porcupine'),(4,'Small Grey Mongoose');

mysql> INSERT INTO animal_food VALUES(1,'insects',4),(2,'rodents',4),(3,'rodents',2),(4,'fruit',2);

mysql> EXPLAIN SELECT DISTINCT name FROM animal,animal_food WHERE animal.id=animal_id;
+----+-------------+-------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | animal_food | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using temporary |
|  1 | SIMPLE      | animal      | ALL  | PRIMARY       | NULL |    NULL | NULL |    3 | Using where     |
+----+-------------+-------------+------+---------------+------+---------+------+------+-----------------+
2 rows in set (0.25 sec)

mysql> EXPLAIN SELECT name FROM animal WHERE id IN (SELECT animal_id FROM animal_food);
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | animal      | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using where |
|  2 | DEPENDENT SUBQUERY | animal_food | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using where |
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT name FROM animal LEFT JOIN  animal_food ON animal.id=animal_id WHERE animal_id IS NULL;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | animal      | ALL  | NULL          | NULL |    NULL | NULL |    4 |             |
|  1 | SIMPLE      | animal_food | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
 
mysql> EXPLAIN SELECT name FROM animal WHERE id NOT IN (SELECT animal_id FROM animal_food);
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | animal      | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using where |
|  2 | DEPENDENT SUBQUERY | animal_food | ALL  | NULL          | NULL |    NULL | NULL |    4 | Using where |
+----+--------------------+-------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT animal.id,name FROM animal WHERE animal.id IN (1,2,3) AND EXISTS (SELECT NULL FROM animal_food WHERE animal_food.id>2 AND animal.id=animal_food.id ) ORDER BY name;
+----+--------------------+-------------+--------+---------------+---------+---------+----------------+------+-----------------------------+
| id | select_type        | table       | type   | possible_keys | key     | key_len | ref            | rows | Extra                       |
+----+--------------------+-------------+--------+---------------+---------+---------+----------------+------+-----------------------------+
|  1 | PRIMARY            | animal      | ALL    | PRIMARY       | NULL    |    NULL | NULL           |    4 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | animal_food | eq_ref | PRIMARY       | PRIMARY |       4 | test.animal.id |    1 | Using where; Using index    |
+----+--------------------+-------------+--------+---------------+---------+---------+----------------+------+-----------------------------+
 
mysql> EXPLAIN SELECT DISTINCT animal.id,name FROM animal,animal_food WHERE animal.id IN (1,2,3) AND animal_food.id > 2 AND animal.id=animal_food.id ORDER BY name;
+----+-------------+-------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                 | rows | Extra                                                     |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | animal_food | range  | PRIMARY       | PRIMARY |       4 | NULL                |    3 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | animal      | eq_ref | PRIMARY       | PRIMARY |       4 | test.animal_food.id |    1 | Using where                                               |
+----+-------------+-------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+

mysql> EXPLAIN SELECT animal.id,name FROM animal WHERE animal.id IN (1,2,3) AND animal.id IN (SELECT animal_id FROM animal_food WHERE animal_food.id>2 ) ORDER BY name;
+----+--------------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type        | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                       |
+----+--------------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+
|  1 | PRIMARY            | animal      | ALL   | PRIMARY       | NULL    |    NULL | NULL |    4 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | animal_food | range | PRIMARY       | PRIMARY |       4 | NULL |    3 | Using where                 |
+----+--------------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------+