javinpaul

Posted on Sep 19, 2019 • Edited on Jun 8, 2021

How to write SQL queries which are easier to read?

#sql #database #programming #development

Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.

There is no doubt that writing code is more art than science and every coder cannot write beautiful code which is both readable and maintainable, even with the experience. Yes, it's blunt and hard but it's mostly true.

In general, coding improves with experience but only when you learn the art of coding like favoring composition over inheritance or coding forinterface than implementation, but, unfortunately only a few developers able to master these techniques.

Same applies to SQL queries. The way you structure your query, the way you write it goes a long way to communicate your intent to the fellow developer, DBA and even yourself after a few months.

Whenever I see SQL queries on emails from different developers, I can see the stark difference in their writing style. Some developers and DBAs write it so neatly and indent their query such that you can easily spot key details like which columns you are extracting, and from which table, and what are joining or filtering conditions.

Since in real-life projects, SQL queries are hardly one-liner, learning the right way to write complex SQL query makes a lot of difference; especially when you share that query to someone for review or execution. It also helps when you read it yourself later as I said, after a few months.

The problem is there are many books and courses to teach you SQL like what is a table, different SQL commands but there are very few (like The Complete SQL Bootcamp by Josh Portilla) which focus on writing proper SQL queries.

In this article, I am going to show you a couple of styles which I have tried in past, their pros and cons and what I think is the best way to write SQL query.

Unless you have a good reason not to use my style e.g. you have a better style or you want to stick with the style used in your project (consistency overrules everything) there is no reason not to use it.

Btw, I expect that you are familiar with SQL and definitely know how to write query. I expect that you have used different SQL clauses like SELECT, INSERT, UPDATE, DELETEand understand their meaning in a SQL query. If you are not, it's better you gain some experience with SQL by joining some of my recommended courses like:

Learn SQL by CodeCademy
Introduction to SQL by Jon Flanders
The Complete SQL Bootcamp by Josh Portilla, a Data Scientist, on Udemy or
SQL for Newbs: Data Analysis for Beginners by David Kim and Peter Sefton's course on Udemy.

They all are great courses and teach you SQL basics, but, if you need some free alternatives you can also checkout this list of free SQL courses for programmers and developers.

Anyway, let's examing a couple of ways to write SQL query and find out which one is the best way to express intent in quick time:

SQL Query Version 1.0

Pros:

The mixed case was introduced to separate keyword from column and table names like writing SELECT in a capital case and writing Employee in as it is, but given you are not consistent like SELECT is in caps but from is in small, there is no benefit of using that style.

Cons:

1) Mixed case.
2) The whole query is written on one line which gets unreadable as soon the number of tables and columns increases.
3) No flexibility in adding a new condition or running without an existing condition

SQL Query Version 2.0

Improvement:

1) SQL query is divided into multiple lines which make it more readable, yes, that small thing makes a huge difference.

Problems

1) Mixed case
2) All conditions on WHERE clause is on the same line, which means excluding them by commenting is not that easy.

SQL Query Version 3.0 (Best)

Pros

1) Dividing SQL queries into multiple lines makes it more readable.
2) Using proper indentation makes it easy to spot the source of data i.e. tables and joins
3) Having conditions on separate lines allow you to run the query by commenting one of the conditions as shown below:

Btw, if you notice there is a subtle differnece, I have added WHERE 1=1 which means you can comment any condition without commenting WHERE clause which was not possible in earlier version of query.

If you are a fan of CAPITAL case for keywords, you can also write the same SQL query as shown below, the rules are same but just capital letters for keywords.

That's all about how to write a SQL query which is readable and more maintainable. Feel free to give your opinion on what do you think of this indentation or styling of SQL queries.

It's a simpler technique but very powerful and goes a long way on improving the readability of your complex SQL queries involving multiple joins as shown in my earlier example.

If you like you can also use various SQL formatters online but I suggest you learn a style and stick with it, rather relying on formatters.

Thanks for reading this article and let us know how do you write SQL queries? which style you use, or you have your own style? If you are a beginner and learning SQL, you may also find my list of free SQL courses and books helpful.

So, what's your thoughts? Does these points make sense? Which SQL style are you using? And, can you make it even more readable and maintainable?

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Other SQL and Database Articles you may like

5 Websites to learn SQL for FREE (websites)
5 Free Courses to Learn MySQL database (courses)
5 Free Courses to learn Database and SQL (courses)
5 Books to Learn SQL Better (books)
How to join more than two tables in a single query (article)
Difference between WHERE and HAVING clause (answer)
10 SQL queries from Interviews (queries)
Top 5 SQL books for Advanced Programmers (books)
Difference between SQL, T-SQL, and PL/SQL? (answer)
Top 5 Online Courses to Learn SQL and Database (courses)

Thanks for reading this article and let me know how do you write SQL queries? which style you use, or you have your own style?

P. S. - If you are looking for a free course to start learning SQL and Database basics then I suggest you go through Introduction to Databases and SQL Querying course on Udemy. It's completely free, all you have to do is create a Udemy account and you can access whole course.

Top comments (20)

Gpower2 • Sep 19 '19

Hey javinpaul, nice article! I just wanted to add my 2 cents here, since I have also developed a style writing big and complex SQL queries.
In your example, I would write the query like this:

SELECT
    e.emp_id
    , e.emp_name
    , d.dpt_name
FROM
    Employee e
    INNER JOIN Department d
        ON e.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND e.emp_name LIKE '%A%'
    AND e.salary > 500

