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
复制代码
http://www.51relaw.com/news/bencandy.php?&fid=37&id=1230