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

51–60 of 84 posts

Re: How I write SQL

#51

Post by jmelloy »

Svip wrote: post #3

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 W…

If I'm doing a simple query, I still structure it as

  select blah, blha, blah
  from dual
  where ...
I find that even for short ones, lines quickly get very long and illegiable if you're shmushing them all onto one line.

Re: How I write SQL

#52

Post by garblegarble »

btilly wrote: post #36
ebbv wrote: post #14

Earlier quoted context omitted.

No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.

My experience. Changed formatting is a shock the first time. But it does not take long to retrain yourself to find it aesthetically more pleasing. When you're making a quick fix to a batch job that does not hit the problematic query for 30 minutes, the cost of messing up the comma is 30 minutes. (Yeah, I know, it all should be properly factored out, and unit tested. But there is a lot of lightly tested code in the re…

Couldn't you use an IDE that understands SQL? IntelliJ does this - it recognises the SQL dialect you're using and can even validate against the tables & columns in your schema

Re: How I write SQL

#53

Post by rosser »

ebbv wrote: post #47
unoti wrote: post #24

Earlier quoted context omitted.

The sensibilities for everything you just said are entirely subjective, and change as you are exposed to them. If you spend all day every day writing SQL, it is different than if you do it a little here and there. The entire codebase of Oracle Apps looks like this, so I found I grew accustomed to it over time and saw the benefits. Editing commas actually can be significant in terms of time and drag (this is even more…

It's not the worst thing in the world. It's the worst thing in the history of the world . Get it right, gosh. Seriously, though, hideous syntax is bad. It makes life miserable for everyone who has to work on your code after you. Don't do it, no matter what good reasons you think you have.

"Ugly" is a subjective judgement. "Worst thing in the history of the world" is a subjective judgement, and egregious hyperbole, to boot. Saying that mitigates pretty much all the credibility of any rational, non-hyperbolic argument you might also have offered in support of your point.

I find having to re-run my query because I forgot to delete the comma following the penultimate item in the SELECT list when I commented the ultimate one out "ugly".

Additionally, everyone I have ever explained this concept to has said some variation of, "Wow, that's a great idea. Thanks!" No one has ever said, "That's the worst thing in the history of the world" ... until you.

I've been doing database work professionally for a decade now. I've explained this concept to a lot of people.

Re: How I write SQL

#54

Post by jmelloy »

tom_b wrote: post #40

Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the values were stored in XML element attributes. I tend to build these monstrosities up in a very repl-like way. SQL actually led me to Lisp . . . BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things. with bgu as (select pro…

I can't stand the XMLTable syntax. I like the idea of turning an xpath expression into a result set, but in practice it's a massive pain in the ass.

Re: How I write SQL

#55

Post by Osiris »

btilly wrote: post #42
MartinCron wrote: post #37

Earlier quoted context omitted.

This reminds me about something that I like in C#, you can leave an extra comma at the end of a sequence and the compiler doesn't freak out on you.

JavaScript is almost the same way. It works in some browsers, but not in others. :-(

This exact issue caused us to deploy code that completely broke our application in IE7. There was a trailing comma in a list which all browsers ignore except IE6/7.

To this day we still give that dev (the team lead) a hard time about the trailing comma. We since implemented mandatory linting on all JS.

Re: How I write SQL

#56

Post by mast »

I don't often deal with SQL and when I do, it is usually quite simple. But, I think there is actually an error in the first step:

   SELECT 
     avg(rating),
     wine_id
   FROM 
     app_wine
   GROUP BY
     wine_id;
The query should be against the app_rating table. It does get corrected later on, but I was confused for a while.

Re: How I write SQL

#57

Post by mmsimanga »

ebbv wrote: post #14
unoti wrote: post #5

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

No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.

Not sure why the strenuous objection to the comma first. I too code that way and a couple of SQL formatting tools such as http://poorsql.com/ have the comma first as the default setting. An added advantage to the one mentioned by the original comment is being able to utilise column editing features.

Re: How I write SQL

#58

Post by genwin »

unoti wrote: post #24
ebbv wrote: post #14

Earlier quoted context omitted.

No, no, no. Comma first variable listing is the worst thing ever in the history of the world. Having the comma in the proper place has such a negligible cost (oh no I might have to delete the comma on the last element and add it to the formerly-last element!), and having it like this looks so god awful and doesn't really save you anything if you're swapping the first element instead of the last one.

The sensibilities for everything you just said are entirely subjective, and change as you are exposed to them. If you spend all day every day writing SQL, it is different than if you do it a little here and there. The entire codebase of Oracle Apps looks like this, so I found I grew accustomed to it over time and saw the benefits. Editing commas actually can be significant in terms of time and drag (this is even more…

I do SQL most days. Commas at the end of the line is more advantageous than not, in my book. I find this best:

  select
      Field1,
      Field2,
      AnotherField
  from
      Table1
So clean, so consistent! No all-uppercase that adds almost no benefit at significant cost. Fields lined up for easy reading and editing.

The comma controversy could be eliminated if the SQL standard was like Go's, where even the last line would have a comma.

Re: How I write SQL

#59

Post by tom_b »

jmelloy wrote: post #54
tom_b wrote: post #40

Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the values were stored in XML element attributes. I tend to build these monstrosities up in a very repl-like way. SQL actually led me to Lisp . . . BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things. with bgu as (select pro…

I can't stand the XMLTable syntax. I like the idea of turning an xpath expression into a result set, but in practice it's a massive pain in the ass.

XML and Oracle drives me crazy in general. You wind up with neither a relational model nor a generally useful way of data storage/exchange.

I am almost always working with legacy schemas, many generated by long-extinct tools or developers, and being a genuinely curious person, wandering down strange code paths to see if I can satisfy random reporting requests.

Re: How I write SQL

#60

Post by rosser »

Please, please, please don't perform your JOINs with comma-separated items in the FROM clause. As soon as you need also to do an OUTER JOIN, you're going to get unexpected results.

Per the SQL spec, explicit JOIN-style syntax binds more tightly than FROM-list elements, which can not only constrain the query planner in the choices it can make, but can actually yield a different result.

Post reply on HN