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