Home Forums Main Forums SAS Forum SQL Query: Order of Operations

  • SQL Query: Order of Operations

     Justin updated 7 months, 1 week ago 1 Member · 1 Post
  • Justin

    Administrator
    February 6, 2021 at 11:06 pm

    When we query data in a database, it is very important to optimize your SQL queries for processing efficiency. If you fail to do it, your queries can slow down or even crash the server.

    One important thing in developing SQL queries is to know the order in which SQL directives get executed as this will change the way I can optimize:

    Everyone knows that SQL queries usually appear as:

    create table    AAA as 
    select
    from
    where
    group by
    having
    order by

    Please note that, these clauses are not executed in their apparent order as shown above. Actually, they are executed in the below sequence:

    FROM clause
    WHERE clause
    GROUP BY clause
    HAVING clause
    SELECT clause
    ORDER BY clause

    This order holds some very interesting pros/cons:

    FROM Clause

    Since this clause executes first, it is our first opportunity to narrow downpossible record set sizes. This is why I put as many of my ON rules (for joins) as possible in this area as opposed to in the WHERE clause:

     FROM contact c   INNER JOIN display_status d 
    ON c.display_status_id = d.id AND
    d.is_active = 1 AND d.is_viewable = 1

    This way, by the time we get to the WHERE clause, we will have already excluded rows where is_active and is_viewable do not equal 1.

    WHERE Clause

    With the WHERE clause coming second, it becomes obvious why so many people get confused as to why their SELECT columns are not referencable in the WHERE clause. If you create a column in the SELECT directive:

    SELECT  ( 'foo' )   AS  bar 

    It will not be available in the WHERE clause because the SELECT clause has not even been executed at the time the WHERE clause is being run.

    ORDER BY Clause
    It might confuse people that their calculated SELECT columns (see above) are not available in the WHERE clause, but they ARE available in the ORDER BY clause, but this makes perfect sense. Because the SELECT clause executed right before hand, everything from the SELECT should be available at the time of ORDER BY execution.

    I am sure there are other implications based on the SQL clause order of operations, but these are the most obvious to me and can help people really figure out where to tweak their code.

    A good understanding of the above order of operations of SQL clauses will be very helpful for the sophisticated use of SQL programming.

Log in to reply.

Original Post
0 of 0 posts June 2018
Now