T-SQL查询笔记1：当使用联接时on和where子句的区别

联接运算涉及下列步骤的一个子集（取决于联接的类型）：

1.执行笛卡尔积cross join；

2.应用on筛选器；

3.添加外部行(只在外联接outer join中发生)。

 

所以，当使用内联接inner join时哪里指定逻辑表达式on和where作用一样，因为会跳过步骤3。

当使用外联接outer join时，步骤2的on筛选器对保留表中部分行的删除不是最终的，因为步骤3会把这些行再添加回来，而where筛选则是最终的。

 

例子，先插入测试数据：

USE tempdb
GO
if OBJECT_ID('dbo.Article') is not null drop table dbo.Article
if OBJECT_ID('dbo.ArticleType') is not null drop table dbo.ArticleType

CREATE TABLE dbo.Article(ID int,TypeID int,Title nvarchar(50))
INSERT Article SELECT 1, 1, N'文章1'
UNION ALL SELECT 2, 1, N'文章2'
UNION ALL SELECT 3, 2, N'文章3'
GO
CREATE TABLE dbo.ArticleType(ID int,Name nvarchar(50))
INSERT ArticleType SELECT 1, N'C#'
UNION ALL SELECT 2, N'SQL'
GO

 1，当使用inner join时，可知on和where作用一样

select * from dbo.Article a inner join dbo.ArticleType b on a.TypeID = b.ID
select * from dbo.Article a inner join dbo.ArticleType b on a.TypeID = b.ID and a.TypeID=1
select * from dbo.Article a inner join dbo.ArticleType b on a.TypeID = b.ID where a.TypeID=1

 运行结果：

ID          TypeID      Title        ID          Name
----------------------------------------------------------
1           1           文章1          1           C#
2           1           文章2          1           C#
3           2           文章3          2           SQL

(3 行受影响)

ID          TypeID      Title     ID          Name
-------------------------------------------------------
1           1           文章1       1           C#
2           1           文章2       1           C#

(2 行受影响)

ID          TypeID      Title       ID          Name
----------------------------------------------------
1           1           文章1         1           C#
2           1           文章2         1           C#

(2 行受影响)

 

2，对于外联接，通过为其指定一种外联接类型（LEFT、RIGHT或FULL），就把一个或两个输入表标记为保留表，保留表即表示希望返回该表的所有行，即使筛选器<on_predicate>过滤掉了一些行。左外联接是把左边的表标记为保留表。。。这一步骤除了返回ON筛选器中的行，还返回保留表中在ON筛选器中被过滤掉的行，这些新添加的行称为外部行，外部行中非保留表的属性被赋值为NULL。而where筛选则是最终的。

select * from dbo.Article a left join dbo.ArticleType b on a.TypeID = b.ID
select * from dbo.Article a left join dbo.ArticleType b on a.TypeID = b.ID and a.TypeID=1
select * from dbo.Article a left join dbo.ArticleType b on a.TypeID = b.ID where a.TypeID=1

 运行结果：

ID          TypeID      Title          ID          Name
---------------------------------------------------------
1           1           文章1            1           C#
2           1           文章2            1           C#
3           2           文章3            2           SQL

(3 行受影响)

ID          TypeID      Title        ID          Name
--------------------------------------------------------
1           1           文章1          1           C#
2           1           文章2          1           C#
3           2           文章3          NULL        NULL

(3 行受影响)

ID          TypeID      Title         ID          Name
--------------------------------------------------------
1           1           文章1           1           C#
2           1           文章2           1           C#

(2 行受影响)