left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。

right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

full join:外连接，返回两个表中的行：left join + right join。

cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

关键字: on

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

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

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

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

假设有两张表：

表1：tab2

id

size

1

10

2

20

3

30

表2：tab2

size

name

10

AAA

20

BBB

20

CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

第一条SQL的过程：

 

 

1、中间表

on条件:

tab1.size = tab2.size

tab1.idtab1.sizetab2.sizetab2.name11010AAA22020BBB22020CCC330(null)(null)

 

 

 

2、再对中间表过滤

where 条件：

tab2.name=’AAA’

tab1.idtab1.sizetab2.sizetab2.name11010AAA

 

 

 

第二条SQL的过程：

 

 

1、中间表

on条件:

tab1.size = tab2.size and tab2.name=’AAA’

(条件不为真也会返回左表中的记录)

tab1.idtab1.sizetab2.sizetab2.name11010AAA220(null)(null)330(null)(null)

 

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

 

举例说明：

下记转载：

   例如存在两张表A,B表数据如下：

 语句一

Sql代码

select  A.ID as AID, B.ID as BID   from A left join B on A.ID = B.ID where B.ID<3

 语句二

Sql代码

select  A.ID as AID, B.ID as BID  from A left join B on A.ID = B.ID and  B.ID<3

   以上两个语句的查询结果是否一致。

   我们看看实际结果

   语句一的查询结果

 

 

语句二的查询结果为：

 

 

发现两个查询存在差异。

为什么会存在差异，这和on与where查询顺序有关。

我们知道标准查询关键字执行顺序为 from->where->group by->having->order by

left join 是在from范围类所以 先on条件筛选表，然后两表再做left join。

而对于where来说在left join结果再次筛选。

 第一sql语句查询过程如下等价于：

    1:先是left join

Sql代码 

select  A.ID as AID, B.ID as BID   from A left join B on A.ID = B.ID

   查询结果如下

  

  2：再查询结果中将B.ID即BID<3筛选出来。

       也就是我们上面看到的结果。

第二sql语句查询过程如下等价于：

  1：先按照on条件B.ID<3筛选表等价于先筛选B表：

   

   2：已上查询结果与A表做left join，这也是为什么我们看到第二个查询的sql会保留A表的原因。

 

ON与where的使用一定要注意场所：

    （1）：ON后面的筛选条件主要是针对的是关联表【而对于主表刷选条件不适用】。

    （2）：对于主表的筛选条件应放在where后面，不应该放在ON后面。

    （3）：对于关联表我们要区分对待。如果是要条件查询后才连接应该把查询件放置于ON后。

                如果是想在连接完毕后才筛选就应把条件放置于where后面。

    （4）： 对于关联表我们其实可以先做子查询再做join

所以第二个sql等价于

Sql代码 

select  A.ID as AID, B1.ID as BID  
from A left join  ( select B.ID from B  where B.ID <3 )B1 on A.ID = B1.ID

 

通过以上讲解，不知大家是否明白了，欢迎沟通交流。