Esat Erkec
We need to use IS NULL or IS NOT NULL syntax when we compare the NULL values in a table

5 Best Practices for writing SQL queries

December 30, 2022 by

In this article, we are going to learn some best practices that help to write more efficient SQL queries.

Introduction

Queries are used to communicate with the databases and perform the database operations. Such as, we use the queries to update data on a database or retrieve data from the database. Because of these functions of queries, they are used extensively by people who also interact with databases. In addition to performing accurate database operations, a query also needs to be performance, fast and readable. At least knowing some practices when we write a query will help fulfill these criteria and improve the writing of more efficient queries.

Pre-requisites

In this article’s examples, we will use the Adventureworks2019 sample database.

Deciding the appropriate SQL editor to write queries

Whatever our experience in writing queries, deciding on an appropriate editor is one of the key points that will affect our productivity because, during the development of SQL queries, we mostly consume our time in the SQL editors. Preference of the SQL editor can be changed from person to person but before to decide an editor checking the features of the editor and looking at the feature comparison with its competitors will help to decide on the proper editors. In this context, Microsoft offers two different tools to develop queries:

These two tools have some advantages and disadvantages, but the main advantage of the Azure Data Studio is to can work on different platforms (Linux and macOS) and it offers a more user-friendly user interface for professionals who commonly develop queries. At the same time, the extensions allow us to add new features to it. Despite this, SQL Server Management Studio helps to manage and maintain the database administrators’ operation more easily and offers a more advanced SQL query development environment. So, if we mostly consume our time developing SQL queries, using the Azure Data Studio can be more reasonable. Except then these two tools, we can also use other editors which are developed by lots of vendors.

Best Practice: As much as possible as a preference to use the proper editor for your requirements and you can consider using either 3rd party add-ins or extensions that improve the capabilities of the editors.

Avoid using the asterisk sign (SELECT *) in the select SQL queries

Using the SELECT * statements in the queries may cause unexpected results and issues in the queries’ performance. Using an asterisk sign in a query causes redundant consumption of the database engine’s resources because it will retrieve all columns of the table. In particular, using SELECT * provokes consuming more network and disk resources. Another problem with using the SELECT * sign is to be facing unexpected result sets because:

  • Column names can be changed
  • New columns can be added
  • The columns’ order can be changed

To prevent these types of problems, we need to explicitly write the column names in our SQL queries. For example, the following query will retrieve all column data of the Employee table.

The issues when using the SELECT * statement in a query.

However, we can transform this query correctly by explicitly defining the column names as follows and including only the columns which we needed.

Using the SELECT * statement will cause performance problems. Using the SELECT * statement will cause performance problems.

Best Practice: Besides getting rid of SELECT * statements and using the column names explicitly in the SQL queries, we can use the alias names for the tables and column names. This usage type makes our queries more readable and easily understandable.

You can use aliases for the table names to improve the code reusability.

Add the requisite comments to the SQL queries

SQL comments are the plain text that can be added to the queries, and they are not parsed and executed by the query engine. Mostly, we use the comments either to add some description or disable some code blocks of the queries. However, adding brief and understandable explanations to our SQL queries is one best practice because, over time, the purpose of the query and its use by the which application can be forgettable. In this case, the process of maintaining and refactoring the query will be a bit painful.

Single-line comment:

To change a line as a comment, we can add the two dashes (–) at the beginning of the query line, thus this line color will be changed, and these lines will not be considered by the query engine.

Multiple line comment:

By placing multiple lines inside this sign () block, we can convert them into multiple comment lines.

Best Practice: As possible as standardize your SQL comments and add all short information that you required according to your development process. For example, you can use the following template:

Consider NULLable columns in SQL queries

A NULL value in a row specifies an unknown value and this value does not point to a zero or empty value. Because of this particular characteristic of the NULL value, we need to take into account nullable columns in the queries.

Comparing the NULL values:

When we either filter out the NULL or exclude NULL rows in a query, we cannot use the use equality operator (=) in the WHERE clause. The proper way to compare the NULL values is to use IS NULL and IS NOT NULL operators. For example, if we want to return only NULL values from the Address table, we can use the following query:

We need to use IS NULL or IS NOT NULL syntax when we compare the NULL values in a table

When we use the equality operator (=) in the WHERE clause, we do not get the appropriate result set.

Don't use equality operator to find out the NULL values in a column

COUNT() function and NULLable columns:

The COUNT() function counts and returns the number of columns from the query result set. However, the COUNT(*) function counts all rows of the query resultset but if we replace the column name with the asterisk sign COUNT(column_name), the function counts only the non-null values. For example when we use the asterisk (*) for the COUNT function to count the Employee table rows, we will obtain 290.

Consider the NULL values during the usage of the COUNT() function

However, if we use the column name instead of the asterisk sign the COUNT function returns a different value.

The NULL values will be  ignored when we use the column name in the COUNT() function

Beautify SQL Queries

The queries which we write will never remain a secret and will need to be reviewed and modified by us or by other developers because they might need to test, fix or add a new feature. Because of this case, the queries we write should be as understandable and easy to read as possible. Following the suggestions below for writing more readable code will help us write more readable code.

1-Format SQL Queries: Formatting the queries is one of the important points to improve the readability of a query. A well-formatted query always significantly improves code readability. To make our queries more readable, we can take advantage of online query formatting tools or add-ins (extensions). For example, we can see the mess and complexity of the following query:

Now, we will format this code through the Poor SQL format and we can see the stunning change in the query, or you can choose the formatter that suits your needs.

2-Use aliases for the column and table names: We can use the alias to rename the column and table names so that we can make them more readable. For example, in the following example, the query does not use table and column allies for this reason it seems messy and difficult to read.

Now, we will use aliases for the column names and table names to further format it.

As we can see there is a noticeable readability improvement after using and formatting the query.

Best Practice: If you want to write more readable and understandable queries:

  • Use understandable aliases for the table names
  • Add brief comments to queries
  • Consider using Common Table Expressions (CTE) in your complex queries
  • Variable names should be clear and concise
  • Format the queries before deploying

Summary

In this article, we learned some best practices that help to improve the quality of SQL queries. Well-formatted, more readable, and performant queries will always help us or another person who needs to review or modify the queries.

Esat Erkec
168 Views