表连接on 和where的区别

最新推荐文章于 2024-07-10 16:54:26 发布
原创 最新推荐文章于 2024-07-10 16:54:26 发布 · 1.7k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#and  where区别

本文深入探讨了SQL中不同连接类型（INNER JOIN, LEFT JOIN, RIGHT JOIN）的特点与区别，通过实例解析了ON与WHERE子句在数据筛选过程中的作用及效率差异。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

最近项目中碰到一个有关多表链接on 和where条件下数据的变换问题。做个简单整理：
select count(*) from “SAPHANADB”.“CDHDR” SCR
inner JOIN “SAPHANADB”.“CDPOS” SCS ON SCR.MANDANT = SCS.MANDANT AND SCR.OBJECTCLAS = SCS.OBJECTCLAS
AND SCR.OBJECTID = SCS.OBJECTID
AND SCR.CHANGENR = SCS.CHANGENR and (SCS.tabname=‘MARC’ or SCS.tabname=‘MBEW’ )
where SCR.objectclas = ‘MATERIAL’
在这里插入图片描述
可以看到显示有16805条数据，将where改为and
在这里插入图片描述
发现数据量没有变化。然后我们将inner join改为left join 和right join 看看
left where left andleft join

right and
right whererighr join

很明显，数据量会发生变化。这是因为
当使用 left join 时，无论on的条件是否满足，都会返回左表的所有记录，对于满足的条件的记录，两个表对应的记录会连接起来，对于不满足条件的记录，那右表字段全部是null
当使用 right join 时，on与where类似，只不过是全部返回右表的所有记录
当使用 inner join 时，on与where完全相同。
on是在生成中间的临时表时起作用的先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，在数据比较多的情况下效率上要比较好点，where是生成临时表之后对连接表再进行过滤。

