test>desc items;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| short_desc | varchar(255) | YES | | NULL | |
| example | text | YES | | NULL | |
| explanation | text | YES | | NULL | |
| additional | text | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
test>desc items_links;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| iid | int(11) | YES | | NULL | |
| linkid | int(11) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
.test>explain extended select count(*) from items where id in(select id from items_links);
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+
| 1 | SIMPLE | items_links | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Start temporary |
| 1 | SIMPLE | items | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------+-------------------------------------------------------------------+
2 rows in set, 2 warnings (0.01 sec)
test>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'test.items.id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`items` semi join (`test`.`items_links`) where 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
在使用explain extended查看执行计划后,可以使用show warnings来查看具体实际是怎么执行的,上面的例子中,子查询被转换成了半连接,由于links表中没有id字段,所以其实这个语句只是item自己跟自己的关联,我们在warning的实际的执行计划中也就么有看到关联的条件where后边就是个1,这种语句在oracle中不会执行。