DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • How to Attach SQL Database Without a Transaction Log File
  • A Deep Dive into Apache Doris Indexes
  • Spring Boot Sample Application Part 1: Introduction and Configuration

Trending

  • The Cypress Edge: Next-Level Testing Strategies for React Developers
  • Key Considerations in Cross-Model Migration
  • Transforming AI-Driven Data Analytics with DeepSeek: A New Era of Intelligent Insights
  • Rust and WebAssembly: Unlocking High-Performance Web Apps
  1. DZone
  2. Data Engineering
  3. Databases
  4. The Best Way to Write a SQL Query

The Best Way to Write a SQL Query

How do you write SQL queries? Read this article to discover more than one way to write a SQL query, and see which is the best for you.

By 
Javin Paul user avatar
Javin Paul
·
Jun. 12, 18 · Opinion
Likes (14)
Comment
Save
Tweet
Share
32.8K Views

Join the DZone community and get the full member experience.

Join For Free

There is no doubt that writing code is more of an art than a science. Every coder cannot write beautiful code that 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. 

Same applies to SQL queries. The way you structure your query and 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 the conditions are.

Since in real-life projects SQL queries are hardly one-liners, learning the right way to write a SQL query makes a lot of difference when you read it yourself later or you share that query to 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 a 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.

By the way, I expect that you are familiar with SQL and know different clauses and their meaning in a SQL query. If you are not, it's better that you gain some experience with SQL by joining a good course like:

  1.  The Complete SQL Bootcamp by Josh Portilla, a Data Scientist 
  2.  SQL for Newbs: Data Analysis for Beginners by David Kim and Peter Sefton's 

The above are two courses I usually recommend to SQL beginners.

1st Way to Write a 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 keyword 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 can be unreadable as soon as the number of tables and columns increases

3) No flexibility in adding a new condition or running without an existing condition

2nd Way to Write a 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) The query is divided into multiple lines, which make it more readable.

Problems:

1) Mixed case

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

A Better way to write SQL queries

3rd Way to Write a 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 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 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

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 a SQL query that is readable and more maintainable. Feel free to give your opinion on what 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. If you'd 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.

Further Learning

Introduction to SQL

The Complete SQL Bootcamp

SQL for Newbs: Data Analysis for Beginners

5 Free Courses to Learn SQL and Database

Thanks for reading this article. Let me know in the comments how you write SQL queries, for example, which style you use or if you have your own style.

Database sql

Published at DZone with permission of Javin Paul, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • How to Restore a Transaction Log Backup in SQL Server
  • How to Attach SQL Database Without a Transaction Log File
  • A Deep Dive into Apache Doris Indexes
  • Spring Boot Sample Application Part 1: Introduction and Configuration

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!