Yes! I write SQL this way. I grant the comma thing is more a matter of taste, but aligning clauses vertically and maximizing rivers is super useful to me and it forever mystifies me that it isn't standard. This is an extreme example, but I like to keep join clauses vertically aligned as well:
select a
, b
, count(*) as count
from table
join table2 on table.key1 = table2.key1
and table.key2 = table2.key2
and DATEADD('day', 1, table.key3) = table2.key3
where cond1
and cond2
group by a, b
order by count desc
For me this lets me read the SQL query almost like a picture at first glance, identifying blocks of rivers as semantically connected groups of statements. Some SQL formatters out there love to put join clauses at the same indentation level as the join itself, drives me nuts.
This looks nearly the same, but it's much easier to write and generates less spurious diffs:
select a
, b
, count(*) as count
from table
join table2
on table.key1 = table2.key1
and table.key2 = table2.key2
and DATEADD('day, 1, table.key3) = table2.key3
where cond1
and cond2
group by a, b
order by count desc
The largest difference is that the equal operators aren't aligned anymore, but on my opinion, aligning them is really not worth the cost.
Nice changes, I like it! I will say I really like aligning the join clauses, but I'll try writing things this way for a little while and see if it sticks.
The only thing I require from an SQL formatter is to make typos and boolean mistakes super easy to spot, especially as it grows in size. At first glance it might seem strange, but I like indentation on even the first term of an AND.
SELECT
a,
b,
count(*) as count
FROM
table
JOIN
table2
ON
table.key1 = table2.key1
AND
table.key2 = table2.key2
AND
-- 1 day apart
DATEADD('day', 1, table.key3) = table2.key3
WHERE
cond1
AND
cond2
AND
(
cond3
OR
cond4
)
GROUP BY
(a, b)
ORDER BY
count desc,
a asc
LIMIT
1000
;
Yes, that's a lot of whitespace, but it helps for complex queries.
Do you format your code this way by hand or does your IDE help you? Also, I can only imagine how noisy your diffs would be when you make changes and have to indent everything.
I'll use PyCharm's SQL formatter sometimes to massage spaghetti code that someone else wrote, but mostly I just write it this way by hand.
I will say my use case is probably different than others, I'm a Data Scientist so I write a ton of throwaway SQL and stuff that will only be seen by me. If I'm dealing with an existing SQL codebase or production stuff, definitely try to keep it more easily diff-able (like left justifying the keywords, really only caring about rivers in the join/where clauses), or just follow whatever patterns are already there.
- it doesn't work for collaborative efforts unless everyone is using the same linter or IDE with the same configuration. Not friendly to edits outside of that ecosystem (e.g. open source).
- there are other ways of writing SQL (e.g. dbt style guide) which are still very readable while also being much faster to write by hand without the need for an auto-formatter.
- some of these patterns such as the from and join indentations have nothing to do with readability and are purely stylistic. Nothing wrong with that, but there's nothing objectively better about this pattern - it just comes down to how you're used to reading SQL.
> and it forever mystifies me that it isn't standard
I can take a stab at justifying that - commas are used to form lists (eg, 1, 2, 3) and text (like "order by count") are supposed to be left justified.
You may be right that there are good reasons to format SQL while ignoring the commas and rules for writing English text. I personally think you are right. But if most people believed that then there'd be enough support to rewrite SQL with a decent modern syntax. Say allowing training commas before a from.
So the existence of SQL is, in a weird way, evidence that your view is uncommon.
