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

141–150 of 168 posts

Re: Writing more legible SQL

#141

Post by hiou »

sevensor wrote: post #135
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…

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.

As long as it does not deviate into getting really creative with the spec, keeping everything consistent is overrated in my opinion.

There are the exceptions of very widely used projects where contributors will only look at the code a couple times when making a change to be pushed upstream or where there are significantly junior developers that need a narrow scope of the language to help them with the learning curve and getting up to speed with the code base.

In my experience(which is very anecdotal I admit), the requirement for consistency is more about a particular personality trait that many developers have - the need for order and control - as opposed to a requirement for quality production code.

Re: Writing more legible SQL

#142

Post by saosebastiao »

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)

I use it (way more than a lot) and I love it, but we could do better.

1) It really needs algebraic data types (and pattern matching!). I refuse to use a general purpose language that doesn't have them, and I always end up struggling to represent them using SQL.

2) Null, and the subsequent binary-but-maybe-ternary logic that it imposes on everything is a terrible hazard. It needs Option types. I guess this goes along with algebraic data types, but it is important enough to merit its own mention.

3) It needs more expressive constraints: foreign keys on partial unique indexes or views, declarable immutability, window constraints, etc.

4) It needs better graph representation and queryability. Anything remotely resembling a graph (or even trees!) ends up as a huge incomprehensible hack. Graphs are still relational, they're just ignored in favor of constructs that only tenably represent them.

5) It needs better ways to present output. Dumping everything as a table leaves the end user to their various hacky devices if what they really want is not an array of flat data structures. Users should be able to easily output data as a graph, tree, arbitrarily nested structs, associative maps, sets, arrays, and even scalars.

6) It needs extensive compile time checking of queries and statements, complete with errors and warnings and lints. As it stands right now, you only get syntax validation and checks for object existence. And although this is implementation level and not language level, SQL engines need to provide way better IDEs to compliment the better compilation.

7) It needs phantom keys for multi-column foreign keys against multi-column primary-keyed tables. As it stands right now, I have to lose the semantic benefits of a natural key in favor of a surrogate key in order to not duplicate a large number of columns in a 1-many table relationship. I should be able to use the many-columned natural foreign key, but invisible and behind the scenes map to a surrogate primary key.

Re: Writing more legible SQL

#143

Post by irrational »

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 prefer putting the comma at the beginning of the line (this makes dynamic sql much easier). I prefer putting and/or at the beginning of the line. I indent multiple items like case statements in the select. I use parens for join statements with multiple join conditions. I separate the select/from/where clauses with a blank line when a section has more than one line.

select

t1.col1

, t2.col2

, t3.col3

, (case when t1.col1 = 'Y'

   then '1'

   else '0'

   end) as the_flag
, t3.col2

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

, col1

limit 100

Re: Writing more legible SQL

#144

Post by howItsDone123 »

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…

I use this style. I work with queries that are a little bigger than average, and it's very difficult to keep track of it unless it's well laid out.

conditionals, loops, etc, get formatted like they would in a procedural language. the DML gets formatted similarly to what you've posted.

a not unrealistic example from this morning is as follows:

  if (select object_id('aTableName', 'u')) is not null begin
    select column
      from aTableName
     where thisIsTrue = 1
           and somethingIs in (select aList from aListTable)
           and somethingElse = (
             select aValue
               from aValueTable
              where aValue = 'somethingImportant'
           )
  end else begin
    print('Oh noes! I couldn't find somethingImportant or something in someList form aTable!')
  end
we work with huge amounts of data with systems and processes that don't lend themselves to other methods (yet... it's coming.) so we have to make SQL as readable as possible.

edit: yes... i know... subqueries are bad... it's just an example. those would have been done in joins or as temporary variables or something else.

Re: Writing more legible SQL

#145

Post by irrational »

__jal wrote: post #86
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…

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 used to agree with you about putting commas at the beginning of lines, but now I've changed my mind. By putting the commas at the beginning of lines it becomes much easier to do dynamic sql and to be able to add remove select statements without having to check the ends of the lines to make sure the last line does not have a trailing comma and to make sure the preceding lines each does have the requisite comma.

Re: Writing more legible SQL

#146

Post by kentosi »

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…

Yes! All the time, but with some variation. Let me explain by rewriting your query:

    select t1.col1,
           t2.col2,
           t3.col3
      from table1 t1
     inner join table2 t2 on t1.col2 = t2.col1
     inner join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else
     where 1 = 1
       and t1.col1 > 0 
       and t2.col2  t1.col4
     order by col2
     limit 100
My logic is that I: 1 - There should be a clean vertical line between the select/from/inner/where/etc keyboards to the right hand side for clean alignment. 2 - I always use the where 1 = 1 syntax so that I can easily comment out the following conditions while testing.

And thanks for not capitalising the keywords :-)

Re: Writing more legible SQL

#147

Post by 1wd »

83457 wrote: post #93
1wd wrote: post #73

Earlier quoted context omitted.

If "joins" are operators like "and", and you put "and" at the end of the line, shouldn't you put "join" at the end too? from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else

well I don't put and/or at the end of lines so there is that... but I have seen people but joins at the end of lines

sorry, mistook you for the top-level poster.

Re: Writing more legible SQL

#148

Post by bigato »

wvenable wrote: post #121
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.

Exactly! In some cases like in recursive queries, CTEs may be the only way to achieve some result. In almost all other cases, people use them just because they apparently can't reason about joins, or don't like to do so. I frequently have to deal with SQL that looks like the person was writing an imperative algorithm.

Re: Writing more legible SQL

#149

Post by combatentropy »

rubyfan wrote: post #67
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…

Very similar to my style, though I rarely use and instead mostly use != Never realized I could do tuple compare and will probably adopt that.

  >> prefer tuple comparisons of multi-comparisons,
  >> i.e. "(a,b)=(c,d)" instead of "a=c and b=d"

    > never realized I could do tuple compare and
    > will probably adopt that.
Indeed. Mind blown.

Re: Writing more legible SQL

#150

Post by irrational »

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.

Think of the leading commas as being the same as leading AND/OR in a where clause or a leading JOIN in a join clause. I used to prefer the commas after the column names as well, but I've since been converted to the wisdom of placing them to the front. It does take a little while to get used to it, but now I visually prefer it and find that I can read my SQL and find any potential issues much faster. Plus its much better for dynamic SQL and refactoring SQL statements.
Post reply on HN