web analytics
Skip to content

Correctly format SQL queries

code sql indentation style - Correctly format SQL queries

In computing, the indentation style is used to format the code so that it is more readable by developers. By adding spaces, tabs, and newlines in the right places, code reading is made easier, saving time when reading code by a third party. This article provides recommendations for correctly formatting SQL queries.

Use single quotes for each column and table

One of the first recommendations that is not always applied is to use single quotes (on the keyboard: alt gr key + 7 key) around all keywords. This is to avoid errors in the event that one of the column words unintentionally matches the name of an SQL command. For example, if a table is called “where”, it can cause problems knowing that WHERE is also the name of an SQL command.

Before (request in error)

SELECT date, timestamp
FROM where

After

SELECT `date`, `timestamp`
FROM `where`

To note : to avoid such errors and to facilitate the understanding of the database, it is suggested not to use certain keywords when designing the tables which constitute the database. There are more than a hundred reserved words whatever the database system:

New line for each command

Putting each new command in an SQL query on the line is a simple method to dissociate each element that composes it. Thus, the reading of a request is clearer. This also makes it possible to reduce the width of a request to avoid scrolling horizontally in order to read the entire request.

Before

SELECT * FROM `utilisateur` WHERE `is_actif` = 1 ORDER BY `date_inscription`

After

SELECT *
FROM `utilisateur`
WHERE `is_actif` = 1
ORDER BY `date_inscription`

Warning : it is necessary to pay attention to the encoding of the characters to be sure that the new line is considered as a space between 2 commands.

No newline not necessary

The use of a newline is practical to better identify the elements, but using too much can affect the readability of the code within an application. Indeed, a developer’s computer screen is limited in size, if there is too much line feed then a simple code can become very long in height. Conversely, a code that does not take up too much space (in height) on the screen makes it possible to indirectly see the code which precedes and which is on the screen and thus have a better overview of the script while reducing the number of times the developer has to scroll.

Before

SELECT
    *
FROM
    `table`
WHERE
    `colonne` = 1

After

SELECT *
FROM `table`
WHERE `colonne` = 1

Formatting the WHERE with 1 = 1 or 1! = 1

When a query has many conditions within the WHERE command, an unknown trick is to use the condition “1 = 1” or “1! = 1” to standardize the queries.

Using WHERE 1 = 1

Before

SELECT *
FROM `produits`
WHERE `en_stock` = 'oui'
AND `favoris` = 'oui'

This query looks clean, but in reality it’s quite annoying to make clean PHP code (for example) to correctly prefix with WHERE or with AND when changing the conditions of the query. It is possible to standardize the code by using “WHERE 1 = 1”. In this way, each condition is presented using the logical operator AND.

After

SELECT *
FROM `produits`
WHERE 1 = 1
AND `en_stock` = 'oui'
AND `favoris` = 'oui'

This request is much easier to implement. It is also possible to delete the line “AND en_stokc = ‘yes’”Just by commenting out that single line, or removing it, without having to change the structure of the query.

To know : it does not affect performance.

Using WHERE 1! = 1

You can use the same trick for WHERE commands that use the OR operator.

Before

SELECT *
FROM `clients`
WHERE `nombre_achat` > 0
OR `client_premium` = 'oui'

After

SELECT *
FROM `clients`
WHERE 1 != 1
OR `nombre_achat` > 0
OR `client_premium` = 'oui'

Use spaces instead of tabs to indent

In many programming languages, it is recommended in code not to use tabs to indent code. Instead it is suggested to indent only with spaces.

The problem is with code editors which display tabs differently depending on the software used. Thus, for the same code, the tabs will be more or less wide depending on the software used, which completely breaks the display of the code. Therefore, you have to get used to indenting with spaces, in particular on projects where several developers will use the same code.

Homogeneity

Whatever style is used, it is more than essential to homogenize the style that is used by a developer and the team with whom he works. This saves time and avoids errors. For example, imagine that for a query you forget the newline for the WHERE command. A developer who quickly skims through the code may not notice the WHERE request because they are not used to reading that command there.

For this reason, it is necessary to agree on how to format SQL queries, even if this is different than what is recommended in this article.