15 ms·

Writing more legible SQL

195 points by craigkerstiens 10y ago

guruparan18 10y ago
Am I the only one to see or missing obviously. The following is wrong, where clause has table name (baz)? SELECT foo, bar FROM baz WHERE foo > 3 AND baz = 'craig.kerstiens@gmail.com' Edit: Technically you can have a column name exactly same as table name, however I am finding it hard to find difference between the two queries presented.
- craigkerstiens 10y ago
  Yeah, definitely a typo in the example, will fix.
leetrout 10y ago
I loathe wide lines. I think it's much easier to mentally parse vertical lists of things and I write my SQL much like this. In PHP / Python / Go when inlining SQL I also line up sequential items indentation with spaces (which most editors do by default when hitting enter inside a multi line string). Nice to see this codified. I really just wish I would have worked more in my career on teams that care as much about readability and comprehension in 6+ months as I do. Hopefully more people will follow the advice in the article and write cleaner, easier to mentally parse SQL.
shearnie 10y ago
I prefer keywords in their own line eg: <code> SELECT foo, bar, blah FROM baz WHERE foo > 3 AND blah = 'dont use baz' </code>
ak39 10y ago
If you're in the world of Microsoft SQL Server: 1. http://poorsql.com/ http://poorsql.com/ 2. SSMS plug-in: http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_5 http://architectshack.com/PoorMansTSqlFormatter.ashx#Downloa... Your life reading and saving SQL will change forever.
jjp 10y ago
Would have been useful to see some thinking on joins and the naming conventions when you have the same column in both tables
- NDizzle 10y ago
  Use aliases, and if you have more than two joins you should prefix every column, unless the column names are obvious.
nurettin 10y ago
I prefer my own style where comma is placed before every column. It makes columns, subqueries and case expressions line up nicely, especially when you have 15 columns or more.
- 83457 10y ago
  I do this often mainly due to beine annoyed by missing a comma somewhere or having to worry about commas when commenting out the last item
- craigkerstiens 10y ago
  Yeah, I've definitely seen this and tried it myself. Visually for some reason, it just pains me too much. It does make removing lines much easier though so I can understand the appeal.
combatentropy 10y ago
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 things vertically. 3. Conjunctions like "and" are put at the end of a line, like a comma, not at the beginning. I think it lines things up better vertically. 4. I try to keep lines short, but I also try to keep it from getting needlessly long vertically. So if the statement is short and simple, I might fold some things back onto one line, like: select col1, col2, col3 from table1 where col1 = something_or_other or even: select * from table1 where col1 = something
- rubyfan 10y ago
  I go back and forth with conjunction at the end or beginning of the line. At the end you get to line up column names, at the beginning it makes it easier to comment out or remove the line and basically contains the intended logic on a single line. I think I usually end up with the later because I think it makes more sense.
  - berntb 10y ago
    Why not this? WHERE A AND B AND C Or better, this? You can comment out any part easily. WHERE 1 AND A AND B AND C (With keywords capitalized, as g-d intended. :-) Or not.)
    - nerdponx 10y ago
      I like the "WHERE 1" trick, thanks!
  - bhrgunatha 10y ago
    I have the same problem and also with where to put the commas separating columns names. You can't win the game though. Sometimes you want to remove/comment out the first line, sometimes the last, sometimes one in the middle.
maurycy 10y ago
Is there indent for SQL?
- ManlyBread 10y ago
  Poor Man's SQL Formatter for Notepad++ can do it, it leaves a lot to be desired though in terms of customization. It has saved me a lot of times, since I'd rather read Poor Man's code than the code of the other people.
  - gregn610 10y ago
    Another vote for Poor Man's. But I think it's an important caveat that the whole team use the same formatter and the same settings. Otherwise diff's turn to useless noise* because Cubicle Bob has personalized his settings. *Yes, I've tried the Winmerge plugin. It's helpful but not ideal.
- mianos 10y ago
  I am using Jetbrains Datagrip to write SQL all day, every day. It can auto-format SQL just like all their other tools can auto-format the language in use. It also has a bunch of other handy stuff like symbol completion from the current database.
- timlin 10y ago
  For Microsoft's SQL Server Management Studio, try SSMS Boost (http://www.ssmsboost.com/ http://www.ssmsboost.com/). There is a free community edition.] They just released a beta for SSMS 2016 that has a lot more flexible SQL formatting features. See http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-Beta--2008-2012-2014-2016 http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-....
Revell 10y ago
I write my SQL as follows: SELECT `second_column`, `fourth_column` FROM `table_name` WHERE `first_column` = 'Value' AND `third_column` = 3 ORDER BY `fifth_column` ASC LIMIT 1
- 83457 10y ago
  Well that is cute. I find it odd that the second line of the where clause is on the same level as the where but a neat format otherwise. Tabs or spaces though?
- dfox 10y ago
  What happens when you need to do something non-trivial? Ie. nested selects, joins or even only some more complex expression in WHERE?
