One of the things I have found helpful is to be consistent on how your layout your SQL queries. Consistency is probably more important than any particular little option. If your queries are consistent throughout, then others looking at your code will be able to understand more quickly what is going on. In this area, I really do like having a basic SHOP standard for layout.
I don’t like standards just to force everyone to do the same thing the same way every time, without room for either personal preference or edge case optimizations. If one developer prefers to use a Common Table Expression (CTE) and another developer prefers to use an Inline view, the net result is the same.
But there are a few things I have found worthy of standardization. These are personal preferences you can assess as you see fit. I’ll provide my logic, knowing that it doesn’t work for everyone else.
I like to capitalize ALL of the SQL Commands and operation key words. This makes things stand out.
SELECT, FROM, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, ON, WHERE, IN, LIKE, OVER, PARTITION BY, ORDER BY, SUM, MAX, MIN, TOP, etc. If you capitalize all of these key words they tend to stick out as what they are, allowing the logic of your query to be distinguished through the lack of capitalization.
I like to left align keywords…
SELECT …
FROM …
JOIN …
WHERE ….
GROUP BY …
HAVING
All of the other contents are indented beneath the parent Key Word.
If I am selecting multiple columns I like to put each column on a separate line, preceding the column name with a comma. This way, if I want to comment out a column I can simply comment the single line, instead of having to impact at trailing comma from the previous line. This is VERY MUCH a personal preference. You can apply a similar construct to your where clause.
This tip will not make you a better programmer, or help you write better SQL. What you will gain is the ability to communicate your SQL Queries clearly to other people who have not been working with your code.
Cheers,
Ben