Execution sequence of Group By, Having and Where clause in SQL Server?

Question

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?

KM. · Accepted Answer · 2017-03-02T14:39:25.337

246

in order:

FROM & JOINs determine & filter rows
WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups
LIMIT filters on the remaining rows/groups

edited Mar 02 '17 at 14:39

answered Jul 15 '09 at 12:24

KM.

101,727
34
178
212

is there any reference to this?? – Geshan Oct 19 '10 at 17:49
4

@Geshan, look into `SET SHOWPLAN_ALL ON` – KM. Oct 19 '10 at 17:54
Hi, I have one question. will the case statement when condition override the where condition ? – MAX Jan 07 '17 at 07:49
3

What about the select Clause. Is it in the Last? – MAX Jan 07 '17 at 10:24
Short and concise answer! Thank you! – Abhishek Ghosh Aug 29 '17 at 01:31
@KM. Can you please clarify at which point in the list the select clause is executed? I think it’s immediately after “having”. (Someone correct me if I’m wrong.) – littleO Jan 18 '22 at 00:57

Md. Suman Kabir · Answer 2 · 2022-08-11T07:31:02.977

38

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

Get more information about it from Microsoft

More explanation

edited Aug 11 '22 at 07:31

answered Nov 06 '17 at 17:43

Md. Suman Kabir

5,243
5
25
43

1

`SELECT * FROM table1 INNER JOIN table2 ON col = col2 WHERE table1.col = @val` Here ON comes after join, and select comes first, any explanations? – noob May 26 '20 at 05:32
1

This is just the syntax of writing a SQL statement. The sequence i mentioned is the real order a SQL engine evaluates. That means if your query syntax is correct, then the engine evaluates `FROM` first, then `ON` and so on. – Md. Suman Kabir May 26 '20 at 05:49
1

@ShailajaGuptaKapoor You can read this http://blogs.x2line.com/al/archive/2007/06/30/3187.aspx – Md. Suman Kabir May 27 '20 at 15:26
If the WHERE comes before the SELECT, why does this return a row? select sum(1) from foo where 1 = 0 – Tony B Oct 09 '20 at 15:08
What about "INTO"? – skan May 09 '23 at 10:46

Matthias Meid · Answer 3 · 2016-11-26T18:27:58.467

11

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.

edited Nov 26 '16 at 18:27

answered Jul 15 '09 at 08:14

Matthias Meid

12,455
7
45
79

2

Worth adding that the optimizer may move clauses from HAVING to WHERE if they don't depend on an aggregate. This will not affect the results shown. – Damien_The_Unbeliever Jul 15 '09 at 08:52

score 10 · Answer 4 · answered Jul 19 '20 at 05:07

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:-

score 3 · Answer 5 · answered Jul 15 '09 at 08:25

3

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

answered Jul 15 '09 at 08:25

AdaTheDev

142,592
28
206
200

Very nice explanation you linked to :-). – sleske Jul 15 '09 at 08:41
1

Link is dead. If you can fix that, it would be great :) – Akash KC Jul 23 '17 at 19:44

GimmicksWorld · Answer 6 · 2017-05-24T03:44:12.620

2

In below Order

FROM & JOIN
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

edited May 24 '17 at 03:44

answered May 24 '17 at 02:51

GimmicksWorld

51
3

score 2 · Answer 7 · edited Nov 06 '17 at 18:08

2

In Oracle 12c, you can run code both in either sequence below:

Where
Group By
Having

Or

Where 
Having
Group by

edited Nov 06 '17 at 18:08

Matei Radu

2,038
3
28
45

answered Nov 06 '17 at 17:15

Jim Chen

21
1

score 1 · Answer 8 · answered Jul 15 '09 at 08:47

1

Think about what you need to do if you wish to implement:

WHERE: Its need to execute the JOIN operations.
GROUP BY: You specify Group by to "group" the results on the join, then it has to after the JOIN operation, after the WHERE usage.
HAVING: HAVING is for filtering as GROUP BY expressions says. Then, it is executed after the GROUP BY.

The order is WHERE, GROUP BY and HAVING.

answered Jul 15 '09 at 08:47

FerranB

35,683
18
66
85

What you explained here is brilliant. It cleared my doubt regarding HAVING. So to summarise - GROUP BY & HAVING works same as SELECT & WHERE. HAVING clause always runs on COMPLETE table data taking the GROUP BY condition in picture and NOT ON GROUPED data. – Naveen Kumar Feb 22 '17 at 08:04

score 0 · Answer 9 · answered Dec 07 '18 at 16:47

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

trwavx · Answer 10 · 2022-09-08T02:01:44.937

0

SELECT
FROM
JOIN (INNER, LEFT, RIGHT, FULL OUTER JOIN)
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT;

edited Sep 08 '22 at 02:01

answered Sep 08 '22 at 01:59

trwavx

1
3

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 13 '22 at 05:59
That does not look like working syntax and it otherwise is surely not an explained answer to the sequence question, for that it looks too much like an attempt at actual syntax... Please have a look [answer]. – Yunnosch Sep 30 '22 at 20:45

score -2 · Answer 11 · edited Oct 06 '16 at 10:22

-2

SELECT
FROM
JOINs
WHERE
GROUP By
HAVING
ORDER BY

edited Oct 06 '16 at 10:22

Endri

714
13
34

answered Dec 22 '10 at 02:54

OcTella

21
1

Execution sequence of Group By, Having and Where clause in SQL Server?

11 Answers11

Linked

Related