What's the logical order of SQL query operations?
SQL Query
Execution Order:
1. FROM
& JOIN: The DB looks at the tables we specified in FROM and in the JOINs,
and then merges this data - this merged data is our starting "working
set".
2. WHERE: Perhaps
we don't want the whole merged working set - perhaps we want to filter this
data, so this means we end up with a subset of our working set - we've retained
some rows and discarded other rows.
3. GROUP
BY: Time to make some buckets! When we're grouping data, we're creating buckets
(groups). Each bucket has a key and the associated rows. These buckets help us
perform aggregations.
4. HAVING:
Perhaps we don't want all our buckets. This is where we can specify some
conditions, so that we choose just some buckets, and discard others. (This is
similar to the WHERE clause - in WHERE, we are filtering rows, whereas in
HAVING we are filtering buckets).
5. SELECT:
We can select column names as well as aggregations. That's it, we now have the
resulting data set.
6. ORDER
BY: Perhaps we want to order the data set - sorting data in ascending or
descending order based on columns. Here, the data set remains the same, we're
just changing the ordering of the rows.
7. LIMIT
& OFFSET: Perhaps due to pagination, we want to limit the data returned, so
we're returning just a subset of the rows above.
References
& further reading:
https://www.eversql.com/sql-order-of-operations-sql-query-order-of-execution/
https://www.sisense.com/blog/sql-query-order-of-operations/
NOTES: The
above order is the LOGICAL order, which is different from the PHYSICAL
execution order. For example, the DB may perform WHERE before doing the FROM
& JOIN due to performance optimization, but logically the result is same as
performing WHERE after FROM & JOIN. The query optimizer is free to perform
operations in any way, using whatever algorithm, as long as the result conforms
to what we would have obtained via the logical order.
Comments
Post a Comment