MySQL4种内连接写法

有INNER JOIN，WHERE（等值连接），STRAIGHT_JOIN, JOIN(省略INNER)四种写法。

SELECT * FROM t_blog INNER JOIN t_type ON t_blog.typeId=t_type.id;
SELECT * FROM t_blog,t_type WHERE t_blog.typeId=t_type.id;
SELECT * FROM t_blog STRAIGHT_JOIN t_type ON t_blog.typeId=t_type.id; --注意STRIGHT_JOIN有个下划线
SELECT * FROM t_blog JOIN t_type ON t_blog.typeId=t_type.id;
    +----+-------+--------+----+------+
    | id | title | typeId | id | name |
    +----+-------+--------+----+------+
    |  1 | aaa   |      1 |  1 | C++  |
    |  2 | bbb   |      2 |  2 | C    |
    |  7 | ggg   |      2 |  2 | C    |
    |  3 | ccc   |      3 |  3 | Java |
    |  6 | fff   |      3 |  3 | Java |
    |  4 | ddd   |      4 |  4 | C# |
    |  5 | eee   |      4 |  4 | C# |
    +----+-------+--------+----+------+

内连接谁当驱动表：当内连接时，务必用小表驱动大表，小表驱动大表可以减小内循环的次数。例子：

EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.type=t2.type;
    +----+-------+------+------+-------+----------------------------------------------------+
    | id | table | type | key  | rows  | Extra                                              |
    +----+-------+------+------+-------+----------------------------------------------------+
    |  1 | t1    | ALL  | NULL | 10000 | NULL                                               |
    |  1 | t2    | ALL  | NULL |   100 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------+------+------+-------+----------------------------------------------------+
EXPLAIN SELECT * FROM t2 STRAIGHT_JOIN t1 ON t2.type=t1.type;
    +----+-------+------+------+-------+----------------------------------------------------+
    | id | table | type | key  | rows  | Extra                                              |
    +----+-------+------+------+-------+----------------------------------------------------+
    |  1 | t2    | ALL  | NULL |   100 | NULL                                               |
    |  1 | t1    | ALL  | NULL | 10000 | Using where; Using join buffer (Block Nested Loop) |
    +----+-------+------+------+-------+----------------------------------------------------+

对于第一条查询语句，t1是驱动表，其有10000条记录，内循环也就有10000次，这还得了？
对于第二条查询语句，t2是驱动表，其有100条记录，内循环100次，感觉不错!
这些SQL语句的执行时间也说明了，当内连接时，务必用小表驱动大表。

本文地址：https://blog.csdn.net/weixin_46162745/article/details/108571699

MySQL4种内连接写法

相关推荐