Clever Geek Handbook

SQL query, execution sequence

What will be the sequence of execution followed by SQL if the query has both bulk and order by. It depends on their position in the request ???

+2


source to share


8 answers


ORDER BY

is always performed based on the results of the grouping performed GROUP BY

, i.e. always "after". In standard SQL, you must have ORDER BY

lexically after GROUP BY

, if both are present, to "remind" this fact.



+5


source


in order:



FROM and JOIN define and filter rows
WHERE more filters on rows
GROUP BY concatenates those rows into groups
REVERSE group filters
ORDER BY orders the remaining rows / groups

+4


source


It depends on many things, including the RDMS you are using. The best way to see what's going on is to use a query tool that lets you see the execution plan for the query.

+1


source


Ordering usually happens last.

If you are using SQL Server, run the query parser and the execution plan will give you a nice graphical view of your query.

+1


source


The execution sequence is not required by any SQL statement. The result is said to correspond to the result to be obtained by the "canonical" evaluation. In canonical evaluation, ORDER BY is applied last (even after evaluating SELECT statements), but that does not mean that sorting is carried over to that point when the query is actually executed on the real system.

+1


source


the group is executed first and then the group results are ordered.

0


source


Let's assume we have a SQL query:

SELECT   ...
  FROM     ...
  WHERE    ...
  GROUP BY ...
  HAVING   ...
  ORDER BY ...

order of execution of subqueries of SQL query:

 1. FROM clause
 2. WHERE clause
 3. GROUP BY clause
 4. HAVING clause
 5. SELECT clause
 6. ORDER BY clause
0


source


I also suggest using a query parser for a specific database engine. Below is an example in Postgres explaining that ORDER BY is executed first and a WHERE filter after that:

EXPLAIN
SELECT * FROM table WHERE id=x AND date<='yyyy-mm-dd' ORDER BY date DESC;

So, if I change DESC to ASC, the result set contains different records!

0


source






More articles:


All Articles