9 Best Practices for Writing SQL Queries

If you are reading this article, most probably, you are already familiar with SQL. You know how to write basic SQL queries. There are many ways to execute a SQL query to get desired results on your database.

However, not all SQL queries are created equal. Most can be optimized to follow the SQL query best practices. This article focuses on 9 SQL query optimization tips. After reading, you will know all the dos and don’ts of writing SQL queries.

1. Avoid the Use of SELECT Asterisk (SELECT *)

This is one of the most important SQL best practices. The SELECT * query returns records from all columns of the table. While helpful in some cases, this often query produces numerous complications:

• You might not need to retrieve all the columns. However, SELECT * returns them all, consuming excessive bandwidth for executing a query over the network.
• Column names in a table can be changed or removed, and new columns can be added. Thus, you might receive unexpected results for the SELECT * query. Specifying column names is a better idea.
• SELECT * is slower than SELECT Column Names because the latter can use column indexes to return data. 
• The order of columns returned by SELECT * is not in your control. However, you define the desired order when you specify the column names.

2. Use WHERE and HAVING Clause Accurately

The WHERE and HAVING clauses in SQL have different functionalities. Hence, we should use them differently. The three major use cases of WHERE and HAVING are below:

• WHERE can be used with CRUD queries i.e., SELECT, INSERT, UPDATE, DELETE. On the other hand, you can use HAVING with the SELECT statement only.
• WHERE filters data before any aggregation operation such as GROUP BY. Then it can be used without any aggregation function. HAVING should be used after aggregation.
• We can use the aggregation functions, such as SUM, MIN, MAX COUNT with the HAVING clause. With the WHERE clause, we can’t use aggregation functions, unless this clause is a part of a subquery contained by the HAVING clause.

3. Use INNER JOIN Query instead of the WHERE Clause for Joining Tables

The JOIN query is probably one of the most useful SQL queries. It allows you to SELECT data from multiple tables. Though you can use the WHERE clause to get aggregated data from two tables, the WHERE clause is highly inefficient.

The WHERE clause returns CROSS JOIN which is a cartesian product of records in both columns. For instance, if you have 1,000 records in table A and the same number of records in table B, the WHERE clause will create a CROSS JOIN with 1,000 x 1,000 = 1,000,000 records.

If the columns of tables A and B involved in the WHERE clause have only 1,000 common values, the WHERE clause will return 1,000 records from the initial 1,000,000 records created by the cartesian product.

The INNER JOIN clause returns only 1,000 records where both tables A and B have common values in the columns. In this case, INNER JOIN has 1,000x less work than the WHERE clause. 

Some databases convert the WHERE clause in the JOIN query into the INNER JOIN clause behind the scene. However, it is always recommended that you use INNER JOIN explicitly instead of the WHERE clause to follow SQL coding best practices.

4. Use EXISTS, NOT EXISTS instead of IN and NOT IN in SQL

Always use EXIST over the IN clause if you want to confirm the existence of a value in a particular table.

The process that executes the EXISTS clause stops as soon as it finds the required value in the table. On the other hand, the IN query scans all even after finding the necessary value.

In the same way, you should always use NOT EXISTS instead of NOT IN when you search for the value that doesn’t exist in a table.

5. Use Equals Operator (=) instead of LIKE Operator in SQL

You can use both the = and LIKE operators to match strings. The main difference between the two is that the LIKE operator is used for matching wildcards such as % to searching for partial strings, whereas the equals operator “=” looks for exact matches.

If you have to choose between the two, always prefer the equals operator (“=”), as it makes use of indexed columns.  Hence, it is faster than the LIKE clause.

6. Use the LIMIT clause to Reduce Search Results

If you have to return data from multiple tables or columns, use the LIMIT clause (also known as the TOP clause) to reduce query results. If there are thousands of columns, or you want to see what the data looks like in your tables only, it is not necessary to return all rows. Instead, limit the number of rows returned by the SELECT query with the help of the LIMIT clause in conjunction with it.

7. Use Table Aliases when Querying Multiple Tables

To avoid confusion and prevent databases from parsing column names when searching for the table they belong to, always use table aliases.

You already have to use table names/aliases if you have the same column names in multiple tables, so this won’t increase your workload.

8. Avoid Prefixing Stored Procedures with “sp_”

If you worked with stored procedures, then, most likely, you prefixed the stored procedure name with “sp_”. This isn’t the best.

SQL Server starts by looking for stored procedures with “sp_” at the beginning of their names in the master database before proceeding to search elsewhere.

Therefore, you can save plenty of time by not prefixing stored procedures with “sp_” Then, instead of trying to locate the stored procedure in the master database, the SQL server will directly check dbo as the owner of the stored procedure.

9. Adopt Good Query Writing Styling

It is essential to follow best practices for SQL queries like good styling practices when writing SQL queries. Pay attention to the below recommendations for improving your query writing style:

• Always add comments to SQL queries. Comments will not only help other team members to understand your queries better but also remind you what you were doing yourself in the past.
• Use obvious naming conventions. The database, tables, column names, temporary tables, and other variable names should be 100% readable and clear.
• Indent your queries whenever possible. Inner queries must be indented one tab from the left. Column names and types inside a table should also be indented. Indentation ensures a cleaner look and improves the readability of queries that is reflected in SQL Server best practices for queries.

Conclusion

SQL is a very flexible language providing numerous ways to perform desired tasks on a database. To make your applications more efficient and productive, and avoid long-term database issues, apply modern SQL query optimization best practices to write your queries. These methods help you accelerate performance tuning in SQL, eliminate unnecessary routines, and make all your work more concise and transparent.

Read also

22 Nifty SQL Index Examples to Warp Speed Your Queries