chrismorgan 10y ago
The document styles make code blocks sans-serif, and then attempt to use monospace indentation—the end result looks worse, not better, than no leading indentation.
chrismorgan 10y ago
Of interest: http://www.sqlstyle.guide/ http://www.sqlstyle.guide/
- okket 10y ago
  It was on HN three weeks ago, here es is the link to the discussion: https://news.ycombinator.com/item?id=12671667 https://news.ycombinator.com/item?id=12671667 (146 comments)
mythz 10y ago
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'
- alistairbayley 10y ago
  Re uppercase: can't afford a syntax-highlighting editor?
  - Sean1708 10y ago
    It's true that there are some editors which highlight SQL inside strings, but most don't.
- bhrgunatha 10y ago
  That covers the simplest case. What would you do when you're selecting 20 columns from 5 different tables joined together?
wenham 10y ago
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.
deleted 10y ago
[deleted]
thom 10y ago
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.
mobiuscog 10y ago
Is it just me, or are the code examples not monospaced ? This doesn't help when talking about alignment.
- exprA 10y ago
  It isn't just you, and indeed, the alignment examples make little sense with a proportional typeface. I checked the CSS, and for some reason the code blocks use the exact same fonts as the rest of the text (albeit with a new font-family clause just for them).
- sundvor 10y ago
  Came here to say the same; really struggled to see the difference between before and after. :) I think I'll enjoy reading the comments here more. (Quite liked SQL Prompt's auto formatter, but a shame so hideously expensive).
mosselman 10y ago
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 should not do, just use common sense.
- mosselman 10y ago
  Haha woops, multiline did not work. The first example is supposed to read: SELECT foo, bar FROM baz
craigds 10y ago
Is there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.
- lunchladydoris 10y ago
  If Oracle is your flavour of SQL, PL/SQL Developer is very nice and has a good formatter built in.
- krylon 10y ago
  If you happen to use MS SQL Server, there is ApexSQL Refactor, a plugin to SQL Server Management Studio (https://www.apexsql.com/sql_tools_refactor.aspx https://www.apexsql.com/sql_tools_refactor.aspx). As the name implies it mainly does stuff other than formatting, but the latter is what I use it for, and I am rather happy with it. When I write SQL myself, I tend for format it rather scrupulously, but when I have to read SQL written by somebody else, it is very useful.
- mvdwoord 10y ago
  I have used this quite a bit.. http://www.dpriver.com/pp/sqlformat.htm http://www.dpriver.com/pp/sqlformat.htm
- jdwyah 10y ago
  IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
- jdwyah 10y ago
  IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
- sundvor 10y ago
  Redgate's SQL Prompt has a great auto formatter with definable rule sets, if you use SSMS - and have the budget for it.
justin_vanw 10y ago
So I think it is much easier to read if you can line up like clauses: SELECT spf.blah_blah, count(*) AS cnt FROM jv_CTLG_ENTITY_ATTR_PROD_MAP prod_map JOIN jv_SSA_PRODUCT_FACT spf ON prod_map.prod_ref_id = spf.PROD_REF_ID WHERE prod_map.INVSBL_IND = '0' AND spf.LSTG_END_DT >= date_sub(from_unixtime(unix_timestamp()), 2) AND spf.LSTG_START_DT <= date_sub(from_unixtime(unix_timestamp()), 2) AND spf.lstg_site_id = 0 AND prod_map.ENTITY_ATTR_TYPE_DESC IN ('BMPN','GTIN') AND spf.CTLG_ID = 1075 AND spf.LSTG_TYPE_CD NOT IN (10,12,15) GROUP BY spf.blah_blah;
ksri 10y ago
I'd love to get HN feedback on a library I wrote to make SQL more manageable - https://github.com/hashedin/jinjasql https://github.com/hashedin/jinjasql. The library is built on the Jinja template engine. You can create macros, functions and other reusable sql fragments, and then use them to create the query. You can also write loops and conditionals, so you can do all the things you routinely do to generate HTML. The library tracks bind parameters, and doesn't let user input into the query. At the end of the day, you get the generated SQL and an array of bind parameters. You can use these two to execute the query using whatever database & driver combination you like.
wiz21c 10y ago
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)
- tragomaskhalos 10y ago
  I have plenty of gripes about SQL but am not aware of any real alternatives ... which I do find bizarre - in the realm of programming languages in general we live in a time of Cambrian explosion in terms of the number and type of languages being used and developed, and yet SQL sits aloof and unassailable on its pedestal. There are a number of clear paths not taken: 1/ A new, better language that compiles down to SQL; 2/ A set of enhancements to SQL that compile down to regular SQL (<= best imo); 3/ A new language that compiles down to the same AST that is generated from SQL: this of course would need to be done by the database implementors. That we don't see this I attribute to: 1/ Perception that SQL is "good enough". Which is true as far as it goes, but again odd compared with the endless innovation in the world "normal" programming languages; 2/ Innate conservativism of the database tech community; 3/ Wide use of tools that already sit on top of SQL - query builder APIs, ORMs etc.
  - garysieling 10y ago
    I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.
    - __jal 10y ago
      ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere. My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior. People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).
