I am just confused with the execution sequence of a SQL query when we use GROUP BY
and HAVING
with a WHERE
clause. Which one gets executed first? What is the sequence?
This question is related to
sql
sql-server
sql-server-2005
In Oracle 12c, you can run code both in either sequence below:
Where
Group By
Having
Or
Where
Having
Group by
I think it is implemented in the engine as Matthias said: WHERE, GROUP BY, HAVING
Was trying to find a reference online that lists the entire sequence (i.e. "SELECT" comes right down at the bottom), but I can't find it. It was detailed in a "Inside Microsoft SQL Server 2005" book I read not that long ago, by Solid Quality Learning
Edit: Found a link: http://blogs.x2line.com/al/archive/2007/06/30/3187.aspx
Here is the complete sequence for sql server :
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
So from the above list, you can easily understand the execution sequence of GROUP BY, HAVING and WHERE
which is :
1. WHERE
2. GROUP BY
3. HAVING
In below Order
Having Clause may come prior/before the group by clause.
Example: select * FROM test_std; ROLL_NO SNAME DOB TEACH
1 John 27-AUG-18 Wills
2 Knit 27-AUG-18 Prestion
3 Perl 27-AUG-18 Wills
4 Ohrm 27-AUG-18 Woods
5 Smith 27-AUG-18 Charmy
6 Jony 27-AUG-18 Wills
Warner 20-NOV-18 Wills
Marsh 12-NOV-18 Langer
FINCH 18-OCT-18 Langer
9 rows selected.
select teach, count() count from test_std having count() > 1 group by TEACH ;
TEACH COUNT
Langer 2 Wills 4
SELECT
FROM
JOINs
WHERE
GROUP By
HAVING
ORDER BY
WHERE is first, then you GROUP the result of the query, and last but not least HAVING-clause is taken to filter the grouped result. This is the "logical" order, I don't know how this is technically implemented in the engine.
This is the SQL Order of execution of a Query,
You can check order of execution with examples from this article.
For you question below lines might be helpful and directly got from this article.
- GROUP BY --> The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.
- HAVING --> If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.
References:-
Think about what you need to do if you wish to implement:
The order is WHERE, GROUP BY and HAVING.
Source: Stackoverflow.com