CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Aarti
Aarti

Reputation:

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

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?

Upvotes: 139

Views: 257164

Answers (12)

yax prajapati
yax prajapati

Reputation: 1

First the FROM will be executed, as it has to know which table to use. Then on that table the where clause is applied. It filters out the data we want based on the provided condition. Then if there is any GROUP BY clause it gets executed. which helps in grouping the filtered data. Then HAVING clause is applied which again filters out data from the groups. Then ORDER BY which orders the final and filtered data. and in the last LIMIT is executed, limiting the number of rows.

Upvotes: 0

trwavx
trwavx

Reputation: 1

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

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

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

Upvotes: 47

Kushan Gunasekera
Kushan Gunasekera

Reputation: 8576

This is the SQL Order of execution of a Query,

enter image description here

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.

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

Upvotes: 12

manoj remala
manoj remala

Reputation: 35

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

Upvotes: 0

Jim Chen
Jim Chen

Reputation: 21

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

Where
Group By
Having

Or

Where 
Having
Group by

Upvotes: 2

GimmicksWorld
GimmicksWorld

Reputation: 51

In below Order

  1. FROM & JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Upvotes: 2

KM.
KM.

Reputation: 103637

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

Upvotes: 256

Matthias Meid
Matthias Meid

Reputation: 12523

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.

Upvotes: 12

OcTella
OcTella

Reputation: 21

SELECT
FROM
JOINs
WHERE
GROUP By
HAVING
ORDER BY

Upvotes: -2

FerranB
FerranB

Reputation: 36817

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.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147314

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

Upvotes: 3

Related Questions