Summary

The web content outlines essential SQL query writing practices to enhance code readability, performance, and team collaboration.

Abstract

The article "Best Practices for Writing SQL Queries" emphasizes the importance of adhering to a set of guidelines when writing SQL to ensure clarity and efficiency. It suggests using uppercase letters for SQL keywords, employing table aliases for queries involving multiple tables, and explicitly listing columns in the SELECT clause instead of using asterisks. The author advises adding meaningful comments, using joins over subqueries for performance gains, and creating Common Table Expressions (CTEs) to improve query readability. Other recommendations include optimizing the GROUP BY clause by considering cardinality, preferring EXISTS over IN, and avoiding ORDER BY in subqueries. The article also advocates for using UNION ALL when duplicates are not a concern, employing WHERE instead of HAVING for non-aggregate filters, and steering clear of leading wildcards in predicates to prevent full table scans. The author encourages readers to adopt these practices to maintain code professionalism and facilitate understanding among team members.

Opinions

The author believes that writing SQL keywords in uppercase enhances the professional appearance and readability of the code.
They suggest that using table aliases and explicitly naming columns in the SELECT clause are crucial for query clarity, especially in multi-table joins.
Comments are deemed necessary for complex logic but should be used judiciously to avoid clutter.
The author posits that joins and CTEs are preferable to subqueries for both readability and performance.
Considering the order of columns in the GROUP BY clause based on uniqueness can lead to performance improvements.
The use of EXISTS is encouraged over IN for better performance in certain scenarios.
The article expresses a clear preference for using JOIN keywords over join conditions in the WHERE clause for better readability.
The author advises against using ORDER BY in subqueries, as it can lead to unnecessary performance overhead.
UNION ALL should be chosen over UNION when dealing with distinct datasets to enhance performance.
The author recommends using WHERE clauses for filtering non-aggregate fields instead of HAVING, which is more appropriate for aggregate functions.
Leading wildcards in predicates are discouraged due to their tendency to cause full table scans, which are resource-intensive.

Best Practices for Writing SQL Queries

Make your team happy once in a while

Dear reader, stay at least 30 seconds. Don’t destroy writers’ read ratio :)

It’s always easy to mess with SQL without proper guidelines.

Everyone has their habits to write SQL queries, just like any coding language you can end up confusing other team members so they cannot understand.

So, slowly and steadily people realize the the importance of following a set of good practices.

Below are some practices you can follow to make yourself better.

1. Write SQL keywords in capital letters. Writing SQL queries in capital, makes your code look more professional, clean, and readable.

2. Use table aliases with columns when you are joining multiple tables.

3. Never use select *, always mention the list of columns in the select clause. Using an asterisk sign in a query causes redundant consumption of the database engine’s resources because it will retrieve all table columns.

In particular, using SELECT * provokes consuming more network and disk resources.

4. Add useful comments wherever you write complex logic. But avoid too many comments.

5. Use joins instead of subqueries when possible for better performance.

6. Create CTEs instead of multiple sub-queries, it will make your query easy to read

7. Considering cardinality within GROUP BY can make it faster (try to consider a unique column first in the group by list)

8. Use EXISTS in place of IN wherever possible

9. Join tables using JOIN keywords instead of writing join condition in where clause for better readability.

10. Never use order by in sub queries, It will unnecessarily increase runtime.

11. If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance.

12. Use WHERE instead of HAVING to define filters on non-aggregate fields

13. Avoid wildcards at the beginning of predicates (something like ‘%abc’ will cause a full table scan to get the results)

Anything else you want to add...💬

You can Subscribe to me here for more such content. P.P.S.S. Put a ❤️ in the comments if this content resonates with you.

Cheers!