SQL Query Optimization Framework Pt.1
This article is the first in a multi-part series focusing on optimizing SQL queries for structure, performance, and readability.
Basic SQL Best Practices
There are many types of SQL data bases, each of which have ways to optimize performance and pitfalls to avoid. For this article series general best practices will be reviewed to ensure transferability between each language type.
There are many pitfalls to avoid when writing SQL queries that should be reviewed to ensure performant, accurate, and maintainable SQL throughout your organization. Each section below reviews the proper structure and formatting for SQL queries in any database that should be followed.
Proper SQL Query Structure
Proper SQL makes queries readable, maintainable, and easier to debug. While there’s no universal standard, best practices generally follow clear alignment and logical grouping.
SQL Statement Basic Clause Definitions:
While the above table shows the various clauses in the order that you would write them in a query, that is not the order in which the query would execute its logic. This order can be seen in the table below.
SQL Statement Basic Clause Execution Order:
SQL Statement Basic Clause Logical Order:
The previous section reviews the execution order and formatting of a SQL statement, this section covers the logical order that a statement should be written.
In SQL, the logical order of execution for clauses is different from the order in which they are written. The logical order is:
SELECT – Choose the columns to return.
FROM – Specify the table(s) to retrieve data from.
WHERE – Filter rows before aggregation.
GROUP BY – Group rows based on column values.
HAVING – Filter aggregated results.
ORDER BY – Sort the final result.
LIMIT – Restrict the number of rows returned.
SQL Statement Advanced Clauses:
Beyond a basic SELECT statement, SQL can perform more complex data transformations including the following.
WITH (Common Table Expressions, CTEs) – Defines temporary result sets that can be referenced later in the query.
JOIN – Combines data from multiple tables.
DISTINCT – Ensures unique results in the output.
CASE – Adds conditional logic within the SELECT clause.
WINDOW FUNCTIONS (OVER, PARTITION BY, ROW_NUMBER, etc.) – Performs calculations across a subset of rows.
UNION / UNION ALL – Combines multiple queries into a single result set.
These clauses can be placed in various sections of a statement, an example of a query that leverages these more advanced features is below.
SQL Formatting Best Practices
Anyone can learn the basic syntax and logical steps it takes to create SQL queries, what separates a good SQL programmer from a great one is the ability to maintain properly structured coding standards. This seems simple but oftentimes even industry veterans will default to the ways they feel comfortable writing statements and not to a larger standard set by their company.
At Lakefront we put all of our repository code through the same Linting program to ensure we have standards and practices in our codebase across clients. Check out some of the main rules we enforce when checking our engineers code.
Write your keywords in UPPERCASE including SELECT, FROM, JOIN, WHERE, etc.
Each clause starts on a new line - this means putting SELECT, FROM, WHERE, GROUP BY, etc. each on their own line in your statement.
Use proper indentation for readability:
Indent column selections after SELECT.
Indent JOIN conditions.
Indent after WHERE, HAVING, and ON for clarity.
Consistent column listing - Align column names in SELECT and GROUP BY sections.
Avoid deep nesting by using Common Table Expressions (CTEs) when needed.
Example of a Properly Formatted Statement:
Next up, we will dive into Performance Optimization of queries including Indexing, Joins, and Sub Queries vs CTEs.
