Today I want to share another little SQL Style thing. This is really not a big deal, but it has a lot of value when you are debugging things. This style technique is to place aggregated clauses with all the additional criteria on separate lines when writing your SQL Statement. I’ll give you an example, and then tell you how it can be useful.
SELECT
t1.Column1 AS T1Col1
,t1.Column2 AS T2Col2
,t2.Column2
,t2.Column3
,t2.Column2 + t2.Column3 as Column4
FROM Table1 t1
JOIN Table2 t2 ON t1.Column1 = T2.Column1
As you can see, the comma delimiting the different columns in the select statement precedes each individual column line. Also, all of the join information of table1 to Table2 is on the same line. This is a simple query, so there is not a lot of value here. But, when you have a lot of columns and tables in a query, when you align you syntax in this format, it is easy to comment out portions of the query. You can simply comment out any line you wish to exclude by using the SQL — comment syntax.
If that doesn’t make sense, let me give you an example the way SQL is traditionally written, even by database tools.
SELECT
t1.Column1 AS T1Col1,
t1.Column2 AS T2Col2,
t2.Column2,
t2.Column3,
t2.Column2 + t2.Column3 as Column4
FROM Table1 t1
JOIN Table2 t2 ON t1.Column1 = T2.Column1
Using this form of the query, if I want to exclude Column4 from the query I have to remove the comma after t2.Column3, and then comment out the next line that creates Column4. Using the previous form I can simply comment out the Column4 line of code, without touching the previous line.
You can do the same thing on your join clause. If there are multiple lines in the join, or if there are a lot of columns in the select, or even a where clause, you can comment them out using a multi-line comment as well. The point is to keep each line autonomous, so that if you wish to exclude it from your query, you can easily comment out a portion, and later remove the comment, allowing you to exercise the query in different forms.
I use this same form when defining tables, constraints, and other things like that, because you can easily remove small sections without having to maintain multiple lines.
Cheers,
Ben