DEV Community

How to write SQL queries which are easier to read?

javinpaul on September 19, 2019

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

Read full post

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).

javinpaul • Sep 20 '19

Indeed, thanks for comment @Jannik

Ben Halpern • Sep 19 '19

Nice post

javinpaul • Sep 20 '19

Thanks Ben and a belated happy birthday :-)