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

21–30 of 168 posts

Re: Writing more legible SQL

#21

Post by mythz »

I also use UPPER case for SQL Keywords to visually distinguish them from user-defined names (i.e. similar purpose to syntax coloring), but I don't collapse SELECT over multiple lines and line up each statement so the conditions line-up, e.g:

    SELECT foo, bar
      FROM baz
     WHERE foo > 3
       AND bar = 'craig.kerstiens@gmail.com'

Re: Writing more legible SQL

#22

Post by wenham »

Yes, readability is good, it makes any scripting easier. SQL is no exception. Hand in hand with this is calling things what they are and good comments for the 'why' rather than the 'what' parts of a statement.

Re: Writing more legible SQL

#23

Post by BurningFrog »

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…

The purpose of capitalization is to visually distinguish the keywords.

In this style that's done in other ways so there is no need.

Re: Writing more legible SQL

#24

Post by alistairbayley »

mythz wrote: post #21

I also use UPPER case for SQL Keywords to visually distinguish them from user-defined names (i.e. similar purpose to syntax coloring), but I don't collapse SELECT over multiple lines and line up each statement so the conditions line-up, e.g: SELECT foo, bar FROM baz WHERE foo > 3 AND bar = 'craig.kerstiens@gmail.com'

Re uppercase: can't afford a syntax-highlighting editor?

Re: Writing more legible SQL

#27

Post by alistairbayley »

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?

Re: Writing more legible SQL

#28

Post by thom »

I make very liberal use of functions in SQL, as much as I would in other languages - any complex calculation or WHERE clause I'll usually abstract out into a function with a meaningful name. Postgres has nice syntactic sugar that makes these look pretty much like columns, so if your function takes a row type as an argument, you can just call it as `row.function_name` instead of `function_name(row)`. With the right hints performance is fine, and where it's not, it's possible to build indexes over function call results.

The article touches on CTEs, while mentioning they are on optimisation boundaries on some platforms (e.g. Postgres). This touches on my main annoyance with SQL - most of the abstractions are very costly. Even just phrasing a query in the simplest, most natural way is rarely optimal. I spend hours of my life wondering why query planners are so deliberately obtuse. And there are countless specific little annoyances, e.g. 'percentile_disc' seeming like a natural fit for calculating a median, but being wildly slower than more hacky solutions.

I would love to know what's next, to be honest. A readable query language, which captures the intention and semantics of a query clearly and minimally, is designed to allow abstractions to be composed together, and compiles to a mostly optimal query plan. I'd probably settle for SQL as-is, if there was an RDBMS with a query planner that was willing to go away and think for five minutes so I could just write the SQL I wanted without thinking too hard about the implementation.

Anyway, the formatting's important, but like any language, the way you can create and compose abstractions, and reveal the intention of your code is the biggest thing for readability.

Post reply on HN