I appreciate write-ups like this, but I really disagree with what seems to be the majority that SQL keywords should be uppercase. It’s one of the last uppercase holdovers from the old days. HTML used to be uppercase as well. Lowercase is objectively more readable, easier to type, and editors colorize keywords so they stand out. Uppercase is really not necessary in the 2020s. Check out Matt Mazur’s styleguide (linked in the post) for an alternative that endorses lowercase. He also has a contrasting style on where Boolean operators should go. <a href="https://github.com/mattm/sql-style-guide/blob/master/README.md" rel="nofollow">https://github.com/mattm/sql-style-guide/blob/master/README....</a>
> God is merciful because AND_ is 4 characters, a good tab width, so WHERE conditions are to be lined up like (same for JOIN conditions)<p><pre><code> WHERE
country = 'UAE'
AND day >= DATE('2019-07-01')
AND DAY_OF_WEEK(day) != 5
AND scheduled_accuracy_meters <= 10*1000
</code></pre>
It looks better when you use a tab-width of 2:<p><pre><code> WHERE country = 'UAE'
AND day >= DATE('2019-07-01')
AND DAY_OF_WEEK(day) != 5
AND scheduled_accuracy_meters <= 10*1000</code></pre>
I don't see the benefit of putting table names on a different line than the keyword.<p>How is this:<p><pre><code> FROM
tablename t
INNER JOIN
other_table ot
ON
t.id = ot.id
</code></pre>
More readable than:<p><pre><code> FROM tablename t
INNER JOIN other_table ot
ON t.id = ot.id
</code></pre>
I agree with a lot of these recommendations, but this one irks me. Also I'd love if someone could create a nice code-formatter for SQL like Python's Black.
I also use his multi line format for boolean logic:<p><pre><code> select 'biscuit'
where
(
(
@alpha
<
pow(
sin(
radians(
@scheduled_lat - @actual_lat
)
/ 2
)
, 2
)
)
and
@alpha > 0
)</code></pre>
Great style guide in my opinion. It is actually rather helpful to have those
SQLs formatted neatly. As an analyst you have to write quite a few of them. So copy pasting and reusing is most helpful and boosts productivity. To make sure that you don’t make errors a clean layout for eyeballing is necessary. The same for bug fixing, should you have one planted still.
Personaly, I put the comma before the column name :<p><pre><code> SELECT
col1
,col2
,col3
</code></pre>
It's easier for me to add a column or move it like this.
Otherwise I have to search the comma when my query has only one column and I add one or when I add a column at the end
I struggle to find a beautifier doing something similar to this with indentation. I use quite a bit of plpgsql which makes it even more challenging. I have tried a few found in the www however none of them cut it. Any recommendations?
This is my favorite guide yet!<p>My syntax, like others, is a little different (lowercase, 2 spaces, commas-first, bracket quotes, ons right under joins w/ joined table on LHS, left joins left-aligned): (this query isn't supposed to make sense)<p><pre><code> select
u.id [user]
, u.email [email]
, o.name [office]
, sum(t.id) [# things]
from main_tblusers_db u
inner join tbloffices_db o
on o.id = u.office_id
inner join things_tbl t
on t.user_id = u.id
left join example e
on e.user_id = u.id
where
u.deleted is null
and (
u.active is not null
or u.special = 1
)
group by
u.id -- the 1, 2 syntax is new to me!
, u.email
, o.name</code></pre>
The rest of this man's blog is also worth a visit. Great work, Marton!<p>P.S. Can I suggest you put your name somewhere in your header?<p>P.P.S. I see you, too, use 'self' when taking notes. Would you also be a Pythonista? :)
It's a case of yet another standard (<a href="https://xkcd.com/927/" rel="nofollow">https://xkcd.com/927/</a>)<p>The author recommends using upper-case for all keywords while Matt Mazur's SQL style guide, that is linked at the bottom of the article, recommends using lowercase for keywords :-)