If you need to understand a query, say, to fix a bug, you need to be able to look at it and quickly answer some questions:
- how many columns in the result set?
- what are their names?
- what tables are involved in the query?
- what's the join criteria?
So, to enhance understanding speed of comprehension, I like to make my SQL as tabular as possible, especially WRT to the
from clause and the
join criteria. In the select list, I like to use the
column-name = expression syntax as it makes things much more readable. I also omit keywords that are optional in the SQL grammar. There's no point in saying
left outer join' whenleft join
will do: there is no such thing as aleft inner join`.
This isn't very important for trivial queries: if your query is something on the lines of
select * from foo where foo_date between @lowBound and @hiBound
it doesn't matter much. But, the bigger and more complicated the queries get, the more important it all becomes. If your query runs to 400 lines long and involves 18 tables with nested subqueries, very complicated join criteria and assorted other ugliness, having well-formatted SQL is very important to your ability to understand the code. This is true even if you're the author of the code in question. When it comes to the ability to create "write once" code, Perl has nothing on SQL.
A lot of years of doing maintenance work on existing, sloppy code has led me to a form that looks something like this:
select column_name = <some-expression> ,
another_column_name = <another-expression> ,
from dbo.some_table t1
left join dbo.another_table t2 on t2.some_table_id = t1.id
join dbo.yet_another_table t3 on t3.id = t2.another_table_id
and exists ( select ...
join ( select ...
) t4 on t4.id = t3.widget_id
where t1.xxx = t4.yyy
and t2.zzz = ( select count(*)
Gerald Weinberg pointed out back in the early 1970s in his seminal work, The Pyschology of Computer Programming, that the most important language for a computer programmer to know is English, not [insert-the-programming-language-of-choice-here]: the writing and maintenance of computer programs is a social task (something one does with people: the computer is merely a tool). As far as actual source code goes, the compiler doesn't care what it looks like, so long as it parses correctly. However, the people who need to work with that source code do care: they need to be able to grok it.
The human mind likes order: whitespace and tabular column alignment are your friends.