Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the difference between where and having in SQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what's the difference between where and having in SQL?"

Concept

Where

Where is a constraint declaration that constrains the query conditions in the database before the results of the query are returned, and works before the results are returned, and "aggregate functions" cannot be used after where.

Aggregate function

Performs a calculation on a set of values and returns a single value, also known as a group function, often used with the HAVING of the GROUP BY clause of a SELECT statement. For example

AVG returns the average value in the specified group COUNT returns the number of items in the specified group MAX returns the maximum value of the specified data. MIN returns the minimum value of the specified data. SUM returns the sum of the specified data and can only be used for numeric columns. Null values are ignored.

Having

Having is a filtering declaration that is filtered after the result of the query database is returned, that is, it works after the result returns value, and is used in conjunction with the aggregate function.

Difference

If you use group by for filtering, you can only use having.

The order of execution is where > aggregate function (sum,min,max,avg,count) > having, so where does not use aggregate function.

For instance

Staff table

1. What are the scores greater than 8?

Use where

Use having

An error is reported, because having filters the filtered results, but there is no employee_score value, so the error is reported. If you add it, you can find it.

Therefore, most of the time, where and having cannot be replaced at will, mainly because the filtering objects are different.

2. What is the highest and lowest score of each department older than 20?

You can also use where.

But you need to pay attention to the execution order.

Matters needing attention

When writing sql statements, pay attention to the execution order of where and group by. Where should be executed first and group by later, otherwise an error will be reported.

To sum up, I believe you have a deeper understanding of "what's the difference between where and having in SQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.