4 Replies Latest reply: Jun 3, 2012 2:15 AM by 193210 RSS

    execution order


      If , in a sql query , i use the GROUP BY clause and just before the WHERE clause, can you explain to me in which order the execution occurs:

      Does the execution , first, execute the query considering the GROUP BY clause , and after it makes a selection on the results, using what is precised in the WHERE clause, or does the execution , first, considers the WHERE clause and makes, after , the GROUP BY on the records resulting of the WHERE clause.

      Thanks a lot for your answer.

      Secondly, can you recommand me a book or a documentation on the sql querys execution and performance( if it exsits in french i would prefer).

      Thanks a lot for your answer too.

      Best regards.

        • 1. Re: execution order
          you cannot have where after group by..

          If you want to fileter rows after group by you have to use Having.

          General syntantical order of sql query

          Where clause -> Group by -> Having -> order by
          • 2. Re: execution order
            Vivek L
            Have a look at this article from tom kyte :
            Think of it as being processed in this order:
            1. The FROM/WHERE clause goes first.
            2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
            3. SELECT is applied.
            4. GROUP BY is applied.
            5. HAVING is applied.
            6. ORDER BY is applied.
            • 3. Re: execution order

              Normally SQL queries will work in some order..
              For example if you want to find total amount of salary and it should be grouped by empname for some XYZ table,

              Select sum(salary),ename
              from emp
              where ename='aaa'
              group by ename;

              In this case sql query works

              Execution No. 1 - From

              Execution No. 2 - Where

              Execution No. 3 - Group by

              Execution No. 4 - Select

              For reference http://www.bennadel.com/blog/70-SQL-Query-Order-of-Operations.htm
              • 4. Re: execution order
                Thanks a lot for your answers.
                I consider the problem is resolved.

                Best ragrds.

                Nathalie Harbonnr