What is the difference between WHERE and HAVING clauses in SQL?
The main difference between WHERE and HAVING is the filtering timing: 1. WHERE filters rows before grouping, acting on the original data, and cannot use the aggregate function; 2. HAVING filters the results after grouping, and acting on the aggregated data, and can use the aggregate function. For example, when using WHERE to screen high-paying employees in the query, then group statistics, and then use HAVING to screen departments with an average salary of more than 60,000, the order of the two cannot be changed. WHERE always executes first to ensure that only rows that meet the conditions participate in the grouping, and HAVING further filters the final output based on the grouping results.
The difference between WHERE and HAVING in SQL comes down to when each filter is applied — especially in relation to grouping. In short:
-
WHEREfilters rows before they are grouped. -
HAVINGfilters groups or aggregated results after the grouping is done.
If you're using GROUP BY , that's where this distinction really matters.
Filtering Before Grouping with WHERE
Use WHERE when you want to narrow down which rows go into the grouping process. It works on individual rows, not aggregated values.
For example:
SELECT department, COUNT(*) AS employee_count FROM employees WHERE salary > 50000 GROUP BY department;
Here, only employees earning more than $50k are included before the database groups them by department. The filtering happens early, so it affects the final counts.
You can't use aggregate functions like COUNT() , SUM() , etc., in a WHERE clause — because those values don't exist yet at that stage of the query.
Key points:
- Filters raw data.
- Cannot reference aggregate functions.
- Runs before grouping.
Filtering After Grouping with HAVING
Once you've grouped your data using GROUP BY , you might want to filter based on the result of an aggregation — that's where HAVING steps in.
Example:
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 10;
This query returns only departments with more than 10 employees. The count is calculated first, then filtered.
Unlike WHERE , HAVING can include aggregate functions. You can also reference column aliases defined in the SELECT .
Common uses:
- Filter based on group size (
COUNT,SUM, etc.) - Compare aggregated values (eg,
HAVING AVG(salary) > 60000) - Use with or without
GROUP BY
When to Use Which?
To decide between WHERE and HAVING , ask yourself:
- Do I need to filter individual rows before grouping? → Use
WHERE. - Do I need to filter groups or aggregated results after grouping? → Use
HAVING.
Sometimes both can be used together:
SELECT department, AVG(salary) AS avg_salary FROM employees WHERE status = 'active' GROUP BY department HAVING AVG(salary) > 60000;
In this case:
-
WHEREnarrows the dataset to active employees only. -
HAVINGfurther filters the resulting groups to show only those with an average salary over $60k.
So basically, it's about timing: WHERE filters early, HAVING filters late — especially useful after aggregations.
The above is the detailed content of What is the difference between WHERE and HAVING clauses in SQL?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
What is the difference between HQL and SQL in Hibernate framework?
Apr 17, 2024 pm 02:57 PM
HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).
Analysis of the impact of MySQL connection number on database performance
Mar 16, 2024 am 10:09 AM
Analysis of the Impact of MySQL Connection Number on Database Performance With the continuous development of Internet applications, databases have become an important data storage and management tool to support application systems. In the database system, the number of connections is an important concept, which is directly related to the performance and stability of the database system. This article will start from the perspective of MySQL database, explore the impact of the number of connections on database performance, and analyze it through specific code examples. 1. What is the number of connections? The number of connections refers to the number of client connections supported by the database system at the same time. It can also be managed
The Purpose of SQL: Interacting with MySQL Databases
Apr 18, 2025 am 12:12 AM
SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
How to use AWS Glue crawler with Amazon Athena
Apr 09, 2025 pm 03:09 PM
As a data professional, you need to process large amounts of data from various sources. This can pose challenges to data management and analysis. Fortunately, two AWS services can help: AWS Glue and Amazon Athena.
MySQL and SQL: Essential Skills for Developers
Apr 10, 2025 am 09:30 AM
MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.
Basic concepts and usage analysis of SQL in Go language
Mar 27, 2024 pm 05:30 PM
Basic concepts and usage of SQL in Go language SQL (StructuredQueryLanguage) is a language specially used to manage and operate relational databases. In Go language, we usually use SQL to perform database operations, such as querying data, inserting data, updating data, deleting data, etc. This article will introduce the basic concepts and usage of SQL in Go language, with specific code examples. 1. Connect to the database In Go language, we can use third-party libraries to connect data
MySQL: A Practical Application of SQL
May 08, 2025 am 12:12 AM
MySQL is popular because of its excellent performance and ease of use and maintenance. 1. Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2. Insert and query data: operate data through INSERTINTO and SELECT statements. 3. Optimize query: Use indexes and EXPLAIN statements to improve performance.
SQL vs. MySQL: Clarifying the Relationship Between the Two
Apr 24, 2025 am 12:02 AM
SQL is a standard language for managing relational databases, while MySQL is a database management system that uses SQL. SQL defines ways to interact with a database, including CRUD operations, while MySQL implements the SQL standard and provides additional features such as stored procedures and triggers.
