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

111–120 of 168 posts

Re: Writing more legible SQL

#111

Post by ckastner »

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…

Where did you pick up this style?

I have been following this pattern for over a decade. I believe I picked it up from some project's style guide, but I never found it again.

Re: Writing more legible SQL

#112

Post by snarfy »

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…

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 ","

Re: Writing more legible SQL

#113

Post by bigato »

blowski wrote: post #92
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…

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.

Re: Writing more legible SQL

#114

Post by marcosdumay »

harterrt wrote: post #107
junto wrote: post #105

My pet hate, directly out of SQL Server Management Studio, is how their code generation places commas. It generates code like this: select columnA ,columnB ,columnC from tableName Whereas I want my commas AFTER the column names on the same line: select columnA, columnB, columnC from tableName Or just looks so much neater.

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, ...

Allowing commas on the last list item makes it easier to refactor.

Leading commas just shift the problem around, making the beginning of the list hard to change, instead of the end.

Re: Writing more legible SQL

#115

Post by akavi »

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 referencing table and row instead of "r.table('table name').row('row name')" and support for operator literals so you can write ">" instead of ".gt" or similar.).

Re: Writing more legible SQL

#116

Post by msluyter »

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 and grew to prefer it. It doesn't really hold up for extremely complex queries with lots of subqueries because everything tends to start drifting too far to the right (unrealistic example below):

     select *
       from blah b
      where b.whatever in (
          select blah 
            from c
           where c.whatever = in (
               select blah 
                 from d
                where d.whatever = 'foo'
           )
      )
I almost never see this style used these days, and I was curious if anyone else does it this way?

Re: Writing more legible SQL

#117

Post by marcosdumay »

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)

Hum.. SQL is a "mathematically pure" language, like Lisp or, to a smaller extent, Haskell. Those tend to not attract criticism (mostly because they age slowly, but also because the most vocal language critics are biased towards them).

SQL is also a standardized language. People tend to see those as something you should deal with, not something you should improve. Thus less criticism.

It's also a language that is mostly embedded inside others. So any weakness is easy to compensate on the outer code.

Besides this, it is a pretty well designed language.

Re: Writing more legible SQL

#118

Post by jessemerriman »

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/

Re: Writing more legible SQL

#119

Post by wvenable »

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…

C# has Linq

Re: Writing more legible SQL

#120

Post by mosselman »

mosselman wrote: post #38

This is what is wrong with 'best practices' `SELECT foo, bar FROM baz` Is not at all more legible than: `SELECT foo, bar FROM baz` On first glance I even missed the 'bar' column completely and just saw it when compressing this line. As things get longer it gets more important to make it legible, but saying that my first example is better than the second is just nonsense. Don't worry so much about what you should or s…

Haha woops, multiline did not work.

The first example is supposed to read:

    SELECT foo, 
        bar 
    FROM baz
Post reply on HN