A Guide to SQL Query Formatting
SQL coding style isn’t just a matter of aesthetics. Well-structured and understandable code will make your work much easier, primarily because it’ll be understandable to both you and your colleagues. Imagine opening an old query a couple of months later. Will you be able to quickly figure out exactly what you wrote? In this article, we’ll look at how to write SQL so it’s clean, neat, and easy to read.
Naming objects
Let’s start with the basics for comfortable working with the database:
- Avoid plural names.
- To combine words, use underscores: employee_city or CamelCase style: EmployeeCity.
- Do not use special characters such as $, &, *, etc. in the name.
- Don’t start your name with an underscore.
- Don’t give the same name to both the table and the column.
- Avoid abbreviations.
- Include the AS keyword to create “aliases”.
- Avoid using “id” as the primary key name. It’s better to combine “id” with the table name, for example: “id_employee.”
SELECT statement
- It’s best to write query keywords (
SELECT,FROM,WHERE,ORDER BY,GROUP BYandHAVING) at the beginning of a new line. IfSELECTattributes follow, it’s best to separate them by placing each on a separate line with equal indentation. - Be sure to place commas at the end of a line, not at the beginning of the next one.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
PersonAS p;
WHERE operator
If a statement WHEREhas more than one condition, place each condition on a new line, starting with the conditional ANDor statements OR.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
Person AS p;
WHERE
p.Name = 'New York'
OR p.Name = 'Chicago';
JOIN operator
If you’re joining tables, write the operators INNER JOIN[ , ] LEFT JOINand so on on a new line. ONIt’s also best to move the operator to a new line.
SELECT
p.PersonId,
p.FirstName,
p.LastName
FROM
Person AS p;
JOIN
City AS c
ON p.CityId = c.CityId;
Commenting
It’s best to avoid writing too many comments in your code. If they are necessary, use multi-line comments: /* text */. The comment should be written above the corresponding line of code.
SELECT
p.PersonId,
p.FirstName,
p.LastName,
/* Name column is the name of the city: */
p.Name,
FROM
Person AS p
WHERE
p.Name = 'New York';
Nested SQL query
If a query contains a subquery, it should be written on a new line and indented. For structure, CASEeach expression should WHENalso be placed ENDon a new line.
SELECT
p.FirstName
CASE
WHEN
p.Age < 18
THEN
'below 18'
WHEN
p.Age >= 18
THEN
'18 or more'
END
AS Age
FROM
Person AS p;
Other types of queries
Similar rules apply to queries that modify, insert, or delete data.
Write the keyword VALUESin input prompts at the beginning of a new line and wrap each tuple onto a new line with an indent:
INSERT INTO Car(id_car, name, year)
VALUES
(1, 'Audi', 2010),
(2, 'Skoda', 2015);
In the query, UPDATEthe keywords SETand WHERE, as in the query SELECT, are more convenient to wrap to a new line without indentation:
UPDATE
Car
SET
year = 2012
WHERE
Name = 'Audi';
Or in the request DELETE:
DELETE FROM
Car
WHERE
Name = 'Audi';
Conclusion
Writing SQL beautifully isn’t just a matter of aesthetics; it’s the key to success in database work. Maintaining a consistent style helps avoid chaos, speeds up work, and makes code accessible to everyone who encounters it. We hope you now have a clearer understanding of how to format SQL queries so that you can easily understand them six months from now.
Explore More IT Terms
#
A
- A Guide to SQL Query Formatting
- A/B testing
- Agile
- Algorithm complexity in 5 minutes
- Algorithms and Data Structures in C#
- An overview of the C # programming language
- An overview of the Python programming language
- Anaconda Python
- Android
- Android App Bundle
- Android SDK
- Angular
- Ansible
- Apache
- Apache Airflow
- Apache Kafka
- Apache Tomcat
- App Store
- AppCode
- Array-based stack
- ArrayList
- ASCII
- ASP.NET
- Assembly Language Lessons
B
C
D
- Data Analytics: applications of data analysis in companies
- Data Engineer - Who is it, what does a data engineer do, and an overview of the profession
- Data modeling: what it is, types, and process steps.
- Data preprocessing: a complete guide for beginners and professionals.
- Data structure
- Data structures
- Defining Aliases
- Defining Arrays
- Deque
- Developing a Website from Scratch
- Digital data: understand the importance of this asset for businesses.
- Doubly linked lists
E
F
H
- Handling errors and exceptions
- How to effectively organize your workflow
- How to Learn Java: Tips for Beginner Developers
- How to Learn PHP: A Beginner's Guide
- How to Use S3 Storage in Kubernetes with CSI
- HTML
- HTML and CSS: Definition, Application, and Operating Principles
- HTML and CSS. Layout from Scratch: What to Learn, Where to Learn, and How Long Will It Take?
- HTML Frame Structure
- HTML Link Formatting
I
- if..else construction
- Inserting an Image
- Interactive Python Tutorial – Learn Programming from Scratch
- Interview Problem: Finding a Deleted Element in O(N)
- Interview Scare: The FizzBuzz Challenge
- Introduction to C++
- Introduction to Machine Learning
- Introduction to programming languages
- IT Specialist Resume (CV)
K
M
O
P
S
- SFML Graphics Library Tutorials
- SQL commands: see what they are, what the main ones are + examples
- SQL Interview Questions and Tasks
- SQL Lessons
- SQL Stored Procedures
- SQL Syntactic Sugar: The COALESCE Function
- Stack
- Start in analytics: Python or R
- Statistical analysis: importance for decision making.
- String formatting in Python
- Swift Lessons
- switch/match construct
T
W
- What are databases, and why do they need DBMS and SQL?
- What do Linux distributions consist of?
- What is .NET and what is it used for?
- What is a GPU in a computer, in simple terms?
- What is Big Data? Introduction, Types, Characteristics, and Examples
- What is Golang and what is it used for?
- What is Haskell and what is it used for?
- What is Kotlin and what is it used for?
- What is Linux? The History of Linux
- What is machine learning, and how does it work?
- What is Power BI: everything about the data analytics software
- What is the C++ programming language?
- What is the OSI Model: A Complete Explanation of the Seven Layers and Their Role in Networking
- Where to start learning the C programming language?
- Which Linux distribution should you choose? A Linux distribution overview