lorenzfx 10y ago
How do you handle indentation in python (especially if the line is already indented)?
- falcolas 10y ago
  Triple quoted strings. Since SQL doesn't care about whitespace, you can line up your SQL with the rest of your code quite neatly.
dspillett 10y ago
> CTEs: First, yes they can be an optimisation boundary. Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.
the_ez 10y ago
sqlparse comes to mind - https://github.com/andialbrecht/sqlparse https://github.com/andialbrecht/sqlparse I know the maintainer, they also run a service at https://sqlformat.org/ https://sqlformat.org/ You can try it on the command line e.g. on Fedora: $ sudo dnf install python3-sqlparse $ echo "select c1, c2, c3 from table1 join table2 t2 on t1.col2 = t2.col1 where 1=2 order by col2 limit 100" | sqlformat -k upper -i lower -sa - SELECT c1, c2, c3 FROM table1 JOIN table2 t2 ON t1.col2 = t2.col1 WHERE 1 = 2 ORDER BY col2 LIMIT 100
jgilfillan 10y ago
This is how I write SQL: select col1 ,col2 ,col3 from table1 a left join table 2 b on a.col1 = b.col2 where 1=1 and col1 = 'condition' and col2 = 'condition2' ; * everything lower case (except strings) * leading commas * conditions indented by two spaces * select columns indented by two spaces, except the first colunm which is indented by three. * where 1=1 for easier commenting/uncommenting of conditions
- zikzak 10y ago
  Came here to find out who else uses leading commas. I learned this several years ago at my current job and love it. I find it much more readable and also makes adding to the list clearer for some reason. Plus if you ever need to insert like 50 commas at the start of a bunch of lines in an ad hoc query it is much easier at the start of the line (yes, I am an animal).
deleted 10y ago
[deleted]
erdemozg 10y ago
I use the following indentation pattern and find it very useful: select t1.col1, t2.col1 from table1 t1 left join table2 t2 on t2.primarykey = t1.foreignkey where t1.somevalue is not null and isnull(t2.someothervalue, 0) > 0 order by t1.someothervalue desc By using this pattern I can easily locate columns, tables, criterias, grouping fields, orders etc. And it works well with more complicated queries as well given all the subqueries, cases etc. written in a similar fashion.
codeulike 10y ago
This is all trivial. Simple queries are pretty easy to read as long as the formatting is vaguely sane. What we need to talk about is how to deal with queries with multiple nested subqueries (in the select clause and the where clause) and how to make that legible.
ed_blackburn 10y ago
SQL is 'blighted' by inertia.
nerdponx 10y ago
Am I the only one who puts commas on the next line? I think of them as an operator analogous to AND or ON: select t1.col1 as col1 , t2.col2 as col2 , t2.col3 as col3 It makes commenting out columns painless.
- _1 10y ago
  Same here ... except the first field would be on the same line as the select, and the comma would be aligned with select's "t"
- bigato 10y ago
  > It makes commenting out columns painless. No it does not. If you have to comment out the first column you'll have to remove the comma from the second column. It's the same problem that happens if you put the commas at the end of the line, except that the offending line will be the last column. So putting commas at the beginning just makes it look weird because it differs from how we use commas in natural language.
  - matwood 10y ago
    Exactly, You're also hiding the most important part of the line which is the variable name.
- __jal 10y ago
  You're not the only one, but I wish you were, because then I'd have to read a lot less of it. I doesn't eliminate your commenting problem, it moves your commenting problem from the last line of your list to the first. Also, it is visually distracting as all get out.
- nthcolumn 10y ago
  Yes I do. As you say so I can comment out easily but not so much on with the 'as' and maintaining the river.
krylon 10y ago
One issue that bit me pretty badly wrt comments is either Perl's DBI or the ODBC layer, but when talking to SQL Server from a Perl script via ODBC, I had to remove all comments from a rather convoluted SQL query. I ended up keeping the comments in my source code and removing them via a regex at runtime, but it took me quite a while to figure out why my Perl script failed with a query that ran perfectly when copied and pasted into SSMS.
agentgt 10y ago
I while back I wrote a crappy SQL mapper that has an interesting SQL template language that uses SQL comments to make Jdbc place holders: https://github.com/agentgt/jirm/blob/master/jirm-core/README.md https://github.com/agentgt/jirm/blob/master/jirm-core/README... One of the interesting side effects of using the parser was easier to read SQL.
bigato 10y ago
> 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 are not yet understanding the problem well enough to describe it in a single plain SQL query.
- blowski 10y ago
  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.
  - bigato 10y ago
    Care to elaborate on what situation would a CTE improve performance? Because the OP itself mentioned CTEs degrading performance.
    - blowski 10y ago
      It wouldn't improve performance, but it might improve readability without denigrating performance. I don't have much experience with CTEs, but I do have lots of experience trying to debug unreadable SQL.
      - bigato 10y ago
        > Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. > It wouldn't improve performance, but it might improve readability without denigrating performance. These two statements of yours are negating each other, unless people started using "performant" as synonymous for readable now.
        10y ago
