17 Best practices for writing optimized SQL queries

 Follow 17 Best practices for writing optimized SQL queries:

1. Use EXISTS in place of IN wherever possible

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

3. Use groupby instead of distinct.

4. Add useful comments wherever you write complex logic and avoid too many comments.
 
5. Use joins instead of subqueries when possible for better performance.

6. Use WHERE instead of HAVING to define filters on non-aggregate fields
 
7. Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results)
 
8. Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list)
 
9. Write SQL keywords in capital letters.
 
10. Never use select *, always mention list of columns in select clause.

11.  Create CTEs instead of multiple sub queries , it will make your query easy to read.
 
12. Join tables using JOIN keywords instead of writing join condition in where clause for better readability.
 
13. Never use order by in sub queries , It will unnecessary increase runtime.

14. If you know there are no duplicates in 2 tables, use UNION ALL instead of UNION for better performance
 
15. Always start WHERE clause with 1 = 1.This has the advantage of easily commenting out conditions during debugging a query.
 
16. Taking care of NULL values before using equality or comparisons operators. Applying window functions. Filtering the query before joining and having clause.
 
17. Make sure the JOIN conditions among two table Join are either keys or Indexed attribute.

Comment down below your thoughts if you want to add more points.