Thursday, April 24, 2014
Home » SQL » Formatting SQL – Best Practices
Formatting SQL – Best Practices

Formatting SQL – Best Practices

PL/SQL Developer

Having worked with SQL for so many years, I have definitely made up my mind about formatting SQL. I am a strong-minded person, and I have my own opinions. Most of these opinions are based on experience, but some of them are..maybe…just traditions…sometimes driving colleagues nuts. :-)

In this post a I am going to let you know a little bit how I do most of my SQL queries. Know, that most of this are things I have found helpful as..should we say…”standards” for how I do my SQLs. But, if you are pretty new to Oracle SQL, or any other kind of SQL, there should be some nice pointers for you here.

So, let’s get started!

Editor Setup

Before we get into the SQL specific formatting, I feel it is important to mention a little bit about how I set up any of my editors. After all, most of the formatting you do is usually set up in your editor. During my workday I actually use several types of editors:

  • Notepad++
  • Adobe Flash Builder (for my Flex projects)
  • PL/SQL Developer
  • And sometimes some others

But, the common thing about all my editors, is that they are all set up the same way, even to the same colors some times.

In my opinion, setting up your SQL editor is one of the most important things you do. The reason is that you would want the formatting of your SQL to look the same, no matter what editor you view it in. If you in one editor use the tab character, and in another editor use some sort of “fill with spaces” functionality when you press the tab key…you might end up with some messy formatting.

So, that is why I try to do my best to do the same everywhere. But, enough talking – here are some of the things I always set up. To be blunt, if an editor does not handle these settings, I choose another editor.

  • Tab character: I always use the tab character. I never use “tab as spaces”. I also try to turn off any “smart fill” or “smart tab” settings. This way I can do a backspace, and it will outdent correctly, according to my tab size. I usually set my tab size to 4. With 2, it does not look like it is really indented some times…
  • Syntax Highlighting: Yes, definitely. My eyes is a little bit sensitive to bright light, so a white background is usually not that pleasant for my eyes after a while. I usually choose an editor that handles more than one syntax highlighting schema, or at least let you customize the settings for the default one. It is nice to see different colors on the various types of elements in your SQL statement. It makes it a lot better to read.
  • Use of Uppercase: When I do any kind of SQL coding, I always like to set my editor up to do all the SQL keywords as uppercase. Let me look around to see if I see anyone from Oracle…just a sec…nope.. One of the reasons I do NOT use Oracle’s SQL Developer is the way it handles uppercase on keywords. It looks like the conversion of the keywords to uppercase is not done until you press enter to go to the next line. To me that is a bit annoying some times. I feel that PL/SQL Developer from Allround Automations does way better job in this, and many other areas. Just my honest opinion.

Ok, so let us look at some of the other things that I consider.

Indentation and element placement

Ok, although I spoke about indentation under the editor setup, I also wanted to mention WHAT I usually indent. Also, where do I put what? They say that a picture is worth 1,000 words, so here is a sample of something I usually do:Formatted SQL Statement

  1. Keywords: They are always set up in my editor to be UPPERCASE. This has been a standard for me since I started with Oracle SQL and PL/SQL. If the editor does not make the key word with the right case, I sometimes make it uppercase myself… Yes, I am that picky.
  2. Indentation: All the vertical lines you see on the image above, are actually from my Notepad++ editor. It shows each of the “tab” stops in the code. As you can see, I always go to the next tabstop. What I mean is, when I have written the word “SELECT” I push the tab button. I almost never use a space, unless it is between two keywords, like between “YEAR FROM” on the above image.
  3. Aliases: For me it is important to use aliases in a helpful way. If you alias your tables “A”, “B”, “C”, etc…you will find that you end up having to look back to what you called “A”. I find it much better to alias my tables “ord” for orders, “cus” for customer, “inv” for invoice, etc.But, in the sample in the image above, you see that I use “x”, and “y” as aliases. I use these letters sometimes when I have to wrap a query around another query, as seen above.
  4. Parenthesis: I have been a software developer for about 25 years, so I know a lot about logical expressions. I try to use parenthesis everywhere there is any type of calculation, or expression.
  5. Commas: Ok, this is a place where I differ from many… I like the comma to be on the left side. The reason is that it is a lot easier to comment each of the lines in the SQL then.

ANSI SQL

I will probably start a war on this one, but it has to be said. In any of my SQL statements, you will NEVER see any use of ANSI type of SQL. I have always worked in Oracle shops, so for me there is no need to use any:

I am a (+) kind of guy:

Although Oracle has supported ANSI SQL for a while, I have never liked it, and to me it looks really messy. But, that is just my opinion.

Together with a colleague I had to go through a humongous SQL query from a SQL Server database, using T-SQL. We almost gave up when there were LEFT OUTER JOIN ON A…LEFT OUTER JOIN ON C….LEFT OUTER JOIN ON D, etc, etc.

Nope, it is not for me.

Visualize the result

In addition to my own projects, where I use a lot of PL/SQL and SQL, I am sometimes asked by my company’s Business Analysts to validate their SQL, or any other way help them to solve any SQL task or project.

Since I consider myself as a visual learner, it is important to me to somehow visualize what the outcome of the SQL query will be. Usually I write down the columns of a piece of paper, on my white-board, or sometimes I have the Business Analyst provide me with an Excel spreadsheet “mock-up”.

Sometimes it is easier to “see” how to attack your SQL, when you know what the outcome is supposed to look like. Some times you know that you will end up with a large and complicated SQL statement, and you have no clue where to start. Visualizing it helps me.

