SQL左连接中的on and和on where的区别

转载,原文链接:http://blog.csdn.net/xingzhemoluo/article/details/39677891

SQL左右连接中的on and和on where的区别

使用left join时,on and和on where条件的区别如下:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录(涉及右表的会直接过滤掉,涉及左右表之间的条件过滤完之后,不符合的左表的记录都会返回同时右表置空)。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉,on后的条件用来生成左右表关联的临时表,where后的条件对临时表中的记录进行过滤。

实践是检验真理的唯一标准,接下来用实验来证明一下:

先上表结构以及数据:

SQL> create table A (id int, type int);
SQL> select * from A;
ID TYPE


     1          1
     2          1
     3          2

SQL> create table B(id int ,class int);
SQL> select * from B;

    ID      CLASS

     1          1
     2          2

SQL> select * from A left join B on A.id = B.id where A.type = 1;

    ID       TYPE         ID      CLASS

     1          1          1          1
     2          1          2          2

根据上面那段话的解释,where字句是在生成临时表以后再进行过滤的,也就是可以理解为就是一个左连接:select * from A left join B on A.id = B.id;
其运行结果如下:

SQL> select * from A left join B on A.id = b.id;

    ID       TYPE         ID      CLASS

     1          1          1          1
     2          1          2          2
     3          2

然后加上where A.type = 1对临时表进行过滤,除掉A.type不为1的,显然结果正确。


SQL> select * from A left join B on A.id = B.id and A.type = 1;

    ID       TYPE         ID      CLASS

     1          1          1          1
     2          1          2          2
     3          2

因为左连接不管on and语句是否为真都必须返回左表所有的记录,所以and A.type=1;没有起到任何作用。


SQL> select * from A left join B on A.id = B.id and B.class = 1;

    ID       TYPE         ID      CLASS

     1          1          1          1
     3          2
     2          1

根据上面那段话的解释:on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。显然左连接再加上新的条件:B.class = 1筛选掉第二行记录,结果正确。

SQL> select * from A left join B on A.id = B.id where B.class = 1;

    ID       TYPE         ID      CLASS

     1          1          1          1

原因通①。(①,②)和(③,④)就是关键词on and和on where的区别,但结果却完全不同。综上三个例子, where是生成临时表以后再进行过滤,对左右表都进行筛选。而and后面的语句如果是对left join中的左表进行过滤将不起任何作用,对右表进行过滤的话,那么左表还是返回所有行,只是右表会被过滤掉一部分行。

再来看看内连接inner join on and和 on where的区别:

由于刚开始表的数据不是太适合,所以先稍微更新一下,这样更好观察inner join和left join在and和where的不同之处。

SQL> update A set type = 2 where id = 1;

已更新 1 行。

SQL> select * from A;

    ID       TYPE

     1          2
     2          1
     3          2

SQL> select * from B;

    ID      CLASS

     1          1
     2          2

先看看and的:

SQL> select * from A inner join B on A.id = B.id and A.type = 1;

    ID       TYPE         ID      CLASS

     2          1          2          2

显然输出结果与左连接的不一样,先与没有and的内连接比较一下:
SQL> select * from A inner join B on A.id = B.id;

    ID       TYPE         ID      CLASS

     1          2          1          1
     2          1          2          2

显然如果按左连接的逻辑,这个结果就是错误的。但这是Oracle输出的,而不是我瞎打的,显然在内连接时与左连接不同了。这里on and条件和on where条件一样对生成以后的临时表同样会被过滤。显然A表id为1的type不为1,所以它被过滤了。

再上几组来验证一下上面这个猜想是否是正确的:

再来看看内连接的where:
SQL> select * from A inner join B on A.id = B.id where A.type = 1;

    ID       TYPE         ID      CLASS

     2          1          2          2

这个也是和左连接一样生成临时表然后进行过滤,不作解释。

SQL> select * from A inner join B on A.id = B.id and B.class = 1;

    ID       TYPE         ID      CLASS

     1          2          1          1


SQL> select * from A inner join B on A.id = B.id where B.class = 1;

    ID       TYPE         ID      CLASS

     1          2          1          1

对比发现:(⑤,⑥)和(⑦,⑧)结果都一样,也就是说内连接inner join on and 或者on where不管是对左表还是右表进行过滤,实际都是在生成临时表以后再进行过滤的,而且对左表和右表都起作用,这与左连接left join有本质的区别!!!

