Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Writing more legible SQL

craigkerstiens.com

Post reply on HN
Print view View original

131–140 of 168 posts

Re: Writing more legible SQL

#131

Post by djur »

ckastner wrote: post #109
harterrt wrote: post #107

Earlier quoted context omitted.

Leading with commas makes it easier to refactor if you're prototyping a query. You're less likely to cause an error when removing a column from: SELECT first , second , third ... than from SELECT first, second, third ... Which becomes: SELECT first, second, ...

At best, that may shave off a few seconds of the time needed to refactor. The trade-off is code that is harder to mentally parse, because we are used to trailing commas, not leading commas. If you spend more time reading code than writing it (which I assume applies to the majority of development), then the trailing comma is a much, much better choice of style.

> we are used to trailing commas

Leading commas and other operators are common in some languages. Haskell, for example:

https://github.com/tibbe/haskell-style-guide/blob/master/has...

Re: Writing more legible SQL

#132

Post by mamcx »

wiz21c wrote: post #44

Dunno if it's the quality of the posts here, but I find interesting to see that when we talk about SQL nobody complains about it : nobody says there are better alternative, nobody talks about the great schism between the last 2 major revisions of the SQL standard,... (IMHO, SQL is one of the oldest languages and it is still super powerful and used like hell in production environment, but that's just a point of view)

The main trouble is that even if a better alternative is suggested, how convince the RDBMS developers to use it?

Unfortunately, RDBMS are highly coupled. I remember when someday I ask if is possible to remove the SQL part of sqlite and substitute with my own, and get laughed!

RDBMS are "expected" to be a black box enclosed in dark mystery.

Re: Writing more legible SQL

#133

Post by perlgeek »

msluyter wrote: post #116

A couple of jobs ago, I worked at a company that did a ton of SQL and we used right justified keywords, which looks like: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 Initially it seemed weird to see the ragged left edge, but over time I got used to it a…

We also use right-justified keywords (and never "select *", though I guess that was just to keep your example brief).

Re: Writing more legible SQL

#134

Post by jenkstom »

I am a visual thinker. The method of listing columns as rows (i.e., the one line per column format) is too annoying to focus on the underlying code. Splitting a list is something like pagination or even regular text, so I suppose my mind just compensates for that without my thinking about it.

But I just can't do the columns as rows thing. I feel a little bit out in the cold because of it, but I'll just keep reformatting everybody else's code so I can read it. :-)

Re: Writing more legible SQL

#135

Post by sevensor »

vog wrote: post #33
combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

I fully agree with the sentiment, and especially with treating SQL code as code, because it really is code. My style differs in 4 regards: 1. "join"s are at the same level as "from", and the contents of from/join are indented 2. prefer tuple comparisons of multi-comparisons, i.e. "(a,b)=(c,d)" instead of "a=c and b=d" 3. operators auch as "and" are at the beginning of a line, as in style guide of almost all other pro…

Regardless of the particulars, I find the most important thing for readability is for the author to have made some kind of decision and stuck to it. The most illegible style is no style at all.

Re: Writing more legible SQL

#136

Post by blowski »

bigato wrote: post #126
blowski wrote: post #123

Earlier quoted context omitted.

It wouldn't improve performance, but it might improve readability without denigrating performance. I don't have much experience with CTEs, but I do have lots of experience trying to debug unreadable SQL.

> Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. > It wouldn't improve performance, but it might improve readability without denigrating performance. These two statements of yours are negating each other, unless people started using "performant" as synonymous for readable now.

OK, a clarification.

My first attempt at a query is readable but performance is terrible. I fix the performance problem, but the query is now much less readable. I use a CTE as a compromise between performance and readability.

Re: Writing more legible SQL

#138

Post by Ntrails »

ckastner wrote: post #109
harterrt wrote: post #107

Earlier quoted context omitted.

Leading with commas makes it easier to refactor if you're prototyping a query. You're less likely to cause an error when removing a column from: SELECT first , second , third ... than from SELECT first, second, third ... Which becomes: SELECT first, second, ...

At best, that may shave off a few seconds of the time needed to refactor. The trade-off is code that is harder to mentally parse, because we are used to trailing commas, not leading commas. If you spend more time reading code than writing it (which I assume applies to the majority of development), then the trailing comma is a much, much better choice of style.

I spend a lot of time writing ad hoc queries for analysis. That means I don't know what columns I want, I might be switching things up from aggregates to subsets and back. That's why you'll find things like:

    WHERE   1=1
    AND     a.id = 12
    --AND     a.Col1 > 23
    AND     a.Col1 = 23
Similarly the leading comma makes it faster to cut out things I'm not using anymore. Now obviously for production code you could argue my reasons are no longer valid - but probably they'll leak through because that's what all my shk hotkeys generate and I'm used to writing

Re: Writing more legible SQL

#139

Post by iblaine »

I prefer any formatting over no formatting. If had to choose then I would do this:

* Lowercase for tables & columns and upper case for syntax.

* Having commas first makes it easier to move lines around.

* I avoid small aliases to minimize conflicts when code is resused.

* Every column needs a table reference. Do not make me guess.

* Alias every column in the SELECT clause.

    SELECT
        table1.col1 AS table_col1
      , table2.col2 AS table_col2
      , table3.col3 AS table_col3
    FROM
        table1 table1
    INNER JOIN
        table2 table2
    ON
        table1.col2 = table2.col1
    INNER JOIN
        table3 table3
    ON
        table1.col3 = table3.col1
    AND table3.col2 = something_else
    WHERE
        table1.col1 > 0
    AND table2.col2  table1.col4
    ORDER BY
        table2.col2
    LIMIT 100;

Re: Writing more legible SQL

#140

Post by 20years »

msluyter wrote: post #116

A couple of jobs ago, I worked at a company that did a ton of SQL and we used right justified keywords, which looks like: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 Initially it seemed weird to see the ragged left edge, but over time I got used to it a…

This is the style I use other than I uppercase the reserved words.
Post reply on HN