left join on后面 加条件 与 where后面加条件的区别

本文主要讲解left join on 和where 的区别，以及用法
首先
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left join时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

1.用left join 进行条件过滤时，on条件只会对右表进行条件过滤，不会对左表数据产生任何影响；
2.用left join 进行条件过滤时，where条件会对结果表进行条件过滤，所以会对左表数据产生影响；
3.用inner join 用on和where没有任何区别

使用：

单独使用left join时：

select

o.id id,
o.user_id ,
o.out_trade_no,
o.create_time,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
u.name,
u.head_img,
u.create_time,
u.phone
from video_order o left join user u on o.user_id = u.id

这里video_order o是让o代替video_order 这个表，方便命名书写，它的作用首先是输出左表的全部数据，然后筛选右表，将符合的一一对应，与左表关联输出。注意这里是左表的全部数据均输出

再看另外一个left join on的例子

select

o.id id,
o.user_id ,
o.out_trade_no,
o.create_time,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
u.name,
u.head_img,
u.create_time,
u.phone
from video_order o left join user u on o.user_id = u.id and u.id=6

通过图示我们发现，这里也是显示左表的所有数据，不同在于，要筛选右表，右表不符合的显示为NULL；

join on与where同时使用
下面是我们更希望出现的样子，就是我们关联查询后，能过滤到左表我们不想要的东西

select

o.id id,
o.user_id ,
o.out_trade_no,
o.create_time,
o.state,
o.total_fee,
o.video_id,
o.video_title,
o.video_img,
u.name,
u.head_img,
u.create_time,
u.phone
from video_order o left join user u on o.user_id = u.id where u.id=6

其实除了这个办法，我们还可以对左表用子查询进行筛选，具体代码如下

select o.order_id,o.user_id,u.user_id,u.user_name
from ( select * from tb_order where order_id > 10 ) o LEFT JOIN tb_user u on o.user_id = u.user_id;

LEFT JOIN条件在on后面和在where后面的区别
一、left join 的实际运用
二、两表联查SQL区别
1、在on后面补全条件
2、在where后面补全条件
三、三表联查SQL区别
1.错误示范：
1.1 连续使用left join，on后面过滤条件
1.2 连续使用left join，where后面过滤条件
2.正确操作：
2.1将test2和test3作为整个右表查询
四、结论
一、left join 的实际运用
left join 一般用于显示左边所有数据，并携带右表的数据；
而在on后面进行条件过滤，只会过滤掉右表条件不为真的数据，左表数据一定会显示；
在where后面进行条件过滤，是对左表右表的结果表进行过滤，所以数据不一定能完全显示。

二、两表联查SQL区别
存在数据表test1, test4
test1:

test4:

1、在on后面补全条件
select * from test1 left join test4 on test1.id = test4.id and test4.is_effective = 1
1

可以发现test1中的数据全部被读取，test4中过滤了is_effective != 1的数据

2、在where后面补全条件
select * from test1 left join test4 on test1.id = test4.id where test4.is_effective = 1
1

可以发现只显示了test1中的id为1的数据内容。

三、三表联查SQL区别
存在test1,test2,test3三张表，其中test3是test1和test2的中间表
test1:

test2:

test3:

某些业务中，我们会使用中间表查询数据，如果存在业务，要显示test1中的全部数据，并携带test2中的有效数据，我们一般会采用left join 去完成sql，而这时需要注意应该将test2和test3的结果表作为整个右表，才能正常完成业务。

1.错误示范：
1.1 连续使用left join，on后面过滤条件

SELECT
*
FROM
test1
LEFT JOIN test3 ON test1.id = test3.id1
LEFT JOIN test2 ON test2.id = test3.id2 and is_effective = 1

会查出test1中的重复数据

1.2 连续使用left join，where后面过滤条件

SELECT
*
FROM
test1
LEFT JOIN test3 ON test1.id = test3.id1
LEFT JOIN test2 ON test2.id = test3.id2
WHERE
is_effective = 1

无法完全显示test1中的数据

2.正确操作：
2.1将test2和test3作为整个右表查询

SELECT
*
FROM
test1
LEFT JOIN ( SELECT * FROM test2 INNER JOIN test3 ON test2.id = test3.id2 WHERE is_effective = 1 ) b ON test1.id = b.id1
 

正常完成查询
 

参考:left join on后面 加条件 与 where后面加条件的区别

参考:LEFT JOIN条件在on后面和在where后面的区别