最后上俩没有使用连接语句的例子:

SQL> select * from A,B where A.id = B.id and A.type = 1;
    ID       TYPE         ID      CLASS

     1          1          1          1
     2          1          2          2

SQL> select * from A,B where A.type = 1 and A.id = B.id;

    ID       TYPE         ID      CLASS

     1          1          1          1
     2          1          2          2

比较简单,不作解释。

总结一下:

    在使用left join时,on和where条件的区别如下:  

1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。(实际上左连接中如果and语句是对左表进行过滤的,那么不管真假都不起任何作用。如果是对右表过滤的,那么左表所有记录都返回,右表筛选以后再与左表连接返回)
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉,on后的条件用来生成左右表关联的临时表,where后的条件对临时表中的记录进行过滤。

    在使用inner join时,不管是对左表还是右表进行筛选,on and和on where都会对生成的临时表进行过滤。

在这里插入图片描述

  • 4
    点赞
  • 27
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 在SQL语句,`WHERE` 和 `ON` 都是用于过滤查询结果的条件语句。但它们两个的使用场景不同,在不同的情况下应该使用不同的语句。 `WHERE` 语句用于过滤单表查询的结果,即从单个表筛选数据。例如: ``` SELECT * FROM table_name WHERE column_name = value; ``` `ON` 语句则是在使用连接查询(如 `INNER JOIN` 或 `LEFT JOIN`)时使用的条件语句,用于确定哪些行在两个表之间进行连接。例如: ``` SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; ``` 因此,总的来说,`WHERE` 语句适用于过滤单表查询的结果,而 `ON` 语句则是在使用连接查询时用于确定连接行的条件语句。 ### 回答2: 在SQL,WHERE 和 ON 都是用来在查询过滤数据的关键字,但它们的使用场景和功能有所不同。 WHERE: WHERE子句用于在SELECT、UPDATE和DELETE语句,通过逻辑条件过滤出满足要求的数据行。它通常在FROM子句之后、GROUP BY子句之前使用。WHERE条件用于从表选择具有特定属性或满足特定条件的行。在WHERE子句,可以使用比较运算符、逻辑运算符和其他条件来定义过滤规则。 示例:SELECT * FROM 表名 WHERE 条件; ON:ON子句主要用于连接两个或多个表进行联接查询。它通常在FROM子句之后、JOIN子句之前使用。ON条件指定了表与表之间的关联条件,用于在连接操作时指定如何将两个表关联在一起。ON条件是在JOIN操作使用的,并指定连接操作所基于的列。 示例:SELECT * FROM 表1 JOIN 表2 ON 表1.列名 = 表2.列名; 总结: - WHERE子句用于在查询结果过滤行,取决于表的数据; - ON子句用于在连接操作指定关联条件,连接两个或多个表。 这两者的不同之处在于: - WHERE 是对从单个表选择行进行过滤; - ON 则是在连接操作时,将多个表的数据进行关联。 通过这两个关键字,我们可以有效地查询和过滤出我们所需的数据。 ### 回答3: 在SQL,"WHERE"和"ON"是用来过滤或连接数据的两种关键字,它们具有不同的作用和使用场景。 "WHERE"子句用于在查询过程筛选出符合特定条件的记录。当我们需要对表的数据进行条件过滤时,使用"WHERE"来指定条件。例如,我们可以使用"WHERE"来选择特定日期范围内的销售记录或者某个状态下的订单。"WHERE"通常出现在"SELECT"语句之后,"FROM"语句之前。 "ON"子句则用于在查询过程连接两个或多个表,它通常用于"JOIN"操作。它用于指定两个表之间的连接条件,通过匹配这些条件来合并相关的行。"ON"通常出现在"JOIN"语句之后,指定连接条件。例如,我们可以使用"ON"来连接两个表的共同列,根据这些列的匹配结果获取相关的数据。 区别在于,"WHERE"是用于筛选行的,而"ON"是用于连接表的。"WHERE"用于对单个表的数据进行条件过滤,而"ON"用于连接多个表并确定它们之间的关系。"WHERE"通常使用在"SELECT"语句,而"ON"通常使用在"JOIN"操作。 总结起来,"WHERE"和"ON"在SQL查询具有不同的作用和用途。"WHERE"用于过滤行,而"ON"用于连接表。它们的区别在于它们操作的对象(行还是表)以及它们的使用位置("WHERE"在"FROM"之后,"ON"在"JOIN"之后)。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值