4 ms·

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

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. https://github.com/mattm/sql-style-guide/blob/master/README.md https://github.com/mattm/sql-style-guide/blob/master/README....

silveroriole 7y ago
“It's just as readable as uppercase SQL and you won't have to constantly be holding down a shift key.” If only there was some way not to hold shift, some kind of key that locks your case...!
jolmg 7y ago
> editors colorize keywords so they stand out 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 with 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 It makes the column names stand out when you lack color hints. You can quickly skim to see what data is involved in a query without visually parsing the expressions.
- arethuza 7y ago
  For what it is worth, I find the first of those (i.e. the lowercase one) much easier to read.
  - amp108 7y ago
    Yeah, but how about this: 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.
    - roelschroeven 7y ago
      I still find the first one more readable. 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.
      - eukaryote 7y ago
        Same for me - I find the use of upper case keywords like this jarring in SQL. It seems quite old-school, like reading COBOL!
        arethuza 7y ago
oarabbus_ 7y ago
I lowercase everything in my SQL. Lowercase + indentation is the way to go. The only SQL that should be upper case is text in single quotes.
strbean 7y ago
Everything about SQL is a holdover from ancient programming languages. From crappy 'natural sounding' syntax constructs ("SELECT ... FROM ... WHERE ...") to outright inconsistencies ("INSERT INTO table (column1, column2) VALUES (1, 2)" vs "UPDATE table SET column1=1, column2=2"). It's a damn shame that the NoSQL movement turned out to just be NoRelational. RDBMS's could use a non-terrible query language.
lazzlazzlazz 7y ago
Having written many many thousands of SQL queries, and having begun thinking that lower-casing keywords was better, I can tell you this is completely false. You 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.

silveroriole 7y ago

“It's just as readable as uppercase SQL and you won't have to constantly be holding down a shift key.” If only there was some way not to hold shift, some kind of key that locks your case...!

jolmg 7y ago

> editors colorize keywords so they stand out 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 with 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 It makes the column names stand out when you lack color hints. You can quickly skim to see what data is involved in a query without visually parsing the expressions.

arethuza 7y ago
For what it is worth, I find the first of those (i.e. the lowercase one) much easier to read.
- amp108 7y ago
  Yeah, but how about this: 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.
  - roelschroeven 7y ago
    I still find the first one more readable. 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.
    - eukaryote 7y ago
      Same for me - I find the use of upper case keywords like this jarring in SQL. It seems quite old-school, like reading COBOL!
      - arethuza 7y ago

oarabbus_ 7y ago

I lowercase everything in my SQL. Lowercase + indentation is the way to go. The only SQL that should be upper case is text in single quotes.

strbean 7y ago

Everything about SQL is a holdover from ancient programming languages. From crappy 'natural sounding' syntax constructs ("SELECT ... FROM ... WHERE ...") to outright inconsistencies ("INSERT INTO table (column1, column2) VALUES (1, 2)" vs "UPDATE table SET column1=1, column2=2"). It's a damn shame that the NoSQL movement turned out to just be NoRelational. RDBMS's could use a non-terrible query language.

lazzlazzlazz 7y ago

Having written many many thousands of SQL queries, and having begun thinking that lower-casing keywords was better, I can tell you this is completely false. You 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.