可以做网站,帮企业做网站,网站建设后续说明,德惠网站建设sql语句中left join和inner join中的on与where的区别分析 原文:sql语句中left join和inner join中的on与where的区别分析关于SQL SERVER的表联接查询INNER JOIN 、LEFT JOIN和RIGHT JOIN#xff0c;经常会用到ON和WHERE的条件查询#xff0c;以前用的时候有时是凭感觉的… sql语句中left join和inner join中的on与where的区别分析 原文:sql语句中left join和inner join中的on与where的区别分析关于SQL SERVER的表联接查询INNER JOIN 、LEFT JOIN和RIGHT JOIN经常会用到ON和WHERE的条件查询以前用的时候有时是凭感觉的总是没有搞清楚今日亲自测试了下理解到了一些内容在此分享。 要测试首先我们来创建三张表数据库就根据自己的情况而定 创建表TestJoinOnOrWhere_A、TestJoinOnOrWhere_B、TestJoinOnOrWhere_C /****** 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestJoinOnOrWhere_C]([id] [int] NULL,[value] [int] NULL
) ON [PRIMARY] 表创建好了然后我们添加几条数据 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) 现在我们开始测试 语句1SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id b.id AND a.value 1
语句2SELECT * 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 12 1 2 33 2 NULL NULL 结果2 id value id value -------------------------------1 1 1 12 1 2 33 2 3 4 在网上查询到有的人说a.value 1没有生效其实不然它已经生效只是在左联接查询时左表的数据是不会受影响只有右表的数据会根据a.value 1条件取出左表(a表)Value为1的行通过上面两个语句的结果就可以看出那么我们用右表筛选条件会出现什么呢看看下面语句 语句3SELECT * 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 12 1 NULL NULL3 2 NULL NULL 以上结果看出也只是影响了右表的数据 语句4SELECT * 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 11 1 2 31 1 3 42 1 1 12 1 2 32 1 3 43 2 NULL NULL 从上面语句结果看出也只影响了右表的数据取出所有a表value对应为1的b表数据 所以在左联接查询时ON后面的条件只会影响右表相反右联接查询影响的就是左边的表数据 如果用WHERE呢我们看下下面的语句 语句5SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id b.id where a.value 1语句6SELECT * 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 12 1 2 3 结果6: id value id value -------------------------------1 1 1 1 可以从结果看出这个影响的结果就是全部的表就相当于通过ON条件联接查询查询的结果然后通过WHERE后面的条件取总体筛选 对于INNER JOIN 的ON条件会怎样影响呢先看下面语句执行结果 语句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
语句10SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id b.id WHERE b.value 1 结果7/9: id value id value -------------------------------1 1 1 12 1 2 3 结果8/10: id value id value -------------------------------1 1 1 1 上面通过WHERE和ON查询出来的结果是一样的由此可看出INNER JOIN 的ON条件和WHERE条件影响的都是一个效果影响整体的查询结果。 下面我们再来看下对于LEFT JOIN的三表查询对于WHERE和ON影响的结果 语句11SELECT 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 语句12SELECT 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 语句13SELECT 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 语句14SELECT 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 12 1 2 3 2 23 2 NULL NULL NULL NULL 结果12 a_id a_value b_id b_value c_id c_value -----------------------------------------------------1 1 1 1 1 12 1 NULL NULL NULL NULL3 2 NULL NULL NULL NULL 结果13 a_id a_value b_id b_value c_id c_value -----------------------------------------------------1 1 1 1 1 12 1 2 3 NULL NULL3 2 NULL NULL NULL NULL 结果14 a_id a_value b_id b_value c_id c_value -----------------------------------------------------1 1 1 1 NULL NULL2 1 2 3 2 23 2 NULL NULL NULL NULL 通过以上三表数据查询结果可以看出LEFT JOIN 查询对于ON的单独表条件始终只会影响条件表的右表如a.value1会影响b表关联的a表value字段值为1的行并不会限制a表的数据只显示value1的行RIGHT JOIN 影响效果恰恰相反 在使用ON条件时LEFT JOIN影响的是右侧的第二张第三张表并不会对最左侧的表影响所以对于a,b,c,三张表a表数据是不受ON条件影响的只会影响联接查询后的b或c数据 而WHERE就相当于在WHERE条件之前查询的数据当着一个表然后通过WHERE条件进行筛选数据所以影响的是整体。 创建于2015-04-03 本文来自wl131710转载请注明出处http://www.cnblogs.com/wanglu/p/4390612.html posted on 2018-01-16 11:18 NET未来之路 阅读(...) 评论(...) 编辑 收藏 转载于:https://www.cnblogs.com/lonelyxmas/p/8295584.html
