SQL语句:left join后面加上where条件

时间:2024-10-22 08:28:26
select a.*,b.*
from table1 a
left join table2 b on =
where XXX

如上SQL:一旦使用了left join,没有where条件时,左表table1会显示全部内容

  而使用了where,只有满足where条件的记录才会显示(左表显示部分或者全部不显示)

 

一旦加上where条件,则显示的结果等于inner join ?

原因分析:

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户;

where条件是在临时表生成好后,再对临时表进行过滤的条件;

因此:where 条件加上,已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。 

解决方案:

1、where过滤结果作为子查询,和主表left,如下:

select a.*,tmp.*
from table1 a
left join(
    select a.*,b.*
    from table1 a
    left join table2 b on =
    where XXX
)tmp

很明显,子查询语句无论 left join、inner join都没啥区别了

2、查询条件放在on后面

select a.*,b.*
from table1 a
left join table2 b on = and XXX

注意:where XXX去掉,改为链接条件on后面的 and XXX

分析:

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

结论:

过滤条件放在 where后面:是先连接然生成临时查询结果,然后再筛选

过滤条件放在 on后面:先根据条件过滤筛选,再连,生成临时查询结果

 

还是有点不够清晰吧 ~~!     

下面举个栗子-----------------------------

  假设有两张表:

表1 tab1:   表2 tab2:
id size size  name
1 10 10 AAA
2 20 20 BBB
3 30 20 CCC

两条SQL:

第一条SQL的过程:

select * form tab1 left join tab2 on ( = ) where =’AAA’

1、中间表

on条件:  =

 
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 null null

2、再对中间表过滤

where 条件:=’AAA’

 
1 10 10 AAA

 

第二条SQL的过程:

select * form tab1 left join tab2 on ( =  and =’AAA’)

1、中间表

on条件:  = and =’AAA’(条件不为真也会返回左表中的记录)

 
1 10 10 AAA
2 20 null null
3 30 null null

其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。