stadeschuldt 10y ago
Good post. I use one of Jetbrains IDEs and I am big fan of their auto-formatting. You select the dialect of your SQL (or connect it to the data source) and the IDE almost formats it ideally. Sometimes I enforce certain line breaks but for the most part it does a really good job.
- douche 10y ago
  DataGrip is pretty excellent from them. The only thing I'm not really happy with is it's support for diagramming table structure, but I haven't found anything that really does a good job of that.
falcolas 10y ago
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 dogmatic style guide. My time as a DB admin has given me some specific style guidelines, typically stolen from the formatting provided by the DB itself. More specifically, I match my style to what you see when you run a command like `SHOW CREATE TABLE students;`. I tend to reserve my indentation for things like subqueries.
- irrational 10y ago
  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.
junto 10y ago
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.
- harterrt 10y ago
  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, ...
  - ckastner 10y ago
    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.
    - djur 10y ago
      > we are used to trailing commas Leading commas and other operators are common in some languages. Haskell, for example: https://github.com/tibbe/haskell-style-guide/blob/master/haskell-style.md https://github.com/tibbe/haskell-style-guide/blob/master/has...
    - Ntrails 10y ago
      I spend a lot of time writing ad hoc queries for analysis. That means I don't know what columns I want, I might be switching things up from aggregates to subsets and back. That's why you'll find things like: WHERE 1=1 AND a.id = 12 --AND a.Col1 > 23 AND a.Col1 = 23 Similarly the leading comma makes it faster to cut out things I'm not using anymore. Now obviously for production code you could argue my reasons are no longer valid - but probably they'll leak through because that's what all my shk hotkeys generate and I'm used to writing
akavi 10y ago
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.).
- jessemerriman 10y ago
  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/ http://www.try-alf.org/blog/
  - akavi 10y ago
    Sweet, Alf seems to be exactly what I wanted! Shame it no longer seems to be under active development, but it seems to be pretty mature?
- wvenable 10y ago
  C# has Linq
msluyter 10y ago
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?
- teej 10y ago
  This is very close to the style we use. The only big difference is that we opt for CTEs over subqueries whenever possible.
- perlgeek 10y ago
  We also use right-justified keywords (and never "select *", though I guess that was just to keep your example brief).
- 20years 10y ago
  This is the style I use other than I uppercase the reserved words.
- howItsDone123 10y ago
  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.
jenkstom 10y ago
I am a visual thinker. The method of listing columns as rows (i.e., the one line per column format) is too annoying to focus on the underlying code. Splitting a list is something like pagination or even regular text, so I suppose my mind just compensates for that without my thinking about it. But I just can't do the columns as rows thing. I feel a little bit out in the cold because of it, but I'll just keep reformatting everybody else's code so I can read it. :-)
davidw 10y ago
Anyone got some good Emacs advice for automated formatting?
iblaine 10y ago
I prefer any formatting over no formatting. If had to choose then I would do this: * Lowercase for tables & columns and upper case for syntax. * Having commas first makes it easier to move lines around. * I avoid small aliases to minimize conflicts when code is resused. * Every column needs a table reference. Do not make me guess. * Alias every column in the SELECT clause. SELECT table1.col1 AS table_col1 , table2.col2 AS table_col2 , table3.col3 AS table_col3 FROM table1 table1 INNER JOIN table2 table2 ON table1.col2 = table2.col1 INNER JOIN table3 table3 ON table1.col3 = table3.col1 AND table3.col2 = something_else WHERE table1.col1 > 0 AND table2.col2 <> table1.col4 ORDER BY table2.col2 LIMIT 100;
meganvito 10y ago
I think that may be more compact, if the IDE can reflect the algebra beneath the SQL clauses.
tumba 10y ago
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-Management-Systems/dp/0120887975/ https://www.amazon.com/Celkos-Programming-Kaufmann-Managemen...
rockinbizkitz 10y ago
I remember at one of my very first jobs, I had to deal with a dynamic report generator that would generate sql based on your UI selection. The SQL it generated was atleast a couple of pages long. The first thing I did was to lookup a sql formatter to help me figure out the dynamically generated sql statements. Thats how I came across sqlinform.com and I have always used it ever since. It has some pre-defined formats depending on the database you use such as Oracle, DB2, etc. and it also lets you customize these formats if you like.
deleted 10y ago
[deleted]
rdameron 10y ago
Just use Redgate SQL Prompt. It's the easiest fix. I do not work for Redgate.

195 points by craigkerstiens 10y ago