This style allows easily commenting out columns and criteria, identifying the tables and joins, along with the join criteria.

One more advanced example with CTE would be something like this:

;WITH E AS (
    SELECT 
        *
    FROM
        Employee
)
SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%A%'
    AND E.salary > 500

Finally, you could write UNIONs easily with this style:

;WITH E AS (
    SELECT 
        *
    FROM
        Employee
)
SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%A%'
    AND E.salary > 500

UNION

SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%B%'
    AND E.salary > 500

javinpaul • Sep 20 '19

Hello @gpower2 , I must say your style is better than mine :-) thanks for sharing with us.

Gpower2 • Sep 20 '19

I'm really glad you liked it @javinpaul !
Always happy to see fellow devs trying to be better and share their thoughts with the community!

wara matsuluka • Sep 20 '19 • Edited

@gpower2 I like your style and thats what we enforcing at our work place.

Guilherme Taffarel Bergamin • Sep 20 '19

God knows how much I hate those implicit joins. You never know what's going on on this kind of queries.

My queries are usually more or less like this:

SELECT A.field1
      ,B.field2
      ,CASE B.field2
            WHEN 1 THEN 'Text 1'
            WHEN 2 THEN 'Text 2'
            ELSE 'Else Text'
       END AS field3
      ,B.field4
  FROM table1      A
 INNER JOIN table2 B
    ON B.id_table1 = A.id
   AND B.field5 = 1
 WHERE A.field6 = 2
   AND A.field7 = 3;

javinpaul • Sep 20 '19

Sorry, I didn't get your point, isn't join is explicit on my query example?

Guilherme Taffarel Bergamin • Sep 20 '19

It's not a point, I'm just saying I rather do queries the same way you did. I'm not talking about your example, I'm talking about most people I've been working with.

Looks like people are stuck in time sometimes. Huge SQL lines, list of tables instead of JOINs, a lot of business rules in procedures and triggers when they should have been added to the application backend, etc.

javinpaul • Sep 21 '19

Ah, I see from where you are coming. I have seen such queries, and, yes, they are very messy to deal with. I can understand your pain, especially if you have to make a change on such code.

Carla Lewis 🐿️ • Sep 19 '19

I'm of the opinion that using a formatter can actually familiarize the developer with accepted formatting guidelines. They reinforce uniformity.

While not everyone can afford a Red Gate formatter, I've had good experiences with architectshack.com › Poor Man's T-SQL Formatter which has a free plug-in for SSMS.

Guilherme Taffarel Bergamin • Sep 20 '19

VSCode also has it. Good enough. Great for when your console spits out a huge query in one line

javinpaul • Sep 20 '19

Yes, a formatter is a good option and it can also give consistency. Does VSCode has by default or there is any extension/plugins?

Guilherme Taffarel Bergamin • Sep 20 '19

Now you've got me. I'm not sure if from a plugin.

I've been using this one for SQL Server:

github.com/microsoft/vscode-mssql

And this one for other (recent project, not so many contributors, so not yet so full of features):

github.com/mtxr/vscode-sqltools

If select query and right click doesn't show a "format code/file", it probably comes from one of these.

Victor Warno • Sep 19 '19

Hey javinpaul, thanks for encouraging proper capitalization in order to have readable queries. When I read very long and complex queries, I often get lost as well. Do you also have techniques how to cope with multiple UNIONs and a WITH sections in one query? Is one big query better or worse than multiple small but comprehensive ones?

Sometimes I had struggles reading parts of your article. Could it be that your images are jumbled up? The first query (1.0) is not written all in one line as the description states. The first image of 3.0 is not indented. Also, in the second one of 3.0, shouldn't it be 1=1 instead of 1==1? There you could actually mention this as a cool trick, to have the possibility to comment conditions out (without commenting the WHERE keyword)!

Also, there is a typo:
tecach -> teach

Thank you for the links as well! Haven't heard of T-SQL yet.

javinpaul • Sep 19 '19

Hello @victor , first of all very valuable comment.

I have corrected the typo, thanks for that.
In the first version, it was indeed in one line but I had to break just to show that properly in this article.
Yes, somehow indention lost in the image on 3rd, the capital version has a proper indentation.
Yes, using WHERE 1=1 is a nice trick I learned to easily comment condition and try query.
Regarding multiple UNION, could you elaborate more? I think this style will help but I generally use brackets ()
For with queries also, I prefer brackets.

Once again thanks for the comment and making the article better.

sagie • Sep 21 '19

I found this article rather obvious, unless someone just started with sql development.

I would recommend using ssmsboost. Google it. It's a free ssms add in if you use the community version. There are so many useful features there that you will soon not be able to work without it. Do yourself a favour, try it out and understand its features. Oh BTW, it has an sql formatter. Not thrilled with it, but you get quick consistency, perfect to help with diffs.

javinpaul • Sep 21 '19

Thanks @sagie, it may sound obvious but not really as everybody has there own style and many developer doesn't give formatting a thought when writing SQL query, mostly because you don't need it when your query is small. But, when you have more than 4 tables joining, and feting 50+ columns with 10+ conditions it make sense to format it. I didn't know about ssmsboost, thanks for the pointer, I'll take a look.

Jannik Wempe • Sep 20 '19

There is also one more big advantage in adding "1=1" to your WHERE-clause: You can add conditions dynamically without checking if there already is a condition. Without 1=1 as the first condition you have to check if you also have to add an "AND" (if condition 2+) or not (first condition).