Чем отличается HAVING от WHERE?
Почему данную выборку нельзя переписать с использованием WHERE?
- Вопрос задан более трёх лет назад
- 18109 просмотров
- Вконтакте
- Вконтакте
Чтобы понять, что у студента Mark=5 — минимальный, нужно сгруппировать значения, вдруг не минимальный (помимо проверки — есть он или нет).
То есть чтобы полностью ваше условие проверить, нужно точно группировать, WHERE просто проверит — есть такое значение или нет и выведет студентов, у которых есть
WHERE же используется движком на момент выборки, физически не возможно узнать минимальное значение
HAVING же уже работает с агрегирующими функциями, то есть мы сгруппировали, посчитали нужной функцией (минимальное значение в вашем случае), а HAVING уже выдал с условием этого подсчета
- Вконтакте
Можно и переписать. но в виде извращенного каскада вложенных селектов:
— внутренний будет вываливать сгруппированные StudentId и MIN(Mark)
— внешний будет селектить StudentId с условие where
HAVING vs. WHERE in SQL
Unable to SELECT with an aggregate function in SQL? You’ve come to the right place!
What You’ll Learn
- The difference between WHERE and HAVING in SQL
- How to use WHERE in SQL
- How to use HAVING in SQL
- How to use WHERE + HAVING in SQL
Prerequisite Knowledge
Before hopping into the difference between HAVING and WHERE , you should be familiar with
- Terminal commands
- SQL Tables
- The SELECT clause
- Aggregate functions in SQL ( SUM , MAX , MIN , etc.)
Level 0: Getting Started
In this post, we will investigate WHERE and HAVING in several contexts. While not essential, it might be useful to recreate the tables and data used as examples. To get started, navigate to an open directory in your terminal and paste the following code:
PRESS ENTER
PRES ENTER
PRESS ENTER
PRESS ENTER
PRESS ENTER
If you pasted it correctly, you should see a table with some information about students and their GPAs. If not, try again or brush up on your use of the terminal.
Level 1: SELECT with Simple Condition
Say you want to SELECT students with a GPA above the cutoff required for a scholarship. A quick Google search will reveal the WHERE clause that tells SQL to only select entries in a database where some other condition is met. Let’s look at an example that assumes some table full of students:
Great, we only see Jane and Steve! These commands are easy and intuitive to read. Why don’t we make things more complicated?
Level 2: Aggregate Methods && HAVING
Let’s change the scenario. Now, we have a table called student_credits that contains information on how many credits each student took in a given semester.
In this situation, let’s assume that our scholarship only requires that a student has taken more than 30 total credits. So, we want to SELECT a student’s name WHERE the sum of all of their credit hours is greater than 20. Let’s try the approach above and directly translate this command into SQL.
NOTE: The following code will not work
Oh no! This code outputs an error about misusing the aggregate method SUM . Looks like we are going to have to try something else: the HAVING command.
Let’s try the following code:
Wow! Only Steve was returned, exactly as we had hoped! But what’s going on here? What’s HAVING doing that WERE isn't? Let’s take a closer look…
Level 3: Understanding HAVING && WHERE
First, we can see that HAVING allows aggregate methods, like SUM , to be used as SELECT criteria. Why can’t WHERE use aggregate methods?
In short, WHERE can only see one row at a time, so it has to filter line by line. An aggregate method like SUM is looking at many rows at once, and so WHERE does not know what to do with SUM and other aggregate methods.
On the other hand, HAVING was designed to work in conjunction with the GROUP BY clause. So, in order to understand HAVING , we have to understand GROUP BY first.
Level 3.1: Understanding GROUP BY
In SQL, GROUP BY is a powerful clause that allows us to ‘group’ rows together based on a particular value. Let’s go back to our student_credits example:
But remember, there were three entries for Steve, so GROUP BY has consolidated those three rows down to one row. Cool, huh?
Level 3.2: GROUP BY + HAVING
When we then add our HAVING clause to GROUP BY , we are essentially adding a WHERE statement that looks at the rows based on the value we grouped by. This allows HAVING to look at many rows at once, allowing it to working with aggregate methods! Remember, because HAVING requires GROUP BY , we always need to use a GROUP BY clause before we can add HAVING . Take a look back at our code, can you explain exactly what’s going on?
Level 4: Using WHERE + HAVING Together
Let’s try one more table. Copy the following code into your terminal:
PRESS ENTER
PRESS ENTER
Now, let’s find students that have above a 3.5 GPA and have taken a total of 30 classes. From the data, it looks like Steve should be the only name our search returns. Since the GPA of every student is stored in every row, we can use WHERE to filter for students above a 3.5. However, since credits are different every semester, we need to use the aggregate method SUM to find total credits; hence, we must use a GROUP BY and HAVING clauses to get the desired result. Since GROUP BY has to precede HAVING, our final search is
And boom! We only return Steve. If you followed allow in this post, this combination should have been intuitive. SQL did the following:
Step 1: WHERE removes GPAs below 3.5
Step 2: GROUP BY consolidates row information
Step 3: Apply HAVING SUM to Credits
And our return value is Steve!
Feel free to mess around with the code provided to flesh out your understanding. Happy coding!
What is the difference between HAVING and WHERE in SQL?
What is the difference between HAVING and WHERE in an SQL SELECT statement?
EDIT: I have marked Steven’s answer as the correct one as it contained the key bit of information on the link:
When GROUP BY is not used, HAVING behaves like a WHERE clause
The situation I had seen the WHERE in did not have GROUP BY and is where my confusion started. Of course, until you know this you can’t specify it in the question.
18 Answers 18
HAVING: is used to check conditions after the aggregation takes place.
WHERE: is used to check conditions before the aggregation takes place.
Gives you a table of all cities in MA and the number of addresses in each city.
Gives you a table of cities in MA with more than 5 addresses and the number of addresses in each city.
HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
Number one difference for me: if HAVING was removed from the SQL language then life would go on more or less as before. Certainly, a minority queries would need to be rewritten using a derived table, CTE, etc but they would arguably be easier to understand and maintain as a result. Maybe vendors’ optimizer code would need to be rewritten to account for this, again an opportunity for improvement within the industry.
Now consider for a moment removing WHERE from the language. This time the majority of queries in existence would need to be rewritten without an obvious alternative construct. Coders would have to get creative e.g. inner join to a table known to contain exactly one row (e.g. DUAL in Oracle) using the ON clause to simulate the prior WHERE clause. Such constructions would be contrived; it would be obvious there was something was missing from the language and the situation would be worse as a result.
TL;DR we could lose HAVING tomorrow and things would be no worse, possibly better, but the same cannot be said of WHERE .
From the answers here, it seems that many folk don’t realize that a HAVING clause may be used without a GROUP BY clause. In this case, the HAVING clause is applied to the entire table expression and requires that only constants appear in the SELECT clause. Typically the HAVING clause will involve aggregates.
This is more useful than it sounds. For example, consider this query to test whether the name column is unique for all values in T :
There are only two possible results: if the HAVING clause is true then the result with be a single row containing the value 1 , otherwise the result will be the empty set.
Чем отличается WHERE от HAVING?
Обсудим такой популярный вопрос на собеседовании, как «Чем отличается WHERE от HAVING»?
Ой, скажет кто-то, зачем же спрашивать такие банальности? Как ни странно, довольно много разработчиков даже с сертификатами по SQL quering не понимают, что происходит в HAVING.
Давайте разберёмся. В сущности, HAVING очень похож на WHERE — это тоже фильтр. Вы можете написать в HAVING name = ‘Anna’, как и в WHERE, и ошибки не будет.
В чём же ключевое различие?
Во-первых, в HAVING и только в нём можно писать условия по агрегатным функциям (SUM, COUNT, MAX, MIN и т. д.). То есть если вы хотите сделать что-то вроде COUNT() > 10, то это возможно сделать только в HAVING*.
«Почему бы не оставить только HAVING?» — спросите вы. Всё кроется в том, как SQL Server выполняет запрос, в каком порядке происходит его разбор и работа с данными. WHERE выполняется до формирования групп GROUP BY. Это нужно для того, чтобы можно было оперировать как можно меньшим количеством данных и сэкономить ресурсы сервера и время пользователя.
Следующим этапом формируются группы, которые указаны в GROUP BY. После того как сформированы группы, можно накладывать условия на результаты агрегатных функций. И тут как раз наступает очередь HAVING: выполняются условия, которые вы задали.
Главное отличие HAVING от WHERE в том, что в HAVING можно наложить условия на результаты группировки, потому что порядок исполнения запроса устроен таким образом, что на этапе, когда выполняется WHERE, ещё нет групп, а HAVING выполняется уже после формирования групп.