Thursday, July 20, 2023

A Better way to write Complex SQL queries for Programmers

There is no doubt that writing code is more art than science, every coder cannot write beautiful code which is both readable and maintainable, even with experience. In general, coding improves with experience when you learn the art of coding e.g. favoring composition over inheritance or coding for interface than implementation, but only a few developers able to master these techniques.  The 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. When I see SQL queries on emails from multiple developers, I can see the stark difference in their writing style.

Some developers write it so neatly and indent their query properly, which makes it easy to spot the key details e.g. which columns you are extracting from which table and what are conditions.

Since in real-life projects, SQL queries are hardly one-liner, learning the right way to write SQL queries makes a lot of difference when you read it yourself later or you share that query with someone for review or execution.

In this article, I am going to show you a couple of styles that I have tried in the past, their pros and cons and what I think is the best way to write SQL queries. 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.





The Good way to write an SQL query

SELECT e.emp_id, e.emp_name, d.dept_name, p.project_name from Employee e 
INNER JOIN Department d ON e.dept_id = d.dept_id INNER JOIN Projects p 
ON e.project_id = p.project_id Where d.dept_name="finance" and e.emp_name 
like '%A%' and e.salary > 5000;


Pros:
1) The mixed-case was introduced to separate keywords from column and table names e.g. writing SELECT in a capital case and writing Employee in as it is, but given you are not consistent e.g. 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



The Better way to write SQL query

SELECT e.emp_id, e.emp_name, d.dept_name, p.project_name
from Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id
INNER JOIN Projects p ON e.project_id = p.project_id
Where d.dept_name="finance" and e.emp_name like '%A%' and e.salary > 500;

Improvement:
1) query is divided into multiple lines which make it more readable

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.

A Better way to write SQL queries


The Best way to write an SQL query

select e.emp_id, e.emp_name, d.dept_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;

1) Dividing SQL queries into multiple lines makes them 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 on one of the conditions e.g.

select e.emp_id, e.emp_name, d.dept_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%';
add e.salary > 5000

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

How to write readable SQL query



That's all about how to write 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 toward improving the readability of your complex SQL queries. If you like you can also use various SQL formatters online but I suggest you learn a style and stick with it, rather than relying on formatters.


Other SQL and Database Articles you may like
  • 5 Websites to learn SQL for FREE (websites)
  • 5 Free Courses to Learn MySQL database (courses)
  • My favorite courses to learn SQL and 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)
  • 10 Free Courses to learn Oracle and SQL Server (courses)
  • Top 5 Courses to learn MySQL for Beginners (courses)
  • Difference between WHERE and HAVING clause (answer)
  • Top 5 Courses to learn PostgreSQL for Beginners (courses)
  • 10 SQL queries from Interviews (queries)
  • Top 5 Courses to learn Microsoft SQL Server (Courses)
  • 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?

13 comments :

Badal Chowdhary said...

Nice post. 1 additional input: make all the sql text lower case and keywords upper case. it can help distinguish between key words and column/table names.

In SQL Developer, can be easily achieved by:
1. Ctrl + Shift + ' (change case)
2. Ctrl + F7 (Format: keywords are changed to uppercase)

Mladen said...

I think upper case sql keywords are not bad. If there is no sql highlighting available (maybe the sql is embedded in another language or just an email) then it's much easier to read. I also prefer indentation in some cases.

Turnkey Solutions said...

You have barely scratched the surface of SQL at this point. What about subqueries?

Anonymous said...

I always put the new table name first in a join clause, not second. That way the join looks more consistent if you're joining to Table c with clauses correlating to Table a and Table b, and if you have a simple join constraint on c, e.g. "
LEFT JOIN c
ON c.foo = a.foo
AND c.bar = b.bar
AND c.baz LIKE 'A%'

Anonymous said...

If you want to run query by commenting conditions, you better write
where 1=1
and id > 100
and name like '%s%'

CSC Programmer said...

I agree with @Badal that you should make all the sql text lower case and keywords upper case to help distinguish between key words and table names. Nice tutorial.

Unknown said...

I also think that the upper case for SQL keywords is not a bad thing at all.
The 'Department' and Employee with uppercase really bug's me here. That is camel case mixing with underscore_case.
Joining on e.emp_id = d.dept_id sounds very fishy, why would you want a employment id being joined on department id?? Or perhaps is your naming all goovy? Where project_name comes from totally baffles me... If you talk about good style, at least show this correctly on your website with correctly and working example SQL's (assuming the table exists).

For anybody looking for good books about SQL, Joe Celko's book simply rock!

javin paul said...

Hello @rvt, I agree with you. Actually some typo left, e.emp_id = d.dept_id should be read as e.dept_id = d.dept_id, a key column between these two table where dept_id is the primary key in Department but foreign key in Employee. Similarly, p.project_name come from Project table which represent the relationship between Employee and Project, again project_id is primary key in Project table and foreign key in Employee table, so if you need project name an employee is working on, you need to join Employee with Project.

Initially I wanted to show examples with multiple join conditions but later refactored to use just one join for simplicity, but looks like some typo left.

Thanks for pointing them out, I'll correct them.

shadrach said...

Nice tutorial. I highly concur with this . sometimes I find myself spending over 10 minutes trying to understand long queries simplely because they are not formatted right.

Anonymous said...

Nice

javin paul said...

Thank you Shardrach and Anonymous, yes formatting is key while writing long SQL queries.

Mladen said...

Personally I've found the best way is to use a sql formatter like the one built into Red Gate's SQL Prompt or the open source Poor Man's SQL Formatter https://poorsql.com/ - that way the sql formatting stays consistent and if you're checking sql into source control it makes changes a lot easier to track.

javin paul said...

Hello Mladen, If you can do that on regular basis then yes, that's the best

Post a Comment