sql语句中left join、inner join中的on与where的区别
- keldy专栏 - CSDN博客
sql语句中left join、inner join中的on与where的区别
table a(id, type):
id&&&& type
----------------------------------
1&&&&& 1&&&&&&&&
2&&&&& 1&&&&&&&&&
3&&&&& 2&&&&&&&&&
table b(id, class):
id&&& class
---------------------------------
sql语句1：select a.*, b.* from a left join b on a.id = b.id and a.type = 2;
sql语句2：select a.*, b.* from a left join b on a.id = b.id where a.type = 1;
sql语句3：select a.*, b.* from a left join b on a.id = b.id and b.class = 1;
sql语句1的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
----------------------------------------
1&&&&&&& 1&&&&&&&&&&&&&&&&&&&
2&&&&&&& 1&&&&&&&&&&&&&&&&&&&
3&&&&&&& 2&&&&&&&&&&&&&&
sql语句2的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
----------------------------------------
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&& 2&&&&&&& 2
sql语句3的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
----------------------------------------
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&&
3&&&&&&& 2&&&&&&&&&&&
由sql语句1可见，相当于做了两次的left join ，左表的全部记录将全部被查询显示，on 后面的条件再做一次筛选，因为在原来的结果集中没有与a.type =2 关联得到的b表中的值，所以这时候b表的数据就都不显示
sql语句2中，加了where条件，就先过滤where条件中的值；由sql语句3可见，on后面的条件中，右表的限制条件将会起作用。
**********************************************************************************
sql语句4：select a.*, b.* from a inner join b on a.id = b.id and a.type = 1;
sql语句5：select a.*, b.* from a inner join b on a.id = b.id where a.type = 1;
sql语句6：select a.*, b.* from a, b where a.id = b.id and a.type = 1;
sql语句7：select a.*, b.* from a, b where a.type = 1 and a.id = b.
这四条语句的执行结果一样，如下：
a.id&&& a.type&&& b.id&&& b.class
----------------------------------------
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&& 2&&&&&&& 2
由此可见，inner join 中on后面的限制条件将全部起作用，这与where的执行结果是一样的。另外，where语句与inner join确实能得到相同的结果，只是效率不同（这个我没有测试过，不过我相信这个结论）。
但是sql语句6是否比sql语句7的效率要低一些，我没有足够的数据量来测试，不过我也相信是如此的。
本文来自CSDN博客，转载请标明出处：
我的热门文章关于SQL SERVER的表联接查询INNER JOIN 、LEFT JOIN和RIGHT JOIN，经常会用到ON和WHERE的条件查询，以前用的时候有时是凭感觉的，总是没有搞清楚，今日亲自测试了下，理解到了一些内容，在此分享。
要测试，首先我们来创建三张表，数据库就根据自己的情况而定
创建表TestJoinOnOrWhere_A、TestJoinOnOrWhere_B、TestJoinOnOrWhere_C
/****** Object:
Table [dbo].[TestJoinOnOrWhere_A]
Script Date:
14:34:41 ******/
CREATE TABLE [dbo].[TestJoinOnOrWhere_A](
[id] [int] NULL,
[value] [int] NULL
) ON [PRIMARY]
/****** Object:
Table [dbo].[TestJoinOnOrWhere_B]
Script Date:
14:34:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[TestJoinOnOrWhere_B](
[id] [int] NULL,
[value] [int] NULL
) ON [PRIMARY]
/****** Object:
Table [dbo].[TestJoinOnOrWhere_C]
Script Date:
14:34:41 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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)
&现在我们开始测试
语句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
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 12 & &1 & & & 2 & & 33 & &2 & & &NULL NULL
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 12 & &1 & & & 2 & & 33 & &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
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 12 & &1 & & &&NULL NULL3 & &2 & & & NULL NULL
以上结果看出，也只是影响了右表的数据
语句4：SELECT * FROM dbo.TestJoinOnOrWhere_A AS a LEFT JOIN dbo.TestJoinOnOrWhere_B AS b ON
a.value = 1
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呢？我们看下下面的语句
语句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
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 12 & &1 & & & 2 & & 3
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 1
可以从结果看出，这个影响的结果就是全部的表，就相当于通过ON条件联接查询查询的结果，然后通过WHERE后面的条件取总体筛选
对于INNER JOIN 的ON条件会怎样影响呢？先看下面语句执行结果
SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND a.value = 1
SELECT * FROM dbo.TestJoinOnOrWhere_A AS a INNER JOIN dbo.TestJoinOnOrWhere_B AS b ON a.id = b.id AND b.value = 1
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
id & value &id & &value
-------------------------------1 & &1 & & & 1 & & 12 & &1 & & & 2 & & 3
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
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
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
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
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.value=1会影响b表关联的a表value字段值为1的行，并不会限制a表的数据只显示value=1的行），RIGHT JOIN 影响效果恰恰相反
& & & 在使用ON条件时LEFT JOIN影响的是右侧的第二张第三张表，并不会对最左侧的表影响，所以对于a,b,c,三张表，a表数据是不受ON条件影响的，只会影响联接查询后的b或c数据
而WHERE就相当于在WHERE条件之前查询的数据当着一个表，然后通过WHERE条件进行筛选数据，所以影响的是整体。
本文来自wl131710，转载请注明出处：&
阅读(...) 评论()sponsored links
sql语句中left join、inner join中的on与where的区别
table a(id, type):
id&&&& type
1&&&&& 1&&&&&&&&
2&&&&& 1&&&&&&&&&
3&&&&& 2&&&&&&&&&
table b(id, class):
id&&& class
sql语句1：select a.*, b.* from a left join b on a.id = b.id and a.type = 1;
sql语句2：select a.*, b.* from a left join b on a.id = b.id where a.type = 1;
sql语句3：select a.*, b.* from a left join b on a.id = b.id and b.class = 1;
sql语句1的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&& 2&&&&&&& 2
3&&&&&&& 2&&&&&&&&&&&&&&
sql语句2的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&& 2&&&&&&& 2
sql语句3的执行结果为：
a.id&&& a.type&&& b.id&&& b.class
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&&
3&&&&&&& 2&&&&&&&&&&&
由sql语句1可见，left join 中左表的全部记录将全部被查询显示，on 后面的条件对它不起作用，除非再后面再加上where来进行筛选，这就是sql语句2了；由sql语句3可见，on后面的条件中，右表的限制条件将会起作用。
**********************************************************************************
sql语句4：select a.*, b.* from a inner join b on a.id = b.id and a.type = 1;
sql语句5：select a.*, b.* from a inner join b on a.id = b.id where a.type = 1;
sql语句6：select a.*, b.* from a, b where a.id = b.id and a.type = 1;
sql语句7：select a.*, b.* from a, b where a.type = 1 and a.id = b.
这四条语句的执行结果一样，如下：
a.id&&& a.type&&& b.id&&& b.class
1&&&&&&& 1&&&&&&&&&&& 1&&&&&&& 1
2&&&&&&& 1&&&&&&&&&&& 2&&&&&&& 2
由此可见，inner join 中on后面的限制条件将全部起作用，这与where的执行结果是一样的。另外，where语句与inner join确实能得到相同的结果，只是效率不同（这个我没有测试过，不过我相信这个结论）。
但是sql语句6是否比sql语句7的效率要低一些，我没有足够的数据量来测试，不过我也相信是如此的。
原文：/hgwy/articles/1691689.html
SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析,需要的朋友可以参考下. 代码如下: ---在仓储管理中经常会碰到的一个问题 一.关于LIFO与FIFO的简单说明 ---FIFO: First in, First out.先进先出. ---LIFO: Last in, First out.后进先出. --如货物A:本月1日购买10件,单 ...
做为SQL存储过程和.NET的新手,下面的指导还是很有用的,自己这一段刚刚接触这些东西,搜集了一些相关的东西,能使新手较容易上手,当然啦, 要精通和熟练应用,还是要看更多更深的资料的,高手请不要见笑. 以下内容均来自网络,只供参考学习: -------- 存 储过程与SQL语句的恩怨情仇 程序员说:存储过程还是SQL语句,这是一个问题. 江湖现在有三个流派, ...
这篇文章主要介绍了SQL语句的并集UNION,交集JOIN内连接,外连接,交叉连接CROSS JOIN笛卡尔积,差集NOT IN ,需要的朋友可以参考下1. a. 并集UNION SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2 b. 交集JOIN SE ...
有时候不需要出库,在数据库中修改一些内容,就需要用到下列语句! sql语句为:UPDATE `table_name` SET `field_name` = replace (`field_name`,'from_str','to_str') WHERE -- 代码说明: table_name -- 表的名字 field_name -- 字段名 from_st ...
类似于一下数据表中的数据:
如果想查询其中的某一个ID,如查询71这一项,sql语句如下: SELECT
FROM TB_ProjectManagement
WHERE AttaID like ''%,' +@AttaID+',%'' or AttaID like '''+@AttaID+',%'' or AttaID like ''%,' ...
解决问题的场景:执行人名字中含有_ 的,在查询中用_或含有_ 的字段,列表中查不到信息 解决问题的思路:获取从MyBatis的mapper Xml文件中,找到原始的Sql语句(条件判断处理已好的SQL),在含有Like的查询字段值里若包含特殊字符,则转义 ,并在查询条件后面添加 & ESCAPE '/'.处理后的Sql语句类似:select * f ...您的位置： >
SQL join中on与where区别
学习标签：
本文导读：数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。例如在使用left jion时，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录；where条件是在临时表生成好后，再对临时表进行过滤的条件。
on、where、having的区别
1、on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后。有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。
2、where应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。
3、在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢。
4、如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。
5、在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什幺时候起作用，然后再决定放在那里
1、假设有两张表：
size& &name
10&&& &AAA
20&&& &BBB
20& && CCC
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的过程：
<span style="color: #、中间表on条件: tab1.size = tab2.size
&tab1.id&& tab1.size&& tab2.size&& tab2.name
&1& 10& 10& AAA
& 2& 20& 20 BBB
& 2 20 20 CCC
& 3 30 (null) (null)
<span style="color: #、再对中间表过滤where 条件：tab2.name=&AAA&
tab1.id&& tab1.size& &tab2.size&& tab2.name
1 & 10 &10 & AAA
第二条SQL的过程
中间表on条件: tab1.size = tab2.size and tab2.name=&AAA&(条件不为真也会返回左表中的记录)
tab1.id tab1.size tab2.size tab2.name
1& 10& 10& AAA
2& 20 &(null) &(null)
3& 30& (null) &(null)
2、实例2详细说明
SQL语句如下：
LEFT JOIN 表2 ON 表1.id = 表2.id AND 表2.Name != &#39;ff&#39;
WHERE 表1.NAME != &#39;aa&#39;
步骤1：返回笛卡尔积（SELECT * FROM 表1 CROSS JOIN 表2）
步骤2：应用ON筛选器（当前的条件为 表1.id = 表2.id AND 表2.Name != &#39;ff&#39;）
步骤3：添加外部行
这一步只对OUTER JOIN起作用，如果是LEFT JOIN会以左边的表为保留表，如果是RIGHT JOIN会以右边的表为保留表。所谓外部行是指，保留表中的行。即使第二步的ON过滤掉了一些行，在这一步，会根据保留表添加第二步过滤掉的行。当前的例子，不存在这种情况。
步骤4.应用WHERE筛选器(当前是Name != &aa&)过滤前三步所生成虚拟表的数据。
1、如果SQL用的是Left Join ，On后面的条件对Left的表没有作用，只对Right的表有过滤作用，Where语句可以对Left的表有过滤作用
2、如果SQL用的是Right Join ，On后面的条件对Right的表没有作用，只对Left的表有过滤作用，Where语句可以对Right的表有过滤作用
您可能感兴趣
一月好评排行榜