Oct '23 (edited) • General discussion
Order of SQL Query Execution
The sql query below is based on the Advertureworks2019 database we loaded into our database during SQL class.
-- Retrieve aggregated information about transaction types and associated work orders
SELECT
PT.TransactionType,
AVG(PW.StockedQty) AS AVGQTY,
COUNT(PT.Quantity) AS TOTALQTY,
SUM(PT.ActualCost) AS TOTALCOST
FROM
[Production].[TransactionHistory] AS PT
INNER JOIN
[Production].[WorkOrder] AS PW ON PT.ProductID = PW.ProductID
WHERE
PT.Quantity > 13 -- Filters rows where quantity is greater than 13 (handles both integer and non-integer values)
GROUP BY
PT.TransactionType -- Groups results by transaction type (non-aggregated function)
HAVING
SUM(PT.ActualCost) >= 0.00 -- Filters groups where the total actual cost is greater than or equal to 0.00
ORDER BY
TOTALCOST ASC -- Orders the result set by total cost in ascending order
Each query begins with finding the data that we need in a database, and then filtering that data down into something that can be processed and understood as quickly as possible. Because each part of the query is executed sequentially, it's important to understand the order of execution so that you know what results are accessible where.
Query order of execution
1. FROM and JOINs
The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.
2. WHERE
Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.
3. GROUP BY
The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.
4. HAVING
If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.
5. SELECT
Any expressions in the SELECT part of the query are finally computed.
6. DISTINCT
Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.
7. ORDER BY
If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.
8. LIMIT / OFFSET
Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.
DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE
A HAVING clause is like a WHERE clause, but applies only to groups as a whole (that is, to the rows in the result set representing groups), whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. In that case:
The WHERE clause is applied first to the individual rows in the tables or table-valued objects in the Diagram pane. Only the rows that meet the conditions in the WHERE clause are grouped.
The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.
Note: Link to learn more about writing sql https://www.w3schools.com/sql/
5
2 comments
Order of SQL Query Execution