Step-by-step

When you know you that you might end up with a large SQL statement, you don’t start from the top, and then write the code line for line. You start from the “inside”. Here are some steps that I follow:

  1. Driving table: In a more complex SQL statement task, you need to establish what I call the driving table. Let us say that you are going to get the orders for last week, then you are going to check the payments for those orders, and then lastly add some information about the customers on those orders. For me, the logical driving table would be the orders table.
  2. Additional tables: When I started with Oracle SQL, back in 1997, I learned that you should (in your FROM statement) start with your driving table, and then add tables to the left of the driving table. Today the Oracle optimizer is very smart, so it probably does not matter anymore. But, I still practice this old-fashioned way of thinking. I put my next-to-driving table above my driving table, so basically if you put it on one line…it would be to the left of the driving table. Then, as I go on, I put additional tables above.
  3. The inner query: If you know that you are going to add a lot of customer information to your query, but your main query has some summarizations on the orders table, I would do the the summarization first. So you would i.e. select the customer id, from the orders table, and then do a GROUP BY on that field. Then I would wrap another query around the inner query.
    The reason I do this, is that I do not like to do a GROUP BY on more fields than I need to. Besides, it looks a lot cleaner to me.
  4. Repetive queries: Let us say that you are doing a query that is similar to the one shown under point # 3, and you a bit later (on a different level) in your query select the similar query, but for the same time period last year. To me, a bell would ring, telling me to consider using a WITH statement. I will go through the WITH statement and it’s use in a future tutorial.
  5. Use of SELECT *: I never use SELECT * for any query that is going to be used in any analytic scenario. I always specify the names of the few fields I need going along. It would then be easier to avoid “duplicate column names”. What I mean, is that if you have a SELECT * on the orders table, joined with a SELECT * on the customers table, and then wrap a query around it.. If you then in your outer query “SELECT customer_id…”, you would probably get an error, since the pre-compiler would not know which of the customer_id fields you were speaking about.

SQL Formatters

What do you do if you have an editor that does not format your SQL query very well? There are many free SQL Formatters available on the Internet. Here are a few examples:

But, these online tools would just be a backup solution for me. To me it is important to have tool available that do all the formatting you need for your SQL statements. The tool I have been using since I started as an Oracle developer, is PL/SQL Developer from Allround Automations.  PL/SQL Developer has an awesome feature, called “PL/SQL Beautifier“. You can set up your own rules on how you want your SQL query to look. So, if you get a query from some old view in the database, that looks totally messed up, you can mark the query and “beautify” it, based on your own rules. It can come in really handy. Here is a screenshot:

PLSQL Developer Beautifier

You can order PL/SQL Developer by clicking on the green button:

Order PL/SQL Developer *

* Affiliate link. For other license options than “single user“, please contact me through the contact page on my blog.

Conclusion

To me, writing a SQL statement can sometimes be like a piece of “art”… PL/SQL DeveloperIt is all about  the “looks” and readability. Not just for you, but for anyone that looks at your SQL code.

Now, if you get your whole team to follow the same standards…life is just golden! But, sometimes there are people in your team that have different experiences than you, and you might not be able to nail your your “law” to the wall as something everyone has to follow.

Based on experience working in large teams, the best thing is to come up with some common ground, and write down a short document that everyone agrees upon. I have even worked for a large company with it’s own R&D (Research and Development) team, setting all the standards for the whole company.

After reading all this, you might think “he’s just crazy picky”, or something. But, all these things are just “there” in my fingers, and in my head when I code SQL statements.

I hope this post has been of value to you. As always: “Sharing is caring“.

Also, if you have any thoughts, or other ideas…let us all know by leaving a comment.

~ TJ

Having worked with SQL for so many years, I have definitely made up my mind about formatting SQL. I am a strong-minded person, and I have my own opinions. Most of these opinions are based on experience, but some of them are..maybe…just traditions…sometimes driving colleagues nuts. In this post a I am going to let you ...

Review Overview

Rate this

Summary : If you enjoyed this post, rate it to let other people know what you think. Hey...don't forget to share as well :-)

User Rating: Be the first one !
0

About TJ Abrahamsen

TJ Abrahamsen is the founder and author of OracleTuts. He has worked with Oracle since 1997. His expertise is with Oracle SQL and PL/SQL. He loves teaching others, and like to share his thoughts, ideas, and experience on his blog.
  • http://www.sqlinform.com Guido

    Hi TJ,
    interesting article. Did you ever try SQLinForm sql formatter?
    Regards
    Guido

    • http://oracletuts.net TJ Abrahamsen

      Hi Guido -

      No I have not. I added it to the list in my post.

      Are you the creator of it? It looks pretty good. But, I usually like to have this available in my editor.

      ~ TJ

  • http://www.facebook.com/marina.nastenko.9 Marina Nastenko

    Looks like a great collection of anything one might need for formatting SQL. There is also a free online sql formatter by Devart http://www.sql-format.com/ Like the Red Gate’s one it is powered by a more comprehensive SQL Complete, looks solid and as far as I’ve explored has plenty of formatting options.

    • http://oracletuts.net/ TJ Abrahamsen

      Hello Marina -

      Thanks for your comment. I like some of Devart’s tools.

      I added it to the list :-)

      ~ TJ

      • http://www.facebook.com/marina.nastenko.9 Marina Nastenko

        Thank you TJ, I am glad that info was useful :-)