SQL statements are executed by the database system in several steps, including:
- Parsing the SQL statement and checking its validity
- Transforming the SQL into an internal representation, such as relational algebra
- Optimizing the internal representation and creating an execution plan that utilizes index information
- Executing the plan and returning the results
Visualizing SQL Queries
A mental model can help visualize how SQL queries are executed. Conceptually, SQL statements execute in this sequence:
- FROM: Tables are identified and joined to create the initial dataset.
- WHERE: Filters are applied to the initial dataset based on specified criteria.
- GROUP BY: The filtered rows are grouped according to the specified columns.
- HAVING: Additional filters are applied to the grouped rows based on aggregate criteria.
- SELECT: Specific columns are chosen from the resultant dataset for the output.
- ORDER BY: The output rows are sorted by the specified columns in ascending or descending order.
- LIMIT: The number of rows in the output is restricted.