Am I the only one to see or missing obviously. The following is wrong, where clause has table name (baz)? SELECT foo, bar FROM baz WHERE foo > 3 AND baz = 'craig.kerstiens@gmail.com' Edit: Technically you can have a column name exactly same as table name, however I am finding it hard to find difference between the two queries presented.

craigkerstiens 10y ago
Yeah, definitely a typo in the example, will fix.

leetrout 10y ago

I loathe wide lines. I think it's much easier to mentally parse vertical lists of things and I write my SQL much like this. In PHP / Python / Go when inlining SQL I also line up sequential items indentation with spaces (which most editors do by default when hitting enter inside a multi line string). Nice to see this codified. I really just wish I would have worked more in my career on teams that care as much about readability and comprehension in 6+ months as I do. Hopefully more people will follow the advice in the article and write cleaner, easier to mentally parse SQL.

shearnie 10y ago

I prefer keywords in their own line eg: <code> SELECT foo, bar, blah FROM baz WHERE foo > 3 AND blah = 'dont use baz' </code>

ak39 10y ago

If you're in the world of Microsoft SQL Server: 1. http://poorsql.com/ http://poorsql.com/ 2. SSMS plug-in: http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_5 http://architectshack.com/PoorMansTSqlFormatter.ashx#Downloa... Your life reading and saving SQL will change forever.

jjp 10y ago

Would have been useful to see some thinking on joins and the naming conventions when you have the same column in both tables

NDizzle 10y ago
Use aliases, and if you have more than two joins you should prefix every column, unless the column names are obvious.

nurettin 10y ago

I prefer my own style where comma is placed before every column. It makes columns, subqueries and case expressions line up nicely, especially when you have 15 columns or more.

83457 10y ago
I do this often mainly due to beine annoyed by missing a comma somewhere or having to worry about commas when commenting out the last item
craigkerstiens 10y ago
Yeah, I've definitely seen this and tried it myself. Visually for some reason, it just pains me too much. It does make removing lines much easier though so I can understand the appeal.

combatentropy 10y ago

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 things vertically. 3. Conjunctions like "and" are put at the end of a line, like a comma, not at the beginning. I think it lines things up better vertically. 4. I try to keep lines short, but I also try to keep it from getting needlessly long vertically. So if the statement is short and simple, I might fold some things back onto one line, like: select col1, col2, col3 from table1 where col1 = something_or_other or even: select * from table1 where col1 = something

rubyfan 10y ago
I go back and forth with conjunction at the end or beginning of the line. At the end you get to line up column names, at the beginning it makes it easier to comment out or remove the line and basically contains the intended logic on a single line. I think I usually end up with the later because I think it makes more sense.
- berntb 10y ago
  Why not this? WHERE A AND B AND C Or better, this? You can comment out any part easily. WHERE 1 AND A AND B AND C (With keywords capitalized, as g-d intended. :-) Or not.)
  - nerdponx 10y ago
    I like the "WHERE 1" trick, thanks!
- bhrgunatha 10y ago
  I have the same problem and also with where to put the commas separating columns names. You can't win the game though. Sometimes you want to remove/comment out the first line, sometimes the last, sometimes one in the middle.

maurycy 10y ago

Is there indent for SQL?

ManlyBread 10y ago
Poor Man's SQL Formatter for Notepad++ can do it, it leaves a lot to be desired though in terms of customization. It has saved me a lot of times, since I'd rather read Poor Man's code than the code of the other people.
- gregn610 10y ago
  Another vote for Poor Man's. But I think it's an important caveat that the whole team use the same formatter and the same settings. Otherwise diff's turn to useless noise* because Cubicle Bob has personalized his settings. *Yes, I've tried the Winmerge plugin. It's helpful but not ideal.
mianos 10y ago
I am using Jetbrains Datagrip to write SQL all day, every day. It can auto-format SQL just like all their other tools can auto-format the language in use. It also has a bunch of other handy stuff like symbol completion from the current database.
timlin 10y ago
For Microsoft's SQL Server Management Studio, try SSMS Boost (http://www.ssmsboost.com/ http://www.ssmsboost.com/). There is a free community edition.] They just released a beta for SSMS 2016 that has a lot more flexible SQL formatting features. See http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-Beta--2008-2012-2014-2016 http://www.ssmsboost.com/social/posts/m12279-SSMSBoost-v3-0-....

Revell 10y ago

I write my SQL as follows: SELECT `second_column`, `fourth_column` FROM `table_name` WHERE `first_column` = 'Value' AND `third_column` = 3 ORDER BY `fifth_column` ASC LIMIT 1

83457 10y ago
Well that is cute. I find it odd that the second line of the where clause is on the same level as the where but a neat format otherwise. Tabs or spaces though?
dfox 10y ago
What happens when you need to do something non-trivial? Ie. nested selects, joins or even only some more complex expression in WHERE?

chrismorgan 10y ago

The document styles make code blocks sans-serif, and then attempt to use monospace indentation—the end result looks worse, not better, than no leading indentation.

chrismorgan 10y ago

