ON, WHERE, HAVING, wherehaving

ON, WHERE, HAVING, wherehaving

ON, WHERE, and HAVING can filter data by limiting conditions, but their usage is different. Next we will analyze the differences among the three.

1. ON and WHERE

All queries generate an intermediate temporary report. The query result is obtained from the returned temporary report. The difference between ON and WHERE and the restriction conditions mainly depends ON the timing when the restriction conditions take effect. ON filters database records based ON the Restriction Conditions and then produces temporary tables; the WHERE clause filters the results from the temporary table based on the conditions after the temporary table is created.

For the above reasons, the main differences between ON and WHERE are as follows:

1) return result: In the left outer (right outer) join, ON returns all records in the left table (right table). In the WHERE clause, this is equivalent to inner join, only records that meet the conditions are returned (because they are filtered out from the temporary table and do not meet the conditions ).

2) Speed: Because the ON restriction condition occurs earlier, the dataset of the temporary table is smaller, so the ON performance is better than WHERE.

2. HAVING and WHERE

The difference between HAVING and WHERE is also related to the time when the restriction condition takes effect. HAVING filters out the results after the aggregation function computing results are obtained. The query results only return the group that meets the conditions, and HAVING cannot appear separately, it can only appear in the group by clause .; The WHERE clause filters the results before calculation. If the aggregate function uses the WHERE clause, the aggregate function only calculates data that meets the restrictions of the WHERE clause. For example:

     SELECT COUNT(id) FROM db_equip WHERE tb_equip_type = ‘2’;

The result of the Count calculation is to first filter the devices with the device type of 2, and then Count the number of devices with the device type of 2.

HAVING differs from WHERE in terms of usage and functionality:

1) HAVING cannot appear independently and can only appear in the group by clause. WHERE can be used together with other clauses such as SELECT or group by clause, WHERE has a higher priority than HAVING.

2) Because WHERE filters data before the clustering function and HAVING filters groups after calculation, the WHERE query speed is faster than HAVING.

3. to sum up, the main difference between ON, WHERE, and HAVING is caused by the timing when the condition in the clause takes effect. ON filters records based ON the condition before the temporary table is produced, WHERE is used to filter data from a temporary table, while HAVING is used to filter data that meets the conditions in the temporary table. After grouping, the HAVING limit statement is used to filter the data, the returned result is a group that meets the HAVING clause restrictions.
The where and having in the database are different.

Both having and where are used for filtering.
Having is a filtering group and where is a filtering record.

They have their own differences

1. having is used for grouping and filtering.

2. Use where in other cases
-----------------------------------------------------
Having must be used with group,
Having does not exist in group by (it is only used for filtering)
-------------------------------------------------------
Example
Table Structure
Department ID name salary
1 aa 2000
2 bb 1200
1 cc 2100
2 dd 1800
1 ee 2100
3 ff 8000
2 gg 2200
3 hh 4500

Query the Department numbers with the salaries of multiple employees not less than 2000
(That is to say, if there are more than 2000 employees in a department, two or more employees will be queried)

Select Department number, count (*) from employee information table
Where salary> = 2000
Group by department no.
Having count (*)> 1

Where filters each record
Having groups the same Department
Count (*)> 1: Calculate more than two departments

The query result is

1 3
3 2
In addition, the webmaster group has products to buy, Which is cheap and guaranteed.

What is the difference between where and having in SQL?

-- Supplement
Select... from ···
Where · (only attributes before grouping can be filtered)
Group ···
Having · (you can only filter the overall attributes of each group after grouping and use aggregate functions)
····
-- If group by is not used, the entire table is a group by default.