sql语句中left join和inner join中的on与where的区别分析

ningleesherry

　　关于SQL SERVER的表联接查询INNER JOIN 、LEFT JOIN和RIGHT JOIN，经常会用到ON和WHERE的条件查询，以前用的时候有时是凭感觉的，总是没有搞清楚，今日亲自测试了下，理解到了一些内容，在此分享。
　　要测试，首先我们来创建三张表，数据库就根据自己的情况而定
　　创建表TestJoinOnOrWhere_A、TestJoinOnOrWhere_B、TestJoinOnOrWhere_C

http://common.cnblogs.com/images/copycode.gif

/****** Object:  Table [dbo].[TestJoinOnOrWhere_A]    Script Date: 2015/4/3 14:34:41 ******/CREATE TABLE [dbo].[TestJoinOnOrWhere_A](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[TestJoinOnOrWhere_B]    Script Date: 2015/4/3 14:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TestJoinOnOrWhere_B](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]GO/****** Object:  Table [dbo].[TestJoinOnOrWhere_C]    Script Date: 2015/4/3 14:34:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[TestJoinOnOrWhere_C](    [id] [int] NULL,    [value] [int] NULL) ON [PRIMARY]

http://common.cnblogs.com/images/copycode.gif

　　表创建好了然后我们添加几条数据

http://common.cnblogs.com/images/copycode.gif

INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (2, 1)INSERT [dbo].[TestJoinOnOrWhere_A] ([id], [value]) VALUES (3, 2)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (2, 3)INSERT [dbo].[TestJoinOnOrWhere_B] ([id], [value]) VALUES (3, 4)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (1, 1)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (2, 2)INSERT [dbo].[TestJoinOnOrWhere_C] ([id], [value]) VALUES (3, 3)

http://common.cnblogs.com/images/copycode.gif

　　现在我们开始测试
语句1：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1语句2：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id　　结果1：
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　2    1       2     3
　　3    2      NULLNULL
　　结果2
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　2    1       2     3
　　3    2       3     4
　　在网上查询到，有的人说a.value = 1没有生效，其实不然，它已经生效，只是在左联接查询时，左表的数据是不会受影响，只有右表的数据会根据a.value = 1条件取出左表(a表)Value为1的行，通过上面两个语句的结果就可以看出，那么我们用右表筛选条件会出现什么呢？看看下面语句
语句3：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1　　结果3：
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　2    1       NULL NULL
　　3    2       NULLNULL
　　以上结果看出，也只是影响了右表的数据
语句4：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON  a.value = 1　　结果4：
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　1    1       2     3
　　1    1       3     4
　　2    1       1     1
　　2    1       2     3
　　2    1       3     4
　　3    2       NULLNULL
　　从上面语句结果看出，也只影响了右表的数据（取出所有a表value对应为1的b表数据）
　　所以在左联接查询时ON后面的条件只会影响右表，相反右联接查询影响的就是左边的表数据
　　如果用WHERE呢？我们看下下面的语句
语句5：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where a.value = 1语句6：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id where b.value = 1　　结果5:
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　2    1       2     3
　　结果6:
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　可以从结果看出，这个影响的结果就是全部的表，就相当于通过ON条件联接查询查询的结果，然后通过WHERE后面的条件取总体筛选
　　对于INNER JOIN 的ON条件会怎样影响呢？先看下面语句执行结果

http://common.cnblogs.com/images/copycode.gif

语句7：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1语句8：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1语句9：  SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE a.value = 1语句10：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id WHERE b.value = 1

http://common.cnblogs.com/images/copycode.gif

　　结果7/9:
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　2    1       2     3
　　结果8/10:
　　id   value  id    value
　　-------------------------------
　　1    1       1     1
　　上面通过WHERE和ON查询出来的结果是一样的，由此可看出，INNER JOIN 的ON条件和WHERE条件影响的都是一个效果，影响整体的查询结果。
　　下面我们再来看下对于LEFT JOIN的三表查询对于WHERE和ON影响的结果
　　语句11：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN   dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id
　　语句12：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id
　　语句13：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id AND b.value = 1
　　语句14：SELECT a.id AS a_id,a.value AS a_value,b.id AS b_id,b.value AS b_value,c.id AS c_id,c.value AS c_value FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN  dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1  LEFT JOIN dbo.TestJoinOnOrWhere_C AS c ON b.id = c.id AND c.value = 2
　　结果11：
　　a_id  a_value  b_id  b_value  c_id   c_value
　　-----------------------------------------------------
　　1     1           1        1          1       1
　　2     1           2        3          2       2
　　3     2           NULL   NULL    NULL  NULL
　　结果12：
　　a_id  a_value  b_id  b_value  c_id   c_value
　　-----------------------------------------------------
　　1     1           1        1          1       1
　　2     1           NULL   NULL    NULL  NULL
　　3     2           NULL   NULL    NULL  NULL
　　结果13：
　　a_id  a_value  b_id  b_value  c_id   c_value
　　-----------------------------------------------------
　　1     1           1        1          1       1
　　2     1           2        3          NULL  NULL
　　3     2           NULL   NULL    NULL  NULL
　　结果14：
　　a_id  a_value  b_id  b_value  c_id   c_value
　　-----------------------------------------------------
　　1     1           1        1          NULL  NULL
　　2     1           2        3          2        2
　　3     2           NULL   NULL    NULL  NULL
　　通过以上三表数据查询结果，可以看出，LEFT JOIN 查询，对于ON的单独表条件始终只会影响条件表的右表（如，a.value=1会影响b表关联的a表value字段值为1的行，并不会限制a表的数据只显示value=1的行），RIGHT JOIN 影响效果恰恰相反
　　在使用ON条件时LEFT JOIN影响的是右侧的第二张第三张表，并不会对最左侧的表影响，所以对于a,b,c,三张表，a表数据是不受ON条件影响的，只会影响联接查询后的b或c数据
　　而WHERE就相当于在WHERE条件之前查询的数据当着一个表，然后通过WHERE条件进行筛选数据，所以影响的是整体。