#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171

    Which part of the query gets executed first?


    In the query below would it be correct to say this is the order of execution? Please note this may not be the case if the sub query is not corrrelated.


    1 - FROM entries in the main query
    2 - SELECT category, title, created
    3 - WHERE (gets tricky):
    4 - SELECT MAX(created) in the sub query executes "more than once if neccessary"! Matching each and every single result of the main query with the subquery. For example every row returned from main query has to be checked against the subquery. In other words the subquery runs as many times as the results of the main query. If there are 10 rows returned from the main query, the sub query has to evaluate each row and check if it is the MAX(created) or not. This may not be the case if the sub query is not corrrelated..

    Code:
    SELECT category,
           title,
           created
    FROM   entries AS t
    WHERE  created = (SELECT Max(created)
                      FROM   entries
                      WHERE  category = t.category)
    Last edited by zxcvbnm; April 2nd, 2013 at 06:17 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    using your numbers, the order of execution is 1, 3, 4, 2

    SELECT is the last clause executed before ORDER BY

    you are correct that a correleated subquery is executed once for each row in the main query

    just for the sake of interest, what does an EXPLAIN on your query produce?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,670
    Rep Power
    171
    Originally Posted by r937
    using your numbers, the order of execution is 1, 3, 4, 2

    SELECT is the last clause executed before ORDER BY

    you are correct that a correleated subquery is executed once for each row in the main query

    just for the sake of interest, what does an EXPLAIN on your query produce?
    1 - How is 1,3,4,2 possible if the correleated subquery is executed once for each row retrieved by the main query? Main query results shoud be retrieved before the subquery is executed for each returned row, no? Otherwise sunquery won't find any matches because thre are no results yet from the main query.



    2 - Here is the EXPLAIN. I sometimes craving a section in your book speaking about EXPLAIN in details.
    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY t ALL NULL NULL NULL NULL 6 Using where
    2 DEPENDENT SUBQUERY entries ALL NULL NULL NULL NULL 6 Using where

    3 - I am pretty sure you are gonna give negative feedback on this but what about this:
    Code:
    SELECT category,
           title,
           created
    FROM   entries
    WHERE  id IN (SELECT id
                  FROM   entries
                  GROUP  BY category
                  ORDER  BY created DESC)
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by zxcvbnm
    ...but what about this:
    what about it?

    by the way, the ORDER BY in the subquery has no purpose and is ignored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo