034.mysql-left join时把过滤条件写到where中、还是on中的区别;如果是inner join效果相同
tst00表
tst01表
CREATE TABLE `tst00` ( `id` int NOT NULL AUTO_INCREMENT, `json_v` text COLLATE utf8mb4_general_ci, `num` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `tst01` ( `id` int NOT NULL AUTO_INCREMENT, `json_v` text COLLATE utf8mb4_general_ci, `num` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-------
left join
第一种过滤条件 写在最后用 where 过滤,先关联上再做筛选
SELECT * FROM `tst00` a left join `tst01` b on a.id = b.id where b.num = 2
第二种 用on过滤 ,数量是随着左表的,先关联,过滤条件只会把没有筛选到的右表的内容置为空
SELECT * FROM `tst00` a left join `tst01` b on a.id = b.id and b.num = 2
inner join 效果一样
SELECT * FROM `tst00` a join `tst01` b on a.id = b.id where b.num = 2 ; SELECT * FROM `tst00` a join `tst01` b on a.id = b.id and b.num = 2