数据库中使用on,where,having的区别

本文详细介绍了SQL查询中ON和WHERE子句的区别,以及它们在左外连接(LEFT JOIN)中的应用。ON用于在创建临时报表前过滤数据,而WHERE则在临时报表生成后筛选结果。HAVING和WHERE同样涉及筛选,HAVING用于聚集函数计算后的筛选,必须与GROUP BY结合使用。在LEFT JOIN中,WHERE对左表的限制不会生效,但会影响右表的结果。文章通过实例解释了在多个限制条件下如何正确使用ON和WHERE来获取期望的查询结果。

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

on和where

        所有的查询都回产生一个中间临时报表,查询结果就是从返回临时报表中得到。on和where后面所跟限制条件的区别,主要与限制条件起作用的时机有关,on根据限制条件对数据库记录进行过滤,然后生产临时报表;而where是在临时报表生产之后,根据限制条件从临时报表中筛选结果。
总结:在左外连接中,on会返回左表中的所有记录;而where中,此时相当于inner join,只会返回满足条件的记录。
速度:因为on限制条件发生时间较早,产生的临时报表数据集要小,因此on的性能要优于where。

having和where

        having和where的区别也是与限制条件起作用时机有关,having是在聚集函数计算结果出来之后筛选结果,查询结果只返回符合条件的分组,having不能单独出现,只能出现在group by子句中。而where是在计算之前筛选结果,如果聚集函数使用where,那么聚集函数只计算满足where子句限制条件的数据。
总结:where即可以和select等其他子句搭配使用,也可以和group by子句搭配使用,where的优先级要高于having。
速度:因为where在聚集函数之前筛选数据,having在计算之后筛选分组,因此where的性能要优于having

举例:在左连接时使用不同多个限制条件需要注意的地方

mysql> select * from card;
+------+-----------+
| id     | name    |
+------+-----------+
|    1   | 饭卡      |
|    2   | 建行卡   |
|    3   | 农行卡   |
|    4   | 工商卡   |
|    5   | 邮政卡   |
+------+-----------+

mysql> select * from person;
+------+--------+--------+
 | id    | name | cardId|
+------+--------+--------+
 |    1  | 张三   |      1  |
 |    2  | 李四   |      3  |
 |    3  | 王五   |      6  |
+------+--------+--------+

mysql> select * from person p left join card c on cardId=c.id;
+------+--------+---------+--------+-----------+
 |  id   | name | cardId |  id   |  name      |
+------+--------+---------+--------+-----------+
 |    1  | 张三   |      1   |     1    | 饭卡      |
 |    2  | 李四   |      3   |     3    | 农行卡  |
 |    3  | 王五   |      6   | NULL | NULL    |
+------+--------+---------+--------+-----------+
left join查询会从左表那里返回所有的行,即使在右表中没有匹配的行。查询中on条件只有一个,因此不存在特殊注意之处。但是当我们on条件如果存在多个时候会出现一些与我们预期不符的查询结果。

例如此时我希望对上述的查询结果再限制一个条件,我只想要p.id=1的法外狂徒的信息。

mysql> select * from person p left join card c on cardId=c.id and p.id = 1;
+------+--------+---------+--------+--------+
 |  id   | name | cardId |  id      | name |
+------+--------+---------+--------+--------+
 |    1  | 张三   |      1   |    1     | 饭卡   |
 |    2  | 李四   |      3   | NULL | NULL |
 |    3  | 王五   |      6   | NULL | NULL |
+------+--------+---------+--------+--------+
为什么已经限制了p.id=1 却查询结果还有其他人呢?我们换用where约束试试。

mysql> select * from person p left join card c on cardId=c.id where p.id = 1;
+------+--------+--------+------+--------+
 | id    | name | cardId|  id   | name |
+------+--------+--------+------+--------+
 |    1  | 张三   |      1  |    1   | 饭卡  |
+------+--------+--------+------+--------+

这次确实只有p.id=1的张三了。那为什么第一个查询语句会与预期不符?回顾一下left join的定义,左边表会返回所有行,所以left join如果对左边表进行约束的话是不会生效的。但是,对left join的右边表添加条件的话是生效的!

### SQL HAVING 子句使用教程 HAVING 子句主要用于在SQL查询中对已经分组的数据进行条件筛选。当与 `GROUP BY` 结合使用时,可以针对每个分组应用特定的条件来过滤结果集[^1]。 #### 基本语法结构 ```sql SELECT column_name(s), aggregate_function(column_name) FROM table_name WHERE condition GROUP BY column_name(s) HAVING group_condition; ``` - `column_name(s)` 表示要检索的一列或多列。 - `aggregate_function()` 是聚合函数如 COUNT(), SUM() 等。 - `table_name` 是目标表名。 - `condition` 定义行级过滤标准。 - `group_condition` 则是对各分组设定的额外约束条件。 #### 实际案例分析 假设有一个名为`sakila` 的电影租赁数据库,其中包含一张记录客户租借情况的事实表`rental` 描述影片信息维度表`film` 。现在想要找出被出租次数超过平均数目的那些电影及其对应的出租频次: ```sql -- 计算每部电影的出租数量,并选出高于平均水平者 WITH FilmRentals AS ( SELECT f.title, COUNT(r.rental_id) as rental_count FROM film f JOIN inventory i ON f.film_id = i.film_id JOIN rental r ON i.inventory_id = r.inventory_id GROUP BY f.title ), AverageRentalCount AS( SELECT AVG(rental_count) avg_rentals FROM FilmRentals ) SELECT fr.title, fr.rental_count FROM FilmRentals fr CROSS JOIN AverageRentalCount arc WHERE fr.rental_count > arc.avg_rentals; ``` 上述例子展示了如何利用 CTE (Common Table Expressions),即公共表达式简化复杂查询逻辑;同时也体现了 HAVING 子句配合子查询完成更精细的数据挖掘任务的能力[^2]。 #### 关键点总结 - **作用范围**:HAVING 只能应用于已分组后的数据集合上; - **区别 WHERE** :WHERE 对原始未分组前的数据起效,而 HAVING 针对于经过汇总计算之后的结果施加限制; - **支持运算符** : 支持大多数关系操作符 (=,<>,<,<=,>,>=,!>)以及IN/NOT IN、LIKE等特殊用途的操作符; - **嵌套查询** : 允许在HAVING 中嵌入子查询来进行跨表关联或者动态获取阈值参数。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值