看情况,如果你的devicespec1.id是主键或者索引什么的,那么join效率高。看这个就清楚了:
mysql> explain select test_tb1.id, test_tb2.value from test_tb1, test_tb2 where test_tb1.id = test_tb2.id;
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
| 1 | SIMPLE | test_tb1 | index | PRIMARY | PRIMARY | 4 | NULL | 3 | Using index |
| 1 | SIMPLE | test_tb2 | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where; Using join buffer |
+----+-------------+----------+-------+---------------+---------+---------+------+------+--------------------------------+
2 rows in set (0.01 sec)
mysql> explain select test_tb1.id, test_tb2.value from test_tb1 left join test_tb2 on test_tb1.id = test_tb2.id;
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
| 1 | SIMPLE | test_tb1 | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |
| 1 | SIMPLE | test_tb2 | eq_ref | PRIMARY | PRIMARY | 4 | test.test_tb1.id | 1 | |
+----+-------------+----------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
第一次查询要遍历两张表,第二次查询使用eq_ref不用遍历第二张表