1.inner join 和 left join 限制条件放在 where 和 on 后面 有什么区别?
先说结论:
用 inner join , LEFTJOIN时,条件直接放ON后面,是先筛选后连接,条件放WHERE后面,是先连接后筛选
inner join A inner join B on a.id = b.a_id and a.is_delete = 0 and b.is_delete = 0
等同于
A inner join B on a.id = b.a_id where a.is_delete = 0 and b.is_delete = 0
left join
A left join B on a.id = b.a_id and a.is_delete = 0 and b.is_delete = 0
不等同于
A left join B on a.id = b.a_id where a.is_delete = 0 and b.is_delete = 0
说明:
on 后面 直接加条件,只会对右表数据产生过滤筛选,是关联之前对右表的操作,不会对左边的表产生影响,不管如何都会返回左边表中的记录
where 加限制条件 才会对左边的表产生影响
where条件是关联查询之后的条件
# 用 inner join , LEFTJOIN时,条件直接放ON后面,是先筛选后连接,条件放WHERE后面,是先连接后筛选
# inner join
# A inner join B on a.id = b.a_id and a.is_delete = 0 and b.is_delete = 0
# 等同于 A inner join B on a.id = b.a_id where a.is_delete = 0 and b.is_delete = 0
# left join
# A left join B on a.id = b.a_id and a.is_delete = 0 and b.is_delete = 0
# 不等同于 A left join B on a.id = b.a_id where a.is_delete = 0 and b.is_delete = 0
# 说明:
# on 后面 直接加条件的话,不会对左边的表产生影响,只会对右表数据产生过滤筛选,on条件是在左关联时候的条件,不管如何都会返回左边表中的记录
# 是关联之前对右表的操作
# where 加条件 才会对左边的表 生效。where条件是关联查询之后的条件
select * from teacher;
# +---+-----+-------+
# |Tid|Tname|t_group|
# +---+-----+-------+
# |01 |张三 |1 |
# |02 |李四 |1 |
# |03 |王五 |2 |
# |04 |赵六 |2 |
# +---+-----+-------+
select * from course;
# +---+-----+---+----------+
# |Cid|Cname|Tid|is_deleted|
# +---+-----+---+----------+
# |01 |语文 |02 |0 |
# |02 |数学 |01 |0 |
# |03 |英语 |03 |0 |
# |04 |化学 |01 |0 |
# |05 |地理 |01 |0 |
# |06 |体育 |01 |1 |
# |07 |音乐 |01 |1 |
# +---+-----+---+----------+
# inner join
# 查看有代课程的老师情况(没带课程的老师信息不显示)
# 找到 teacher的 任课科目 (A inner join B 也可能结果集是 大于A 表数据,所以说 A inner join B on 的结果集不能 同时大于 A ,B的 数量 )
select t.Tid, t.Tname, c.Cname ,c.is_deleted from teacher t inner join course c on t.Tid = c.Tid order by Tid;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# |01 |张三 |体育 |1 |
# |01 |张三 |音乐 |1 |
# |02 |李四 |语文 |0 |
# |03 |王五 |英语 |0 |
# +---+-----+-----+----------+
# 过滤条件 放到 on 后面 和放到 where 后面的区别
# 选出 张三 老师 的有效课程
# ① where 后面
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
inner join course c on t.Tid = c.Tid
where t.Tname = '张三' and is_deleted = 0 ;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
# ② on 后面 : A inner join B 的时候 ,分别筛选出 A满足条件的集合,B满足条件的集合
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
inner join
course c on t.Tid = c.Tid and t.Tname = '张三' and is_deleted = 0 ;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
# 查看所有老师的代课情况(没带课程的老师信息也需要显示)
# left join
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
left join course c on t.Tid = c.Tid
order by Tid;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# |01 |张三 |体育 |1 |
# |01 |张三 |音乐 |1 |
# |02 |李四 |语文 |0 |
# |03 |王五 |英语 |0 |
# |04 |赵六 |NULL |NULL |
# +---+-----+-----+----------+
# 查看张三的有效代课情况(张三有可能不带课程)
# ① 条件放到 where后面 (结果说明这种情况是正确的)
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
left join
course c on t.Tid = c.Tid
where t.Tname = '张三'
and is_deleted = 0;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
# ② 条件放到 on 后面
# on 后面 直接加条件的话,不会对左边的表产生影响,只会对右表数据产生过滤筛选,on条件是在左关联时候的条件,不管如何都会返回左边表中的记录
# 是关联之前对右表的操作
# where 加条件 才会对左边的表 生效。where条件是关联查询之后的条件
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
left join
course c on t.Tid = c.Tid and t.Tname = '张三' and is_deleted = 0 ;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# |02 |李四 |NULL |NULL |
# |03 |王五 |NULL |NULL |
# |04 |赵六 |NULL |NULL |
# +---+-----+-----+----------+
# 说明:on t.Tid = c.Tid and t.Tname = '张三' and is_deleted = 0 是对 右表 进行 操作 ------>
# 从右表筛选出 张三,有效的数据,结果集B1为:
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
# 然后 A left join B1
# on 后面的条件只针对 右表数据集进行过滤,如果要过滤 坐标数据,需要 在where 后面加过滤条件,如下
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
left join
course c on t.Tid = c.Tid and t.Tname = '张三' and is_deleted = 0
where t.Tname = '张三' ;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
# 等同于如下:
select t.Tid, t.Tname, c.Cname, c.is_deleted
from teacher t
left join
course c on t.Tid = c.Tid and is_deleted = 0
where t.Tname = '张三' ;
# +---+-----+-----+----------+
# |Tid|Tname|Cname|is_deleted|
# +---+-----+-----+----------+
# |01 |张三 |数学 |0 |
# |01 |张三 |化学 |0 |
# |01 |张三 |地理 |0 |
# +---+-----+-----+----------+
select * from girls;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1 |W | |1 |2020-07-23 15:13:48|20200723_0002|
# |2 |慕容b |2 |W | |NULL |2020-07-23 14:13:48|20200723_0001|
# |3 |慕容c |3 |W | |NULL |2020-07-23 15:13:48|20200723_0003|
# |4 |慕容d |4 |W | |NULL |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+
select * from boys;
# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |id|name|sex|age|address|created_time |enum|create_time_str|is_deleted|
# +--+----+---+---+-------+-------------------+----+---------------+----------+
# |1 |慕容皝 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |2 |慕容白 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |3 |慕容垂 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |4 |慕容复 |男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# |5 |慕容龙城|男 |22 |NULL |1000-05-01 17:43:43|1 |1000-05-01 |NULL |
# +--+----+---+---+-------+-------------------+----+---------------+----------+
select girls.name, girls.is_deleted, boys.id, boys.is_deleted
from girls
left join boys ON girls.boy_id = boys.ID and girls.name = '慕容a';
# 结果说明: A left join B on 后面条件不能限制住 A
# +----+----------+----+----------+
# |name|is_deleted|id |is_deleted|
# +----+----------+----+----------+
# |慕容a |1 |1 |NULL |
# |慕容b |NULL |NULL|NULL |
# |慕容c |NULL |NULL|NULL |
# |慕容d |NULL |NULL|NULL |
# +----+----------+----+----------+
select girls.name, girls.is_deleted, boys.id, boys.is_deleted
from girls
left join boys ON girls.boy_id = boys.ID and boys.name = '慕容复';
# 结果说明: A left join B on 后面条件能限制住 B
# +----+----------+----+----------+
# |name|is_deleted|id |is_deleted|
# +----+----------+----+----------+
# |慕容a |1 |NULL|NULL |
# |慕容b |NULL |NULL|NULL |
# |慕容c |NULL |NULL|NULL |
# |慕容d |NULL |4 |NULL |
# +----+----------+----+----------+
# 结论: A left join B on and 这些条件是限制 B的 where 后面才可以限制住 A