SQL (Core) - Order of execution of a Query
Course Syllabus
Now that we have an idea of all the parts of a query, we can now talk about how they all fit together in the context of a complete query.
Complete SELECT Query Syntax
SELECT DISTINCT column, AGG_FUNC(column_or_expression) FROM mytable JOIN another_table ON mytable.column = another_table.column WHERE constraint_expression GROUP BY column HAVING constraint_expression ORDER BY column ASC/DESC LIMIT count OFFSET count;
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
The FROM clause and subsequent JOINs are first executed to determine the total working set of data being queried.
First-pass WHERE constraints are applied to individual rows, and rows that do not satisfy constraints are discarded.
Remaining rows are grouped based on common values in specified columns, condensing rows for aggregate calculations.
Constraints in HAVING are applied to the grouped rows, discarding grouped rows that fail the filter.
Any expressions or calculations in the SELECT projection list are computed.
Duplicate rows in the computed columns marked with DISTINCT are removed.
Rows are sorted alphanumerically based on specified columns in ASC or DESC order.
Rows outside the range specified by LIMIT and OFFSET are discarded, yielding the final output.
🗄️ Schema Explorer SELECTABLE
🎯 Exercises Checklist:
💡 Hint:
Loading hint...
🔑 Solution SQL:
SELECT * FROM movies;Data Output
No results. Run query to fetch data.