Of interest: http://www.sqlstyle.guide/ http://www.sqlstyle.guide/

okket 10y ago
It was on HN three weeks ago, here es is the link to the discussion: https://news.ycombinator.com/item?id=12671667 https://news.ycombinator.com/item?id=12671667 (146 comments)

mythz 10y ago

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'

alistairbayley 10y ago
Re uppercase: can't afford a syntax-highlighting editor?
- Sean1708 10y ago
  It's true that there are some editors which highlight SQL inside strings, but most don't.
bhrgunatha 10y ago
That covers the simplest case. What would you do when you're selecting 20 columns from 5 different tables joined together?

wenham 10y ago

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.

deleted 10y ago

[deleted]

thom 10y ago

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.

mobiuscog 10y ago

Is it just me, or are the code examples not monospaced ? This doesn't help when talking about alignment.

exprA 10y ago
It isn't just you, and indeed, the alignment examples make little sense with a proportional typeface. I checked the CSS, and for some reason the code blocks use the exact same fonts as the rest of the text (albeit with a new font-family clause just for them).
sundvor 10y ago
Came here to say the same; really struggled to see the difference between before and after. :) I think I'll enjoy reading the comments here more. (Quite liked SQL Prompt's auto formatter, but a shame so hideously expensive).

mosselman 10y ago

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 should not do, just use common sense.

mosselman 10y ago
Haha woops, multiline did not work. The first example is supposed to read: SELECT foo, bar FROM baz

craigds 10y ago

Is there a good sql autoformatter? For cleaning up ORM-generated queries so I can read them. I've used python's sqlparse but it produces output that's often still unreadable.

lunchladydoris 10y ago
If Oracle is your flavour of SQL, PL/SQL Developer is very nice and has a good formatter built in.
krylon 10y ago
If you happen to use MS SQL Server, there is ApexSQL Refactor, a plugin to SQL Server Management Studio (https://www.apexsql.com/sql_tools_refactor.aspx https://www.apexsql.com/sql_tools_refactor.aspx). As the name implies it mainly does stuff other than formatting, but the latter is what I use it for, and I am rather happy with it. When I write SQL myself, I tend for format it rather scrupulously, but when I have to read SQL written by somebody else, it is very useful.
mvdwoord 10y ago
I have used this quite a bit.. http://www.dpriver.com/pp/sqlformat.htm http://www.dpriver.com/pp/sqlformat.htm
jdwyah 10y ago
IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
jdwyah 10y ago
IntelliJ's SQL editor is fabulous. Auto formatting and superb autocomplete, even with aliased tables and CTEs. If you don't want the full IntelliJ you can get Datagrip which is just the DB part. Can't say enough nice things about it.
sundvor 10y ago
Redgate's SQL Prompt has a great auto formatter with definable rule sets, if you use SSMS - and have the budget for it.

justin_vanw 10y ago

So I think it is much easier to read if you can line up like clauses: SELECT spf.blah_blah, count(*) AS cnt FROM jv_CTLG_ENTITY_ATTR_PROD_MAP prod_map JOIN jv_SSA_PRODUCT_FACT spf ON prod_map.prod_ref_id = spf.PROD_REF_ID WHERE prod_map.INVSBL_IND = '0' AND spf.LSTG_END_DT >= date_sub(from_unixtime(unix_timestamp()), 2) AND spf.LSTG_START_DT <= date_sub(from_unixtime(unix_timestamp()), 2) AND spf.lstg_site_id = 0 AND prod_map.ENTITY_ATTR_TYPE_DESC IN ('BMPN','GTIN') AND spf.CTLG_ID = 1075 AND spf.LSTG_TYPE_CD NOT IN (10,12,15) GROUP BY spf.blah_blah;

ksri 10y ago

I'd love to get HN feedback on a library I wrote to make SQL more manageable - https://github.com/hashedin/jinjasql https://github.com/hashedin/jinjasql. The library is built on the Jinja template engine. You can create macros, functions and other reusable sql fragments, and then use them to create the query. You can also write loops and conditionals, so you can do all the things you routinely do to generate HTML. The library tracks bind parameters, and doesn't let user input into the query. At the end of the day, you get the generated SQL and an array of bind parameters. You can use these two to execute the query using whatever database & driver combination you like.

wiz21c 10y ago

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)

tragomaskhalos 10y ago
I have plenty of gripes about SQL but am not aware of any real alternatives ... which I do find bizarre - in the realm of programming languages in general we live in a time of Cambrian explosion in terms of the number and type of languages being used and developed, and yet SQL sits aloof and unassailable on its pedestal. There are a number of clear paths not taken: 1/ A new, better language that compiles down to SQL; 2/ A set of enhancements to SQL that compile down to regular SQL (<= best imo); 3/ A new language that compiles down to the same AST that is generated from SQL: this of course would need to be done by the database implementors. That we don't see this I attribute to: 1/ Perception that SQL is "good enough". Which is true as far as it goes, but again odd compared with the endless innovation in the world "normal" programming languages; 2/ Innate conservativism of the database tech community; 3/ Wide use of tools that already sit on top of SQL - query builder APIs, ORMs etc.
- garysieling 10y ago
  I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.
  - __jal 10y ago
    ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators. Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL. Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere. My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior. People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).

