Hi all, I am just confused with the execution sequence of sql query when we use GROUP BY..HAVING with WHERE clause. Which one get executed first?? Please help me.
views:
974answers:
4
+5
A:
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.
Not sure whether I got your question properly. Is this what you wanted to know?
Matthias
Mudu
2009-07-15 08:14:37
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
2009-07-15 08:52:25
+2
A:
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
AdaTheDev
2009-07-15 08:25:50
A:
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.
FerranB
2009-07-15 08:47:40
+1
A:
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
KM
2009-07-15 12:24:20