Mysql 多表连接中on 和where的区别详解！
qq_39751320的博客
05-21 1972
数据库在通过连接多表返回记录时，会先生成一张临时表，然后在将这张临时表返回。 对于Left Join on和where条件的区别 on条件是生成临时表时的条件，无论on的条件是否为真，左表的数据都会返回，只是右表数据这是都会变成null，这是Left Join的特性(Right Join同) where是在临时表创建完后根据条件进行筛选 对于Inner Join 两者并没有区别，因为Inner Join不像Left Join或Right Join一定要返回左表/右表的所有数据，而是根据指定关系进行表连接
表连接join查询与where后使用子查询的性能分析
11-21 1298
声明：本文摘自https://www.cnblogs.com/cdf-opensource-007/p/6540521.html 子查询就是在一条查询语句中还有其它的查询语句，主查询得到的结果依赖于子查询的结果。 子查询的子语句可以在一条sql语句的FROM，JOIN，和WHERE后面，本文主要针对在WHERE后面使用子查询与表连接查询的性能做出一点分析。 对于表连接查询和子查询性...
连接查询中WHERE 和 ON 的区别
m0_50957668的博客
09-08 1272
在 SQL 连接查询时关于使用 WHERE 和 JOIN ON 的区别讨论以及社区语法规范推荐
在SQL里做表关联时，on与where的区别
Cheng的博客
05-30 1958
left join 和 right join 时，on 的关联条件只会对关联表的数据进行过滤，无法对主表的数据进行过滤，若想对主表的数据进行筛选，需要where里加条件进行筛选。 select * from userInfo a left join roleInfo b on a.userId = b.userId where a.xxx = '' --在这个SQL里...
mysql 表连接 on_mysql 表连接
weixin_42464697的博客
01-18 326
表连接当需同时显示多个表中的字段时，就可以用表连接来实现这样的功能。表连接分为内连接和外连接。它们区别在于内连接仅选出两张表中互相匹配的记录。而外连接会显示其他不匹配的记录。列如有一张表存着员工姓名以及入职日期还有工资和部门id ，另一张表存着部门id和部门名首先我们先创建这2个表。创建员工信息表：创建员工信息表：create table emp(ename varchar(20),hiredat...
on条件与where条件的区别
haibo211314的专栏
05-01 3385
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下： 1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。 2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。 假设有两张表： 表1：tab2 id ...
mysql内外连接时，on跟where的区别
ps0833的博客
12-24 676
外连接(left join、right join) left join…on…and…where…and… on...and... 是对右表中的数据进行筛选，返回的是左表中的所有记录 where...and... 是对on之后的记录进行筛选 内连接(inner join) inner join…on…and…where…and… on...and... 是对两表中的数据进行筛选，返回的是两表中的...
关联查询实践指南：on和where条件在Mysql中的应用场景
七一
12-05 1377
今天在开发中遇到一个问题，在两个表关联查询时，最终得到的结果和预想结果不一致，经过排查后，我发现自己犯了一个很低级的错误，这样会导致查询出来的结果莫名其妙的变多或者变少。准备两张表dept,emp，一张员工表，一张部门表，内容如下；情况一：问题sql： 错误结果：这是因为右表的过滤条件放在where中会被过滤掉，而不会显示0条正确写法： 正确结果：情况二：问题sql： 错误结果：这是因为left join 做连接时，左表会显示全部数据，哪怕匹配不上为null的数据，左表过滤应当放入where条件中正确写法：
数据库内连接、左外连接、右外连接中的on、and、where条件使用
qq_45138120的博客
04-03 9608
使用on条件，A为主表；使用on条件，B为主表；使用on、and主表条件；使用on、where主表条件；使用on、and条件，a.type1；使用on、where条件，a.type1；使用on、and从表条件；使用on、where从表条件；使用on、and从表条件，从表Bcard1111；使用on、where条件，从表Bcard1111；inner内链接，使用on、and条件；inner内连接，使用on、where条件。
on与where的区别
qq_42102428的博客
08-16 1万+
on与where的区别 on是先对表进行筛选再生成关联表，where是先生成关联表再对关联表进行筛选，on执行的优先级高于left join,而where的优先级低于left join 当我们使用on关键字时，会先根据on后面的条件进行筛选，条件为真时返回该行,由于on的优先级高于left join，所以left join关键字会把左表中没有匹配的所有行也都返回，然后生成临时表返回 where对与...
on 和where条件的放置详解
热门推荐
只有变秃 才能变强
10-11 1万+
on 和where条件的放置详解 总结： 对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面 对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面。 数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion...
MySQL的on和where的区别，实战注意事项
Sun1090889283的博客
10-19 4483
MySql中on和where的注意事项
Left Join，Right Join，Inner Join与where连接的区别（通俗易懂）
淅沥的博客
11-15 622
连表是SQL常用的操作，但是连表不一样，得到的结果也不一样（在数据量大的时候尤为明显），这里就是对关联表中一些筛选用的关键字的具体区别做了一个详细的梳理。
表连接时on和where的区别
changqingming的专栏
06-25 3186
今天遇到一个求某月所有天数的统计结果，如果某日的结果是0也需要显示出来，即: 日期                  交易次数   交易金额 2009-4-01           1              10 2009-4-02           2              20 2009-4-03           0              0 2009-4
联表条件on和where的区别
Dream_6666的博客
04-14 3198
联表时的联表条件可以写在on后面，也可以写在where后面，有什么区别？ 第一种：等值连接时的联表条件 写在 on 后面： select * from A a join B b on a.xx = b.yy 写在where后面： select * from A a join B b where a.xx = b.yy 这两种查询出的结果一样。 第二种：左连接 或 右连接 时的联表条件, 只有一个联表条件 联表条件只能写在 on 后面，否则语法错误 select * from A a left join B
外连接的on和where的区别
weixin_43850781的博客
09-30 705
on和where的区别
on和where区别
qq_43723636的博客
07-10 489
左连接（LEFT JOIN）返回左表（这里是 Employees 表）的所有行，如果右表（这里是 EmployeeUNI 表）中没有匹配的行，则结果集中的对应列将为 NULL。WHERE 子句返回满足特定条件的行，这里的条件是 Employees.id 等于 EmployeeUNI.id。内连接（INNER JOIN）只返回两个表中都有匹配的行。
sql中on和where的区别在哪
qq_43732763的博客
06-16 5980
sql当中on和where
MYSQL之on和where的区别浅谈
xiaoweids的博客
07-12 1623
有时候如果这先后顺序不影响中间结果的话，那最终结果是相同的。但因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的。根据上面的分析，可以知道where也应该比having快点的，因为它过滤数据后才进行sum，所以having是最慢的。但也不是说having没用，因为有时在步骤3还没出来都不知道那个记录才符合要求时，就要用having了。在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。
多表查询on和where的区别
最新发布
06-09
<think>我们正在讨论多表查询中ON和WHERE的区别及使用场景。根据提供的引用，特别是引用[1]和引用[5]中的内容，可以总结如下：1.**ON子句**：-专门用于JOIN操作中，用于指定表之间的连接条件。-作用：决定两个表如何连接，即哪些行应该被匹配在一起。-应用场景：在连接表时，基于某些列的值（如外键关系）匹配行。例如，连接订单表和客户表，基于客户ID（`ONorders.customer_id=customers.customer_id`）。2.**WHERE子句**：-用于过滤结果集，可以在SELECT、UPDATE、DELETE语句中使用。-作用：对FROM子句返回的结果集（包括连接后的结果）进行过滤，只保留满足条件的行。-应用场景：在连接完成后，基于某些条件筛选行。例如，只查询订单金额大于100的订单（`WHEREorders.amount>100`）。**关键区别**：-**执行顺序**：在连接操作中，ON子句在连接过程中执行（决定如何连接），而WHERE子句在连接完成后执行（过滤结果）。-**影响范围**：ON子句影响连接本身（决定哪些行被包含在连接结果中），WHERE子句影响最终结果（决定连接结果中保留哪些行）。-**性能影响**：在连接条件上使用ON子句（而不是WHERE）可以让优化器更有效地使用索引，避免不必要的全表扫描。**外连接的特殊情况（引用[2][4]）**：-对于左外连接（LEFTJOIN）：-ON子句用于指定连接条件，并决定右表中哪些行与左表匹配。-WHERE子句则用于过滤最终结果（包括左表和右表的列）。-如果对右表的列在WHERE中加条件，会导致未匹配的行被过滤掉（因为右表列值为NULL，不满足条件），从而退化为内连接的效果。-若要对右表进行过滤而不影响左表行数，应将这些条件放在ON子句中。###示例说明####场景1：内连接（INNERJOIN）```sqlSELECT*FROMordersoINNERJOINcustomerscONo.customer_id=c.customer_id--连接条件：只匹配有订单的客户WHEREo.amount>100;--连接后过滤：只显示金额>100的订单```####场景2：左外连接（LEFTJOIN）```sql--查询所有客户及其订单（即使没有订单）SELECT*FROMcustomerscLEFTJOINordersoONc.customer_id=o.customer_id--连接条件：匹配客户的订单WHEREo.order_date>='2024-01-01';--注意：这会过滤掉没有订单的客户（因为o.order_date为NULL）--正确保留所有客户的写法（将日期条件移至ON子句）：SELECT*FROMcustomerscLEFTJOINordersoONc.customer_id=o.customer_idANDo.order_date>='2024-01-01';--在连接时过滤右表```###核心原则1.**连接条件放ON子句**：关联表时，匹配条件（如`table1.column=table2.column`）应放在ON子句中。2.**结果过滤放WHERE子句**：业务过滤条件（如日期范围、数值阈值）放在WHERE子句，除非需要保留主表所有行（外连接时）。3.**外连接的过滤陷阱**：LEFTJOIN的右表条件写在WHERE中会导致主表数据丢失。---###相关问题1.如何避免左外连接中WHERE条件导致的主表数据丢失？2.多表连接时，ON子句能否包含非等值条件（如范围、函数）？3.使用WHERE过滤连接条件（如`table1.id=table2.id`）会产生什么后果？4.如何用执行计划（EXPLAIN）分析ON和WHERE的性能差异？</think> ### 📚 多表查询中 `ON` 和 `WHERE` 的区别及使用场景 基于引用内容的核心总结（引用[1][2][4][5]）： #### 🔍 **本质区别** | **特性** | **`ON` 子句** | **`WHERE` 子句** | |----------------|--------------------------------------------------|---------------------------------------------------| | **作用阶段** | 连接过程中执行（连接表时生效） | 连接完成后执行（结果集过滤） | | **核心用途** | 定义表间的**连接匹配条件**（如外键关系） | 对最终结果集进行**数据过滤** | | **结果影响** | 决定哪些行被包含在连接结果中 | 决定连接结果中保留哪些行 | | **外连接表现** | 右表不满足条件时，左表数据仍保留（补 `NULL`） | 直接过滤掉不满足条件的行（可能导致左表数据丢失） | --- #### ⚙️ **使用场景对比** **示例表结构**： - `orders`（订单表）：`order_id`, `customer_id`, `amount` - `customers`（客户表）：`customer_id`, `name` ##### 场景1：基础连接匹配（用 `ON`） ```sql -- 获取所有订单及对应客户名（包含无客户名的订单） SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id -- 连接条件在此定义 ``` ✅ **作用**： 连接时通过 `customer_id` 匹配，右表无匹配时保留左表数据（`name` 显示为 `NULL`）。 ##### 场景2：连接后过滤（用 `WHERE`） ```sql -- 仅显示订单金额>100的订单及客户名 SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE o.amount > 100 -- 结果集过滤在此定义 ``` ✅ **作用**： 先完成连接操作，再过滤掉 `amount <= 100` 的订单。 ##### ❗ 易错场景：外连接中混淆二者 ```sql -- ❌ 错误：意图保留所有订单，但实际丢失了部分左表数据 SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.name IS NOT NULL -- WHERE过滤导致LEFT JOIN退化为INNER JOIN ``` ✅ **修正方案**（将过滤条件移到 `ON`）： ```sql SELECT o.order_id, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id AND c.name IS NOT NULL -- 连接时直接过滤右表 ``` --- #### 💡 **关键结论**（引用[1][5]） 1. **连接必用 `ON`** 表关联匹配条件（如 `a.id=b.id`）必须写在 `ON` 中，避免笛卡尔积或性能问题[^1]。 2. **过滤优选 `WHERE`** 业务筛选条件（如日期范围、数值阈值）推荐写在 `WHERE`。 3. **外连接慎用 `WHERE`** 左外连接需保留主表数据时，右表过滤条件应写在 `ON` 中，否则会丢失主表数据（引用[2][4]）。 4. **性能影响** `ON` 条件在连接时过滤可减少临时表大小，比 `WHERE` 更高效（尤其关联字段有索引时）[^1][^5]。 --- ### 🔄 **示意图：执行流程差异** ```mermaid graph TD A[开始查询] --> B{JOIN操作} B --> C[用ON条件匹配表数据] C --> D[生成连接结果集] D --> E{应用WHERE过滤} E --> F[返回最终结果] ``` --- ### 📌 相关问题 1. 如何避免左外连接中 `WHERE` 条件导致主表数据丢失？ 2. 多表连接时，`ON` 子句能否包含非等值条件（如 `ON a.date > b.date`）？ 3. 如何用执行计划（`EXPLAIN`）验证 `ON` 和 `WHERE` 的性能差异？ 4. 为什么说 `LEFT JOIN` 的 `WHERE` 条件错误使用会退化为内连接？
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值