lorenzfx 10y ago

How do you handle indentation in python (especially if the line is already indented)?

falcolas 10y ago
Triple quoted strings. Since SQL doesn't care about whitespace, you can line up your SQL with the rest of your code quite neatly.

dspillett 10y ago

> CTEs: First, yes they can be an optimisation boundary. Note that this varies by database engine. They are an optimisation fence in postgres, they generally aren't in MS SQL Server, I'm not sure about Oracle or DB2 but I think they can optimise predicate application over CTE boundaries too.

the_ez 10y ago

sqlparse comes to mind - https://github.com/andialbrecht/sqlparse https://github.com/andialbrecht/sqlparse I know the maintainer, they also run a service at https://sqlformat.org/ https://sqlformat.org/ You can try it on the command line e.g. on Fedora: $ sudo dnf install python3-sqlparse $ echo "select c1, c2, c3 from table1 join table2 t2 on t1.col2 = t2.col1 where 1=2 order by col2 limit 100" | sqlformat -k upper -i lower -sa - SELECT c1, c2, c3 FROM table1 JOIN table2 t2 ON t1.col2 = t2.col1 WHERE 1 = 2 ORDER BY col2 LIMIT 100

jgilfillan 10y ago

This is how I write SQL: select col1 ,col2 ,col3 from table1 a left join table 2 b on a.col1 = b.col2 where 1=1 and col1 = 'condition' and col2 = 'condition2' ; * everything lower case (except strings) * leading commas * conditions indented by two spaces * select columns indented by two spaces, except the first colunm which is indented by three. * where 1=1 for easier commenting/uncommenting of conditions

zikzak 10y ago
Came here to find out who else uses leading commas. I learned this several years ago at my current job and love it. I find it much more readable and also makes adding to the list clearer for some reason. Plus if you ever need to insert like 50 commas at the start of a bunch of lines in an ad hoc query it is much easier at the start of the line (yes, I am an animal).

deleted 10y ago

[deleted]

erdemozg 10y ago

I use the following indentation pattern and find it very useful: select t1.col1, t2.col1 from table1 t1 left join table2 t2 on t2.primarykey = t1.foreignkey where t1.somevalue is not null and isnull(t2.someothervalue, 0) > 0 order by t1.someothervalue desc By using this pattern I can easily locate columns, tables, criterias, grouping fields, orders etc. And it works well with more complicated queries as well given all the subqueries, cases etc. written in a similar fashion.

codeulike 10y ago

This is all trivial. Simple queries are pretty easy to read as long as the formatting is vaguely sane. What we need to talk about is how to deal with queries with multiple nested subqueries (in the select clause and the where clause) and how to make that legible.

ed_blackburn 10y ago

SQL is 'blighted' by inertia.

nerdponx 10y ago

Am I the only one who puts commas on the next line? I think of them as an operator analogous to AND or ON: select t1.col1 as col1 , t2.col2 as col2 , t2.col3 as col3 It makes commenting out columns painless.

_1 10y ago
Same here ... except the first field would be on the same line as the select, and the comma would be aligned with select's "t"
bigato 10y ago
> It makes commenting out columns painless. No it does not. If you have to comment out the first column you'll have to remove the comma from the second column. It's the same problem that happens if you put the commas at the end of the line, except that the offending line will be the last column. So putting commas at the beginning just makes it look weird because it differs from how we use commas in natural language.
- matwood 10y ago
  Exactly, You're also hiding the most important part of the line which is the variable name.
__jal 10y ago
You're not the only one, but I wish you were, because then I'd have to read a lot less of it. I doesn't eliminate your commenting problem, it moves your commenting problem from the last line of your list to the first. Also, it is visually distracting as all get out.
nthcolumn 10y ago
Yes I do. As you say so I can comment out easily but not so much on with the 'as' and maintaining the river.

krylon 10y ago

One issue that bit me pretty badly wrt comments is either Perl's DBI or the ODBC layer, but when talking to SQL Server from a Perl script via ODBC, I had to remove all comments from a rather convoluted SQL query. I ended up keeping the comments in my source code and removing them via a regex at runtime, but it took me quite a while to figure out why my Perl script failed with a query that ran perfectly when copied and pasted into SSMS.

agentgt 10y ago

I while back I wrote a crappy SQL mapper that has an interesting SQL template language that uses SQL comments to make Jdbc place holders: https://github.com/agentgt/jirm/blob/master/jirm-core/README.md https://github.com/agentgt/jirm/blob/master/jirm-core/README... One of the interesting side effects of using the parser was easier to read SQL.

bigato 10y ago

> 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 are not yet understanding the problem well enough to describe it in a single plain SQL query.

