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

121–130 of 168 posts

Re: Writing more legible SQL

#121

Post by wvenable »

bigato wrote: post #80

> CTEs >First, yes they can be an optimisation boundary. But they can also make your query much more read-able and prevent you from doing the wrong thing because you couldn’t reason about a query. In my experience, CTEs may be simpler for the writer to reason about, but they make the queries complicated for the reader to understand. If you use a CTE because you find the problem too complicated, chances are that you a…

CTE's are necessary for some types of queries but I've seen developers use them where a simple join would have worked. It can add a lot of unnecessary noise.

Re: Writing more legible SQL

#122

Post by vog »

snarfy wrote: post #112
vog wrote: post #33

Earlier quoted context omitted.

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…

For item 3, the benefit of "and" at the beginning is that you can add or remove lines to the condition without disturbing the previous/next lines. The same works for ","

But the disadvantage is that you don't immediately see whether it is "and", "or" or something else.

That's why in most programming languages the coding style says you should put the operators at start of line instead of end of line, e.g.

GOOD:

    if ((...some_condition_that_may_make_sense...)
        && (...some_other_condition...)
        && (...and_yet_some_condition_that_makes_sense...))

    x = ((some expresssion)
         + (some other expression of different length)
         - (some final expression))
BAD:

    if ((...some_condition_that_may_make_sense...) &&
        (...some_other_condition...) &&
        (...and_yet_some_condition_that_makes_sense...))

    x = ((some expresssion) +
         (some other expression of different length) -
         (some final expression))
Examples:

- Python PEP-0008: "Should a line break before or after a binary operator?" https://www.python.org/dev/peps/pep-0008/#should-a-line-brea...

- Topvoted SO answer for "If you break long code lines, how do you indent the stuff on the next line?" http://stackoverflow.com/a/699347

Re: Writing more legible SQL

#123

Post by blowski »

bigato wrote: post #113
blowski wrote: post #92

Earlier quoted context omitted.

Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. Anything that can help to write and maintain big ugly queries without a cost in performance is welcome.

Care to elaborate on what situation would a CTE improve performance? Because the OP itself mentioned CTEs degrading performance.

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.

Re: Writing more legible SQL

#124

Post by cwyers »

cptskippy wrote: post #99
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…

If I came across your SQL, I'd probably hunt you down and give you a hug. There's so much horridly formatted code and it's unusual to see a developer care. I prefer to uppercase command syntax to make stand apart visually from the parameters of the query. I don't agree with your conjunctions at the end of the line, I actually prefer commas at the beginning of the next line though I don't do that so as to conform to c…

That's pretty close to how I do it. I started putting commas at the beginning of a line because when I started doing so I stopped ever having queries error out because I copy and pasted something from one query to another and forgot to remove a trailing comma. Having the commas at the front of the line makes finding those kinds of errors automatic for me.

Re: Writing more legible SQL

#125

Post by akavi »

jessemerriman wrote: post #118
akavi wrote: post #115

Only tangentially related, but this reminds me of something I've wanted for a while: Are there any languages for relational queries, but with improved syntax (composability, epxression capture, static typing?) and which "compile" down to SQL? Ideally, I imagine something that looks something like RethinkDB's ReQL, but DSL-ed up a bit beyond what's possible when it's "just" a JS library (eg, a nicer syntax for referen…

There's Alf, which has a functional, pipeline-style, though I don't know if it's still active: http://www.try-alf.org/blog/

Sweet, Alf seems to be exactly what I wanted!

Shame it no longer seems to be under active development, but it seems to be pretty mature?

Re: Writing more legible SQL

#126

Post by bigato »

blowski wrote: post #123
bigato wrote: post #113

Earlier quoted context omitted.

Care to elaborate on what situation would a CTE improve performance? Because the OP itself mentioned CTEs degrading performance.

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.

Re: Writing more legible SQL

#127

Post by __jal »

gbog wrote: post #108
__jal wrote: post #86

Earlier quoted context omitted.

Yep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps. Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.

I think "rivers" are not the same. if the same as in edition of books in French, rivers are those unwanted "paths" your eye sees floating downwards inside the rectangle of words that makes a page. They come out of randomness, when whitespaces happen to draw a negative path. It has to be corrected by slightly modifying the whitespaces between some words. AFAIK this is done manually and is one reason for book edition t…

When I worked in magazine production (90s, California) we used the term for both whitespace and other patterns. Other patterns are a lot less common, because whitespace is the most common "character", but they happen.

Re: Writing more legible SQL

#128

Post by __jal »

dancek wrote: post #110
__jal wrote: post #94

Earlier quoted context omitted.

ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of…

> Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. PostgreSQL distributions come with support for PL/Python, PL/Tcl, PL/Perl and PL/pgSQL (a PostgreSQL specific PL/SQL clone). Other languages exist, but aren't included. See https://www.postgresql.org/docs/9.6/static/external-pl.html

Yes. And PL/Perl is perversely fun. I don't think I've ever seen other languages used in production, though[1]; I think folks tend to stick to PLSQL because of defaults.

[1] I'm sure people do, but this discussion has an anecdata deficiency.

Re: Writing more legible SQL

#129

Post by sbuttgereit »

cptskippy wrote: post #99
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…

If I came across your SQL, I'd probably hunt you down and give you a hug. There's so much horridly formatted code and it's unusual to see a developer care. I prefer to uppercase command syntax to make stand apart visually from the parameters of the query. I don't agree with your conjunctions at the end of the line, I actually prefer commas at the beginning of the next line though I don't do that so as to conform to c…

Very much like this style, very close to what I do... really the only difference between your style and mine is that I will indent the joins. For me it makes it somewhat easier to be able to scan for the where clause. Also I'll typically not use the AS keyword for table aliasing, no real good reason for that choice other than many years of habit.

But mostly commas first and logical operators and such first: If I have complicated queries that I'm working through... having the ability to just comment bit out here and there without worrying too much if I've left a dangling comma (et al) is a good time saver.

Re: Writing more legible SQL

#130

Post by teej »

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 very close to the style we use. The only big difference is that we opt for CTEs over subqueries whenever possible.
Post reply on HN