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 :-)