SQL Coding Best Practices for Writing Clean Code | by Anmol Tomar | May, 2022
Learn how to make your SQL code interpretable
The journey of reaching good analysis results is full of explorations, experimentations, and testing of multiple approaches. In this journey, we write a lot of codes that we and others refer to while working as a team. If your code is not clean enough, it will become a pain for others (and you too) to understand, reuse, or modify your code.
If you write a neat and clean code, you will thank yourself and others will thank you too.
This blog will introduce you to the SQL coding best practices for writing neat and clean code.
1. Use Upper Case for the Clauses
You should use upper case for the SQL clauses as it will help you or anyone reading your code to easily find what operations/manipulations you are performing.
Bad Practice
Select order_id, prod_id, sales from db.Order
Good Practice
SELECT order_id, prod_id, sales FROM db.Order
2. Use Indentation/proper spacing
Having a proper indentation of your code is very crucial, it makes your code look clean and thus very easy for anyone to follow.
Tips for Indentation:
- Select one column per line.
- Write one clause per line.
Bad Practice
SELECT columnB, AGG_FUNC(column_or_expression) FROM left_table JOIN right_table ON left_table.columnA = right_table.columnA WHERE filter_expression GROUP BY columnB
HAVING filter_expression ORDER BY columnB ASC/DESC LIMIT ROW_COUNT;
Good Practice
SELECT columnB,
AGG_FUNC(columnC)
FROM left_table
JOIN right_table
ON left_table.columnA = right_table.columnA
WHERE filter_expression
GROUP BY columnB
HAVING filter_expression
ORDER BY columnB ASC/DESC
LIMIT ROW_COUNT;
3. Use Alias
Aliases are used to give columns , an intuitive user-defined name. Aliases in SQL are created using ‘as’, followed by the user-defined name.
Bad Practice
SELECT OrderID,
SUM(Price),
FROM table1
INNER JOIN table 2
ON table1.ProductID = table 2.ProductID
GROUP BY OrderID
Good Practice
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
4. Intuitive Table names:
Setting intuitive table names makes it very easy for fellow developers to understand the objective of the code. The names should represent what you are trying to achieve.
Bad Practice
CREATE TABLE db.table1 as
SELECT OrderDate,
count(OrderID)
FROM Orders
WHERE EmployeeID = 3
GROUP BY OrderDate
Good Practice
CREATE TABLE db.Orders_across_dates as
SELECT OrderDate,
count(OrderID) as Total_Orders
FROM Orders
WHERE EmployeeID = 3
GROUP BY OrderDate
5. Comment the code
Commenting on the code is one of the most important steps to make your code human-readable and easy to understand.
The comments should cover the following aspects:
- The objective of the code.
- Author Name.
- Script Date.
- Description of the code.
Bad Practice
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
Good Practice
/*
Objective: Get the order amount corresponding to each order.
Author: Anmol Tomar
Script Date: May 05, 2022
Description: This code gives the order id and corresponding order amount by joining OrderDetails and Products table on ProductID (Primary Key)
*/
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
6. Mention column names in the SELECT clause
Using column names in the SELECT clause is very useful to understand which columns (or information) we are selecting for our output. Using select * makes the output a bit of a black box as it hides the selected information/columns.
Bad Practice
SELECT *
FROM db.Order
Good Practice
SELECT order_id,
prod_id,
sales
FROM db.Order
Learn how to make your SQL code interpretable
The journey of reaching good analysis results is full of explorations, experimentations, and testing of multiple approaches. In this journey, we write a lot of codes that we and others refer to while working as a team. If your code is not clean enough, it will become a pain for others (and you too) to understand, reuse, or modify your code.
If you write a neat and clean code, you will thank yourself and others will thank you too.
This blog will introduce you to the SQL coding best practices for writing neat and clean code.
1. Use Upper Case for the Clauses
You should use upper case for the SQL clauses as it will help you or anyone reading your code to easily find what operations/manipulations you are performing.
Bad Practice
Select order_id, prod_id, sales from db.Order
Good Practice
SELECT order_id, prod_id, sales FROM db.Order
2. Use Indentation/proper spacing
Having a proper indentation of your code is very crucial, it makes your code look clean and thus very easy for anyone to follow.
Tips for Indentation:
- Select one column per line.
- Write one clause per line.
Bad Practice
SELECT columnB, AGG_FUNC(column_or_expression) FROM left_table JOIN right_table ON left_table.columnA = right_table.columnA WHERE filter_expression GROUP BY columnB
HAVING filter_expression ORDER BY columnB ASC/DESC LIMIT ROW_COUNT;
Good Practice
SELECT columnB,
AGG_FUNC(columnC)
FROM left_table
JOIN right_table
ON left_table.columnA = right_table.columnA
WHERE filter_expression
GROUP BY columnB
HAVING filter_expression
ORDER BY columnB ASC/DESC
LIMIT ROW_COUNT;
3. Use Alias
Aliases are used to give columns , an intuitive user-defined name. Aliases in SQL are created using ‘as’, followed by the user-defined name.
Bad Practice
SELECT OrderID,
SUM(Price),
FROM table1
INNER JOIN table 2
ON table1.ProductID = table 2.ProductID
GROUP BY OrderID
Good Practice
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
4. Intuitive Table names:
Setting intuitive table names makes it very easy for fellow developers to understand the objective of the code. The names should represent what you are trying to achieve.
Bad Practice
CREATE TABLE db.table1 as
SELECT OrderDate,
count(OrderID)
FROM Orders
WHERE EmployeeID = 3
GROUP BY OrderDate
Good Practice
CREATE TABLE db.Orders_across_dates as
SELECT OrderDate,
count(OrderID) as Total_Orders
FROM Orders
WHERE EmployeeID = 3
GROUP BY OrderDate
5. Comment the code
Commenting on the code is one of the most important steps to make your code human-readable and easy to understand.
The comments should cover the following aspects:
- The objective of the code.
- Author Name.
- Script Date.
- Description of the code.
Bad Practice
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
Good Practice
/*
Objective: Get the order amount corresponding to each order.
Author: Anmol Tomar
Script Date: May 05, 2022
Description: This code gives the order id and corresponding order amount by joining OrderDetails and Products table on ProductID (Primary Key)
*/
SELECT Products.OrderID,
SUM(OrderDetails.Price) as Order_Amount,
FROM table1 as OrderDetails
INNER JOIN table 2 as Products
ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.OrderID
6. Mention column names in the SELECT clause
Using column names in the SELECT clause is very useful to understand which columns (or information) we are selecting for our output. Using select * makes the output a bit of a black box as it hides the selected information/columns.
Bad Practice
SELECT *
FROM db.Order
Good Practice
SELECT order_id,
prod_id,
sales
FROM db.Order
Denial of responsibility! Techno Blender is an automatic aggregator of the all world’s media. In each content, the hyperlink to the primary source is specified. All trademarks belong to their rightful owners, all materials to their authors. If you are the owner of the content and do not want us to publish your materials, please contact us by email – [email protected]. The content will be deleted within 24 hours.