Order of a SQL Query

Last modified: December 09, 2019

The way to make a query run faster is to reduce the number of calculations that the software must perform. To do this, you’ll need some understanding of how SQL executes a query.

Let’s take a look at a sample SQL query :

SELECT DISTINCT column, AGGREGATE(column)
FROM table1
JOIN table2
ON table1.column = table2.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count;

Each part of the query is executed sequentially, so it’s important to understand the order of execution :

FROM and JOIN: The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried
WHERE: Once we have the total working set of data, the WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.
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.
HAVING: If the query has a GROUP BY clause, then the constraints in the HAVING clause are applied to the grouped rows, and the grouped rows that don’t satisfy the constraint are discarded.
SELECT: Any expressions in the SELECT part of the query are finally computed.
DISTINCT: Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.
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.
LIMIT: Finally, the rows that fall outside the range specified by the LIMIT are discarded, leaving the final set of rows to be returned from the query.

Now that we understand the basic structure and order of a SQL query, we can take a look at the tips to optimize them for faster processing in the next chapter.

Resources

Written by: Rohan Joseph
Reviewed by: Matt David

Resources

Created by