How I format SQL code(bytepawn.com) |
How I format SQL code(bytepawn.com) |
Not when it's embedded as a string in another language, like when the query you want is not supported by the ORM.
> Lowercase is objectively more readable
No, and definitely not objectively. I generally don't capitalize my SQL, but I can't argue that using lowercase exclusively makes the SQL more readable. It definitely does help readability to differentiate SQL keywords from table and column names. Compare:
select
region_fleet,
case when status = 'delivered' then 'delivered' else 'not delivered' end as status,
date_trunc('week', day) as week,
count(distinct row(day, so_number)) as num_orders,
count(distinct case when scheduled_accuracy_meters <= 500 then row(day, so_number) else null end) as num_accurate,
avg(scheduled_accuracy_meters) as scheduled_accuracy_meters
from
deliveries
where
...
group by
1, 2, 3
SELECT
region_fleet,
CASE WHEN status = 'Delivered' THEN 'Delivered' ELSE 'Not Delivered' END AS status,
DATE_TRUNC('week', day) AS week,
COUNT(DISTINCT ROW(day, so_number)) AS num_orders,
COUNT(DISTINCT CASE WHEN scheduled_accuracy_meters <= 500 THEN ROW(day, so_number) ELSE NULL END) AS num_accurate,
AVG(scheduled_accuracy_meters) AS scheduled_accuracy_meters
FROM
deliveries
WHERE
...
GROUP BY
1, 2, 3
Lowercase letters are read and comprehended faster: https://ux.stackexchange.com/questions/72622/how-easy-to-rea...
Additionally using casing when it has no meaning is an anti-pattern.
Select
region_fleet,
Case When status = 'Delivered' Then 'Delivered' Else 'Not Delivered' End As status,
Date_Trunc('week', day) As week,
Count(Distinct Row(day, so_number)) As num_orders,
Count(Distinct Case When scheduled_accuracy_meters <= 500 Then Row(day, so_number) Else Null End) As num_accurate,
Avg(scheduled_accuracy_meters) As scheduled_accuracy_meters
From
deliveries
Where
...
Group By
1, 2, 3You absolutely cannot rely on using your application of choice for editing queries: formatting them aggressively and allowing transmittable demarcations of query structure (like case) makes for the most portable, universally readable queries.
Given how many different systems we use these days to develop a single application, upper-casing is more necessary than ever in the 2020s.
Lowercase + indentation is the way to go. The only SQL that should be upper case is text in single quotes.
It's a damn shame that the NoSQL movement turned out to just be NoRelational. RDBMS's could use a non-terrible query language.
WHERE
country = 'UAE'
AND day >= DATE('2019-07-01')
AND DAY_OF_WEEK(day) != 5
AND scheduled_accuracy_meters <= 10*1000
WHERE country = 'UAE'
AND day >= DATE('2019-07-01')
AND DAY_OF_WEEK(day) != 5
AND scheduled_accuracy_meters <= 10*1000 WHERE 1=1
AND country = 'UAE'
AND day >= DATE('2019-07-01')
AND DAY_OF_WEEK(day) != 5
AND scheduled_accuracy_meters <= 10*1000
How is this:
FROM
tablename t
INNER JOIN
other_table ot
ON
t.id = ot.id
FROM tablename t
INNER JOIN other_table ot
ON t.id = ot.id
FROM foo
INNER JOIN
other_table using (other_table_id)
FROM foo
INNER JOIN
+ foo_bars using (foo_id),
other_table using (other_table_id) select 'biscuit'
where
(
(
@alpha
<
pow(
sin(
radians(
@scheduled_lat - @actual_lat
)
/ 2
)
, 2
)
)
and
@alpha > 0
) SELECT
col1
,col2
,col3
The point about ease of adding a new column is absolutely valid.
The best answer to it, subjectively and IMHO, is on the language level, e.g. making it legal to end the statement with a comma:
SELECT col1, col2, col3, from ...
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)
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.nameMultiple spaces after `LEFT` in `LEFT JOIN`? Just to stick with "river"-style alignment, yet your outer-level keywords (`SELECT`, `FROM`, etc.) aren't aligned?
It's difficult to understand why one would pick this format.
EDIT: feel free to show me something better..
P.S. Can I suggest you put your name somewhere in your header?
P.P.S. I see you, too, use 'self' when taking notes. Would you also be a Pythonista? :)
Implementing production queries, you tend to write out the full column names, but for 95% of your SQL this is a boon to the analyst or data scientist.
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 :-)
`INNER JOIN` goes fully on the right side of the river.
The guide at https://www.sqlstyle.guide/ is almost perfect.
> Additionally using casing when it has no meaning is an anti-pattern.
Why do you say that it has no meaning? This is about differentiating SQL keywords from table and column identifiers. That's the meaning.
> The first is actually much easier to read, and lower case is far superior.
Reading the query whole, sure, but are you seriously suggesting that you can skim for the identifiers faster in the all-lowercase one when there are no color hints?
One could also type like `CoUnT(dIsTiNcT CaSe WhEn ... EnD)` to make the keywords stand out. Casing has absolutely no meaning in SQL outside of single quotes.
On the other hand, (in python for example) SOME_FUNCTION() and some_function() both have meaning. They do not refer to the same thing.
>but are you seriously suggesting that you can skim for the identifiers faster in the all-lowercase one when there are no color hints?
Yes. Especially since my eyes don't get stuck on the massive blobs of ALL CAPS YELLING TEXT in the select.
That the language doesn't enforce a meaning doesn't mean that we can't add meaning to the casing. This is like how in multiple languages it's convention to write constant variables in all-caps even though few languages enforce it.
select region_fleet, case when status = 'delivered' then 'delivered' else 'not delivered' end as status, date_trunc('week', day) as week, count(distinct row(day, so_number)) as num_orders, count(distinct case when scheduled_accuracy_meters <= 500 then row(day, so_number) else null end) as num_accurate, avg(scheduled_accuracy_meters) as scheduled_accuracy_meters from deliveries where ... group by 1, 2, 3
vs.:
SELECT region_fleet, CASE WHEN status = 'Delivered' THEN 'Delivered' ELSE 'Not Delivered' END AS status, DATE_TRUNC('week', day) AS week, COUNT(DISTINCT ROW(day, so_number)) AS num_orders, COUNT(DISTINCT CASE WHEN scheduled_accuracy_meters <= 500 THEN ROW(day, so_number) ELSE NULL END) AS num_accurate, AVG(scheduled_accuracy_meters) AS scheduled_accuracy_meters FROM deliveries WHERE ... GROUP BY 1, 2, 3
... because a lot of time, when these nicely-formatted statements get parsed, the whitespace gets condensed, and when it gets spit out in an error message, I for one would like the all-caps keywords to be landmarks to direct my eye.
Maybe it's just me, or maybe it's a matter of habit, but for me the changes from lowercase to uppercase and back are a kind of hurdle that make my brain pause a moment. The second example is much more cumbersome to me to read and parse.
When an identifier is written in all-caps, it generally _____ that it's a constant.
I feel like you're just, in bad faith, refusing to acknowledge that the word "meaning" doesn't have to be in any way related to language enforcement. Are you trying to argue for the sake of arguing?