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

151–160 of 168 posts

Re: Writing more legible SQL

#151

Post by irrational »

falcolas wrote: post #98

One thing missing that I can't recommend more highly - a comment in the SQL clause indicating where in the code it is being called from. These comments are invaluable when debugging or doing performance tuning of queries, especially when you have a large codebase. i.e. SELECT * FROM students WHERE 1 /* somemodule.somemethod */ As for query formatting, as with everything, consistency in style is more important than a…

I agree it would be great to show where the code is being called from, but often we have queries that are called from dozens of different places and it would be unwieldy to add all of those. Plus, if one time a developer starts using a query somewhere in the code and forgets to add that place to the query, all of the sudden all places where the query is called from becomes suspect. I think it is better to give your query block a unique name or namespace that allows you to quickly search all of your code for where that name is used.

Re: Writing more legible SQL

#152

Post by T-hawk »

sundvor wrote: post #103
neves wrote: post #100

Earlier quoted context omitted.

You didn't mentioned the most important thing to make readable SQL and that is demonstrated in your clause: use the "join" keyword instead of doing the join in the "where" clause. I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.

My first thought was "why would you do that?". Glad to say I've rarely seen joins in the where clause.

This is what he means, and I see it all the time: SELECT FROM table1 JOIN table2 WHERE table1.field = table2.field

An unsettling number of SQL developers never actually learned join syntax and write it that way.

It's functionally equivalent to putting the equality in the JOIN ... ON clause, and any modern database will optimize it to execute the same way, but it's a worse syntactical representation of what's actually happening.

Re: Writing more legible SQL

#153

Post by combatentropy »

alistairbayley wrote: post #27
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…

1. Why indent the joins? Are table2 and table3 less important than table1? Is table1 special? Is that why it enjoys privileged status in the from-clause? 2. Why place some predicates in the where-clause and others in the join-clauses? What's the thinking here? Why not put all predicates up in the join-clauses, nearer to the tables that they affect?

  > Why indent the joins?
  > Are table2 and table3 less important than table1?
It's often arbitrary which tables are joined and which one is from'd. But the joins are part of the from-clause. They all join together into one big from.

  > Why place some predicates in the where-clause and
  > others in the join-clauses? What's the thinking here?
  > Why not put all predicates up in the join-clauses,
  > nearer to the tables that they affect?
The join conditions are just to line up the rows of the different tables with each other, to avoid a cartesian product, to form one big table.

This giant table is then filtered through the where-clause, like a funnel. You can put the filters in the joins, and I have in the past, but putting them in the where-clause better reflects the picture in my head.

Tangentially, it would have been better if SQL had the select-clause after the from- and where-clauses:

    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
    select
        t1.col1,
        t2.col2,
        t3.col3
To understand the select-clause, I always first have to jump down to the from-clause anyway. This would also mirror the other statements: insert, update, and delete, which begin with the table names.

This better reflects the flow of data. First you decide the source of data (which tables). Then you filter down to which records (which rows, the where-clause). Finally you determine which fields to get (which columns, the select-clause).

Re: Writing more legible SQL

#155

Post by tumba »

I highly recommend Joe Celko's book, SQL Programming Style. [1] He does deal with formatting, but offers a comprehensive usage standard that goes beyond readability to discussing thorny issues like the taxonomy of words like "type," "category," and "code," as well as referencing relevant international standards for identifier naming.

[1] https://www.amazon.com/Celkos-Programming-Kaufmann-Managemen...

Re: Writing more legible SQL

#156

Post by blakeyrat »

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…

Instead of using uppercase, which looks awful, just get an editor with color-coding. Solves the same problem, no more ugly uppercase.

Re: Writing more legible SQL

#157

Post by harterrt »

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.

The time saving isn't as important as avoiding frustration in your tools. Stubbing your toe on silly problems like trailing commas breaks flow and makes exploring less fun.

In my experience, these commas don't add any real meaning to the human readers. Will it really obfuscate the code for a future reader? I can imagine it can look unfamiliar and consequentially grate someone's nerves. Reminds me of the arguments around R's "I agree with the sentiment for most code, but I doubt I spend as much time reading my SQL queries as I spend writing and refactoring.

Re: Writing more legible SQL

#158

Post by harterrt »

marcosdumay wrote: post #114
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, ...

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.

Agreed.

Re: Writing more legible SQL

#159

Post by WorldMaker »

biztos wrote: post #104
lost_my_pwd wrote: post #59

Earlier quoted context omitted.

That matches what I do pretty much with the exception of capitalization. I agree, it's not totally necessary, but it does provide a visual delineation of each section/component of the the statement, which, for large statements, can be very helpful in quickly scanning what it does: 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 = some…

I do almost the same thing, but I capitalize all SQL keywords. I'm sure that's annoying to someone somewhere but I find it useful in separating the SQL from the relation names.

We've had great syntax highlighting tools for decades now, I'd much rather my tooling do it for me with subtle colorations than be forced to resort to COBOL SCREAMING.

I've trended towards a preference to "sentence-cased" SQL where the first word in an SQL statement is Capitalized and everything else is lower-cased. Makes things read more like a narrative and is a nicer hint than trying to spot an optional semi-colon to determine if you've reached the end of a command yet (and helps in those times when you need to find a place where it turned out an optional semi-colon wasn't in fact optional).

    Select *
    from table
    where condition
    order by fields

    Insert into table
      (...)
    values
      (...)
So far as I know I'm about the only person that likes sentence-casing SQL, but it helped me out quite a bit on some projects I worked on and it kept things readable.

Re: Writing more legible SQL

#160

Post by nthcolumn »

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 I too am of the river people now. +1 for long queries and being able to comment out everything. 30 years SQL. bah!
Post reply on HN