blowski 10y ago
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.
- bigato 10y ago
  Care to elaborate on what situation would a CTE improve performance? Because the OP itself mentioned CTEs degrading performance.
  - blowski 10y ago
    It wouldn't improve performance, but it might improve readability without denigrating performance. I don't have much experience with CTEs, but I do have lots of experience trying to debug unreadable SQL.
    - bigato 10y ago
      > Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. > It wouldn't improve performance, but it might improve readability without denigrating performance. These two statements of yours are negating each other, unless people started using "performant" as synonymous for readable now.
      - 10y ago

stadeschuldt 10y ago

Good post. I use one of Jetbrains IDEs and I am big fan of their auto-formatting. You select the dialect of your SQL (or connect it to the data source) and the IDE almost formats it ideally. Sometimes I enforce certain line breaks but for the most part it does a really good job.

douche 10y ago
DataGrip is pretty excellent from them. The only thing I'm not really happy with is it's support for diagramming table structure, but I haven't found anything that really does a good job of that.

falcolas 10y ago

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 dogmatic style guide. My time as a DB admin has given me some specific style guidelines, typically stolen from the formatting provided by the DB itself. More specifically, I match my style to what you see when you run a command like `SHOW CREATE TABLE students;`. I tend to reserve my indentation for things like subqueries.

irrational 10y ago
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.

junto 10y ago

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.

harterrt 10y ago
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, ...
- ckastner 10y ago
  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.
  - djur 10y ago
    > we are used to trailing commas Leading commas and other operators are common in some languages. Haskell, for example: https://github.com/tibbe/haskell-style-guide/blob/master/haskell-style.md https://github.com/tibbe/haskell-style-guide/blob/master/has...
  - Ntrails 10y ago
    I spend a lot of time writing ad hoc queries for analysis. That means I don't know what columns I want, I might be switching things up from aggregates to subsets and back. That's why you'll find things like: WHERE 1=1 AND a.id = 12 --AND a.Col1 > 23 AND a.Col1 = 23 Similarly the leading comma makes it faster to cut out things I'm not using anymore. Now obviously for production code you could argue my reasons are no longer valid - but probably they'll leak through because that's what all my shk hotkeys generate and I'm used to writing

akavi 10y ago

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

jessemerriman 10y ago
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/ http://www.try-alf.org/blog/
- akavi 10y ago
  Sweet, Alf seems to be exactly what I wanted! Shame it no longer seems to be under active development, but it seems to be pretty mature?
wvenable 10y ago
C# has Linq

msluyter 10y ago

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?

teej 10y ago
This is very close to the style we use. The only big difference is that we opt for CTEs over subqueries whenever possible.
perlgeek 10y ago
We also use right-justified keywords (and never "select *", though I guess that was just to keep your example brief).
20years 10y ago
This is the style I use other than I uppercase the reserved words.
howItsDone123 10y ago
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.

jenkstom 10y ago

I am a visual thinker. The method of listing columns as rows (i.e., the one line per column format) is too annoying to focus on the underlying code. Splitting a list is something like pagination or even regular text, so I suppose my mind just compensates for that without my thinking about it. But I just can't do the columns as rows thing. I feel a little bit out in the cold because of it, but I'll just keep reformatting everybody else's code so I can read it. :-)

davidw 10y ago

Anyone got some good Emacs advice for automated formatting?

iblaine 10y ago

I prefer any formatting over no formatting. If had to choose then I would do this: * Lowercase for tables & columns and upper case for syntax. * Having commas first makes it easier to move lines around. * I avoid small aliases to minimize conflicts when code is resused. * Every column needs a table reference. Do not make me guess. * Alias every column in the SELECT clause. SELECT table1.col1 AS table_col1 , table2.col2 AS table_col2 , table3.col3 AS table_col3 FROM table1 table1 INNER JOIN table2 table2 ON table1.col2 = table2.col1 INNER JOIN table3 table3 ON table1.col3 = table3.col1 AND table3.col2 = something_else WHERE table1.col1 > 0 AND table2.col2 <> table1.col4 ORDER BY table2.col2 LIMIT 100;

meganvito 10y ago

I think that may be more compact, if the IDE can reflect the algebra beneath the SQL clauses.

tumba 10y ago

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-Management-Systems/dp/0120887975/ https://www.amazon.com/Celkos-Programming-Kaufmann-Managemen...

rockinbizkitz 10y ago

I remember at one of my very first jobs, I had to deal with a dynamic report generator that would generate sql based on your UI selection. The SQL it generated was atleast a couple of pages long. The first thing I did was to lookup a sql formatter to help me figure out the dynamically generated sql statements. Thats how I came across sqlinform.com and I have always used it ever since. It has some pre-defined formats depending on the database you use such as Oracle, DB2, etc. and it also lets you customize these formats if you like.

deleted 10y ago

[deleted]

rdameron 10y ago

Just use Redgate SQL Prompt. It's the easiest fix. I do not work for Redgate.