昨天写 SQL Server 数据库上写 SQL 语句时，我原本想通过执行A left join B on ... and ...的操作，将查询结果中的两条记录合并成一条，奈何发现结果中依然有两条记录。

回顾了一下语法知识，意识到join on ... and ...的语句并不会对结果集的记录条数进行过滤。它的主要作用是，根据and后面指定的条件来决定是否显示B表的记录。而A表的记录则会无条件地显示出来。

不论and后面跟的是A.id=1还是B.id=1这样的条件，查询结果都会显示A表中的所有记录。同时，如果B表中存在与A表中id为1的记录相匹配的数据，或者B表中存在id为1的记录，这些信息也会被关联并显示出来。
运行sql :

select * from student s left join class c on s.classId=c.id order by s.id
  • 1.

left join 后用 on 还是 where？_sql

 

运行sql :

select * from student s left join class c on s.classId=c.id and s.name="张三" order by s.id
  • 1.

left join 后用 on 还是 where？_结果集_02

 

运行sql :

select * from student s left join class c on s.classId=c.id and c.name="三年级三班" order by s.id
  • 1.

left join 后用 on 还是 where？_sql_03

 

数据库通过连接多张表来返回记录的过程中，会首先生成一个中间的临时表。这个临时表是连接操作的核心，并且最终会基于这个临时表向用户展示结果。

当使用`left join`时，`on`条件的作用是在构建这个临时表时，用于确定如何连接左表和右表。无论`on`条件是否满足，左表中的所有记录都会被包含在生成的临时表中。

与之不同，`where`条件是在临时表已经构建完成之后，对临时表内容进行筛选的条件。这时，`left join`的语义（即必须返回左表的所有记录）已经不再适用。`where`条件会过滤掉不满足条件的记录，仅保留符合条件的记录作为最终的查询结果。

假设有两张表：
表1：tab2

left join 后用 on 还是 where？_SQL_04

2：tab2

left join 后用 on 还是 where？_SQL_05

 

两条SQL:1、

select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
  • 1.

2、

select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)
  • 1.


第一条SQL的过程：

1、中间表on条件:

tab1.size = tab2.size
  • 1.

left join 后用 on 还是 where？_sql_06

 

2、再对中间表过滤where 条件：

tab2.name=’AAA’
  • 1.

left join 后用 on 还是 where？_sql_07

 

第二条SQL的过程：


1、中间表on条件

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

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

left join 后用 on 还是 where？_结果集_08

 

实际上，上述结果的关键所在正是源于left join、right join以及full join的特点。在这些连接操作中，不论on条件是否成立，都会返回left或right表中的记录。而full join则结合了left和right的特性，返回的是两者的并集。

然而，对于inner join而言，它并不具备这样的特性。因此，在inner join中，无论是将条件放在on中还是where中，最终得到的结果集都是相同的。