Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How I write SQL

craigkerstiens.com

Post reply on HN
Print view View original

1–10 of 84 posts

Re: How I write SQL

#2

Post by btilly »

My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out.

See http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.

Re: How I write SQL

#3

Post by Svip »

I don't write SQL like that. He has a good point about using a line for each and/or condition in the where clause and starting with either one (easier to remove).

But I don't think all queries should be written in several lines. If a query is simple enough (e.g. no aggregation, group by, having and at maximum one where condition and few fields to select), it can be written in one line, like:

SELECT field FROM table WHERE value = @value

That's easy and straightforward to understand and readable (which was initially part of SQL's goals, although we can discuss at a later date how successful that was). Complicated SQL queries - which are bound to happen - follow - in my style - a more straightforward approach:

There are new lines after every grouping (FROM, WHERE, GROUP BY, SORT BY, HAVING, etc.) and indenting. In fact, I prefer to indent my fields twice, because I consider FROM, WHERE, etc. to be subject to SELECT (even if part of an INSERT). I also tend to avoid extra keywords such as 'OUTER' in 'LEFT OUTER JOIN' (there is no such thing as 'LEFT INNER JOIN'). And then I follow each JOIN statement (indented compared to the ground table in from) by the table name, a new line, an indent and the ON condition. If another table relies on that table being joined, I indent it further to indicate that, so I can quickly look at my code to see what tables goes through which (which is useful if you have a lot of relationship tables).

Should be noted, however, I most commonly write towards Sybase at work, which tends to have no preference - style wise - on whether you write the keywords in upper case. My co-workers do not, so I don't either. But when I write my own projects against MySQL/MariaDB, I use upper case. But I generally follow the same style.

Re: How I write SQL

#4

Post by Svip »

btilly wrote: post #2

My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out. See http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.

You also write FROM/JOIN/ON indentations like I do.

Re: How I write SQL

#5

Post by unoti »

A neat trick on writing sql that I learned working with Oracle consultants:

    SELECT field1
    , field2
    , field3
    , another_field
    FROM ...
By placing the comma at the beginning of the line, instead of at the end, we can very easily reorganize the sequence without fiddling with commas most of the time:

    SELECT field1
    , field3
    , another_field
    , field2
    FROM ...

Re: How I write SQL

#6

Post by mjt0229 »

I have developed a very similar style, for the same reasons. It's funny to see this blog post, because I have gobs of SQL just like it on my other monitor.

Re: How I write SQL

#8

Post by larsolefson »

My personal style diverges considerably.

First, most of my SQL scripts are multiple statements, typically 6+, ranging up as high as 100. When I'm reading and trying to digset such scripts, the long format described by the author, particularly putting each column on its own row, makes it difficult to easily digest the script. I'm forced to scroll constantly to make sense of the statements in relation to one another. Instead, I prefer to my SQL to be more compact, so I can view as much of the total script as possible.

To accomplish this, and to maintain readability, I structure each statement something like this:

CREATE/INSERT/UPDATE line SELECT line FROM line JOINS (if present) ON (if present) WHERE (if present) AND (if present) Additional SQL (qualify, group by, order by) ;

If I have a lot of columns, the select portion will get split into several lines, usually where there is a case when, column operations, or when the line is 200ish characters long.

This makes the most sense to me, as each command (create, update, insert, select, from, join, on, where, and, group by) is on its own line, followed by the information relevevant to it.

Re: How I write SQL

#10

Post by bigdubs »

rachbelaid wrote: post #7

I very like the WITH statement in Postgres. It allow to break down the logic and composite the result. Any idea if it's standard SQL?

[strikeout, see comment above, it's in SQL99]

MSSQL has a similar construct with Common Table Expressions (CTE's) though, so it' not unique to PG.

Post reply on HN