Best practices for writing professional SQL code require good formatting skills. In this article, we will learn different professional SQL code formatting techniques :
1. Write SQL keywords in capital letters for better readability.
Example:
SELECT, FROM, WHERE.
2. Use table aliases with columns when joining multiple tables.
SELECT
ord.column_1,
ord.column_2,
rtrn.column_1,
.
.
FROM table_1 AS ord
JOIN table_2 AS rtrn ON <ord.column = rtrn.column>;3. Never use select *, always mention the list of columns in the select clause before deploying the code to production.
example:
SELECT <table_alia.column_list>
FROM table_1
INNER JOIN table_2 ON <table_1.column = table2.column>;instead of :
SELECT * FROM table_1;4. Add useful comments wherever we write complex logic.
5. Use joins instead of subqueries when possible for better performance.
6. Create CTEs instead of multiple sub-queries, it will make the query easy to read.
7. Join tables using JOIN keywords (ANSI-92 JOIN Syntax) instead of writing join conditions in WHERE clauses for better readability.
example:
SELECT
<table_alia.column_list>
FROM table_1
JOIN table_2 ON <table_1.column = table2.column>;instead of :
SELECT <table_alia.column_list>
FROM table_1
JOIN table_2
WHERE <table_1.column = table2.column>;8. Never use order by in subqueries.
9. If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.
10. Column names should not consist of space and special symbols. If the column name consists of multiple words use _ as a separator.
example: CUSTOMER_ORDER_DATE.
11. Use aliases when it improves readability. Give simple and meaningful alias.
12. Include the AS keyword for creating aliases, because this makes the code more readable.
SELECT
ord.column_1
FROM table_1 AS ord13. For the primary key column avoid the name such as id,row_no. A good idea is to combine id with the name of a table, for example emp_id, student_id, etc.