Summary of the difference between having and where in Mysql

1. Introduction

where

Filter query data

having

Used to filter grouped data [having and group by must be used together (group by having must appear when having)]

2. Usage

where

select * from table where sum(Fields)&gt;100

having

select * from table group by Fields having Fields&gt;10

Three. Difference

1. The data to be executed has different sources

Where is the judgment when data is read from disk into memory, [Filtering before data grouping]

Having is to make a judgment after the disk is read into memory. 【Filter the data after the grouping】

Therefore: using where is much more efficient than having.

2. The execution order is different

Where>Group By>Having

MySQL executes the order of execution when interpreting the SQL language:

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition><strong>
WHERE</strong> <where_condition>
GROUP BY <group_by_list><strong>
HAVING</strong> <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

3. Where can't use alias for fields, but having can

select name as aa from student where aa &gt; 100 (mistake)
select name as aa from student group name having  aa &gt; 100 (correct)

4. Having can use aggregate functions as conditions, but where cannot be used, where can only use existing columns as conditions

select  *  as aa from student where count(*) &gt; 1 (mistake)
select *  from student group name having  count(name) &gt; 1 (correct)

Note: If you can use where

5. When querying multiple tables, filter where first and then join, have first and then filter

Find out all employees in the 'IT' department with a salary of more than 10,000: (Filtered before joining)

SELECT e.employee_name, d.department_name  
FROM employees e  
JOIN departments d ON e.department_id = d.department_id  
WHERE  > 10000 AND d.department_name = 'IT';

Find out the total amount of customers who place orders in each customer exceeds 1,000 and their total amount of orders: (First, the table is connected, filtered based on the aggregation results after grouping)

SELECT c.customer_name, SUM(o.order_amount) AS total_amount  
FROM customers c  
JOIN orders o ON c.customer_id = o.customer_id  
GROUP BY c.customer_name  
HAVING SUM(o.order_amount) > 1000;

Summarize

The where clause is applied before the data is joined and grouped and is used to filter rows
Having clauses are applied after data is joined, grouped, and aggregated and are used to filter packets

This is the end of this article about the difference between having and where in Mysql. For more information about the difference between having and where in Mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!