Let us see what is SQL query formatter and how to format SQL to avoid errors.

SQL queries are just like any programming language and its important you format it as you writing it.

So it become easy to understand, troubleshoot and enhance by you or anyone else.

The guidelines that I am about to share here are based on my own experience writing complex queries.

And some that I have learn from other experts.

I have defined 6 basic rules. That help you remember these guidelines.

So let us start with rule number 1.

The rule of indentation.

The rule of indentation is to separate syntax from the expression using new lines and tab space.

This better organizes the code and also makes it easy for developers to read.

Let us see how it works.

Take an example of single line of select statement. It is simple select statement consists of one column being aggregated from one table.

Select SUM(Salary) From Employee;

Now applying rule of indentation we end up with the statement as show here

Select
SUM(Salary)
From
Employee

As you can see here the keywords are maintained in one line whereas all the non keywords are the expressions are moved to a separate line also it prefix of tab space.

So this better organizes the code and for someone who is looking at this code one can easily see what are the keywords.

What are the expressions and also the fact that now the code occupies a lot less width of the screen compared to the other one.

So this contributes to the overall readability of the code.

Moving on Rule number 2.

The rule of capitalization.

So we start off with the query that we enhanced applying rule number 1 and we make it even better.

We do that by changing all the keywords to upper case or in other words changing all words that are specific to the database as in keywords are in syntax's.

For example Select, SUM and From clause.

SELECT
SUM(Salary)
FROM
Employee

as you can see applying this rule we end up with this statement like this where SELECT, SUM and FROM are in upper case and for the expressions we can either choose them in lower case or we can apply unit cap means we pick first alphabet of every word and change it to upper case while retaining the rest of the words in lower case.

This again greatly enhances the readability of your code.

Rule 3

Rule of aliases.

Let us suppose we start with the simple query like this

SELECT
Customer_ID,
Customer_Name,
Customer_Address,
Zip_Code
FROM
Customers

It is a select statement accessing four columns from a single table. After sometime we decided to enhance this query we start joining this table with 2 other new tables on appropriate join conditions.

SELECT
Customer_ID,
Customer_Name,
Customer_Address,
,Zip_Code
FROM
Customers CR
JOIN Sales SL on (CR.Customer_ID= SL.Customer_ID)
JOIN Location LC on (CR.Location_ID=LC.Location_ID)

So you notice that in the query that I have written although the nothing has changed in the select statement it will show a possible error and the error description is ambiguous column name.

What that means is now you have added the new tables to this query the database is not sure which column this table belongs to the reason is this column is available in more than one table.

So this is cause for an error even if I am able to execute this query it would through an error so what can we do about it?

As per the rule 3 the rule of alias it would be a very good idea that whenever you write a query even if it includes a single table always give it an alias.

You never know when you are going to enhance the query drastically adding other tables.

Moving on the rule number 4 the rule of commas.

Look at the above query. We displayed 4 columns from a single table it is usual habit that we give coma at the end of the column.

As per the rule that I follow I always make sure I give the comma at the begging of the column not at the end of the column and there is a reason behind it.

SELECT
Customer_ID
,Customer_Name
,Customer_Address
,Zip_Code
FROM
Customers CR

The reason is that when you write a query and particularly a long one it is expected that over sometime you will make so many changes to the query. As you make every change you may need to test the select query with or without some columns right. So it tipically works like this.

SELECT
     Customer_ID
-- ,Customer_Name
-- ,Customer_Address
    ,Zip_Code
FROM
Customers CR

You may have to comment it one or two columns to test the code and when you do that the probability of you commenting out the first column is lot less.

See the column that you first start with usually you get it right? The other columns you add later which could cause issues right?

I would suggest is add a comma at the beginning of every column so it makes it very easy for you to comment it as you test and you can do this commenting till the last line or till the last column without affecting the syntax of the overall select statement.

So, some of you may argue that we can achieve this something in by adding comma towards the end of the column I totally agree to it.

However if you come across the situation where the first column is fine the rest of the columns that cause an issue then having a column at the beginning makes it a lot easier.

Rule number 5

So the rule number 5 is of the join clause. Lets take this rather complicate select statement with quite a lengthy join condition. Again we can apply the rule of indentation to get some pattern into it.

SELECT
SUM(SL.Sales)
CR.Customer_ID,
CR.Customer_Name,
FROM
Customers CR JOIN Sales SL on (CR.Customer_ID=SL.Customer_ID) AND CR.Customer_ID=101 AND CR.Customer_Name='JOHN' Group b CR.Customer_ID

After applying the rule of indentation we end up with the statement like this.

SELECT
SUM(SL.Sales)
CR.Customer_ID,
CR.Customer_Name,
FROM
Customers CR
JOIN Sales SL
ON
(
(CR.Customer_ID=SL.Customer_ID)
AND CR.Customer_ID=101
OR CR.Customer_Name='JOHN'
Group b CR.Customer_ID
)

Now since there are more than one condition I have enclosed with parentheses and the moment I do this, The moment I apply the rule of indentation it is easy to see that there is a error with this condition there is some ambiguity.

By simply organizing this code by applying the rule of indentation we have identified that something possibly wrong with this query right?

The point being you apply the indentation to every block in your select statement including the join condition and when you do you will figure out issues much before they go into production.

The last rule 6 is the where clause.

Consider a statement such as this, where you have the select statement with a where condition a set of criteria right?

SELECT
SUM(SL.Sales)
CR.Customer_ID,
CR.Customer_Name,
FROM
Customers CR
JOIN Sales SL
ON
(
(CR.Customer_ID=SL.Customer_ID)
AND CR.Customer_ID=101
OR CR.Customer_Name='JOHN'
Group b CR.Customer_ID
)

This nothing wrong with this. This is perfect It will work fine as well. But it is when you have to troubleshoot. Imagine this line of code may be hundred lines even thousand lines of code.

Troubleshooting a code that long requires a structure and its not easy and most of the queries usually have issues with the where clause write.

We spend a lot of time. playing around with the where clause until you zero end up with the issue.

So what I would suggest is you can enhance the where clause by adding by one simple line.

So here you will see that the were clause has an additional line or additional condition one equal to one and enclosed with parentheses.
(1=1)

So the 1=1 alone does not effect the query at all.

It simply acts as dummy line. So how does this help?

This helps in commenting out the code as you can see here you can comment it absolutely any line of the code without loosing the syntax integrity of overall select statement. That is only the purpose it serves but it is very very efficient.

SELECT
CR.Customer_ID,
CR.Customer_Name
FROM
Customers CR
JOIN Sales SL ON (CR.Customer_ID=SL.Customer_ID)

WHERE
(1=1)
AND CR.Customer_ID=101
--OR CR.Customer_Name='JOHN'

Thing about it If i have to comment out the first line in the initial version of the query that I wrote. If I do this means If I comment out the first line see that there is an AND operator without preceding itself.

These are the rules or tips to format SQL query to avoid mistakes.

I hope this information is useful to you and which can help your to format SQL and troubleshoot your queries in efficient manner.