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

61–70 of 84 posts

Re: How I write SQL

#61

Post by ebbv »

rosser wrote: post #53
ebbv wrote: post #47

Earlier quoted context omitted.

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

Chances are if you're explaining this to someone they have less experience than you so they wouldn't be in a position to tell you that this is a bad practice.

Like I said, objectively, you're just moving the extra work from moving around the last element to moving around the first element. You're not really saving any time. Plus even if you accept the argument that you are saving time (pretend you live in a world where you never move the first element) the time you save is negligible.

If you hadn't resorted to this ugly syntax, you should have developed habits that lead you to fix your commas whenever you move the last element around.

Obviously you can do what you want, but if you were on my team, I would not allow this syntax.

Re: How I write SQL

#62

Post by aidos »

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

I do something similar:

    select 
          site.name
        , site.plan
        , site.status
        , domain.domain
        , subscription.updated_at
    from
        subscription
        inner join site on subscription.site_id = site.id
        inner join domain on domain.site_id = site.id
    where
        subscription.status = 'inactive'
        and subscription.plan = 'pro'
        and domain.created_at
When I was writing a lot of sql I found it a lot easier to work with (when you're commenting things out a lot during development for example). Also, I'm a geek, so I like stuff lined up.

I understand the arguments for putting commas at the end of the line. For example, Google say in their style guide that when reading, having the comma at the end alerts the reader to the continuation on the next line. In languages that allow trailing commas (Python, php, C# etc) that's what I do. I put them at the front otherwise.

It's certainly not worth an overreaction though. Having commas on either end of the line isn't going to kill anyone. I've written 1000s of lines of sql in both styles and the world hasn't imploded yet.

As always, be consistent with the surrounding code - that's more important than any other rules (especially ones with a purely subjective basis).

Re: How I write SQL

#63

Post by genwin »

MartinCron wrote: post #37
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 ...

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.

So often I've wished SQL worked like that. Go requires a comma at the end of a sequence.

Re: How I write SQL

#64

Post by cptskippy »

ebbv wrote: post #49
hdctambien wrote: post #18

Earlier quoted context omitted.

Why is "after the element" the proper place for a comma?

The same reason that you should put spaces before and after your logical operators. The same reason you should use consistent spacing on the left and right parenthesis. The same reason you should use caps for the SQL and lower case for all table and field names. Because it makes your SQL more legible. In english, commas go after the previous word, not before the next word. The syntax is the same for all programming l…

I disagree with you regarding legibility. I've been using commas like this in SQL for 10 years and I find it to be much more legible and functional this way.

Besides the subjective argument of legibility, what other benefits does it have?

Having a comma preceding something is an indicator that it is one of many. When debugging you can easily comment out the column, or table from a query.

Re: How I write SQL

#65

Post by saurik »

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

By putting the commas at the beginning, you can easily reorganize the sequence without messing with the commas for all but the first element. By putting the commas at the end, you can easily reorganize the sequence without messing with the commas for all but the final element. I do not see how this is any different. Maybe the description should be more of the form "I can append entries to the end by simply adding a single line"?

Re: How I write SQL

#66

Post by btilly »

ebbv wrote: post #61
rosser wrote: post #53

Earlier quoted context omitted.

"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 comment…

Chances are if you're explaining this to someone they have less experience than you so they wouldn't be in a position to tell you that this is a bad practice. Like I said, objectively, you're just moving the extra work from moving around the last element to moving around the first element. You're not really saving any time. Plus even if you accept the argument that you are saving time (pretend you live in a world whe…

Like I said, objectively, you're just moving the extra work from moving around the last element to moving around the first element.

Except that moving the last element (more specifically appending after it) is by far the more common operation. And when you mess up on the first element, it is visually obvious that you have done so.

If you hadn't resorted to this ugly syntax, you should have developed habits that lead you to fix your commas whenever you move the last element around.

I worked with SQL for years, and never developed the habit reliably enough. Once I was introduced to the idea of the leading comma, I noticed the difference.

Obviously you can do what you want, but if you were on my team, I would not allow this syntax.

Then let's both be glad that I'm not on your team, because there are some co-workers that I don't want to have to put up with.

Re: How I write SQL

#67

Post by rosser »

ebbv wrote: post #61
rosser wrote: post #53

Earlier quoted context omitted.

"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 comment…

Chances are if you're explaining this to someone they have less experience than you so they wouldn't be in a position to tell you that this is a bad practice. Like I said, objectively, you're just moving the extra work from moving around the last element to moving around the first element. You're not really saving any time. Plus even if you accept the argument that you are saving time (pretend you live in a world whe…

To speak frankly, you seem pretty belligerent in your crusade against the comma-firsters. For whatever reason, this is apparently holy war territory for you, and you're willing to make assumptions about me as a person, and my abilities as a technologist, based on where I put a goddamned comma.

Thanks for that.

Re: How I write SQL

#68

Post by btilly »

garblegarble wrote: post #52
btilly wrote: post #36

Earlier quoted context omitted.

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

Discussing IDEs is a different religious war. Outside of the Java world, people are not so wedded to IDEs.

Going back to my last job that was mostly SQL, I was working in Perl, with SQL embedded in TT templates. Good luck finding an IDE that understands that.

In that job the comma placement was critical. Which column would be the last column would depend on what options the report was being generated with. The point is that people would ask for many variations on the same report. One person would want a top level view, another would want to break out event type, another would want to break it out by which type of advertising campaign was thrown at it - and all were being generated out of the same templated SQL so I didn't have to maintain code in parallel. But the result is that in the code I couldn't make assumptions about which column was going to wind up being last.

Thanks to leading comma formatting, it was sufficient to require that the FIRST field stayed the same. Which proved to never be a problem.

Re: How I write SQL

#69

Post by iblaine »

What is comes down to is use any type of formatting as opposed to no formatting. Too often people crank out code, do not comment it and do not format it. This applies to every language. So far as SQL syntax goes, what irks me are:

* No use of spaces.

* Obscure use of aliases. If you have 4 tables all aliased as a,b,c,d then you are making life hard for someone else.

* Not capitalizing operations or reserved words.

Re: How I write SQL

#70

Post by btilly »

ebbv wrote: post #46
btilly wrote: post #36

Earlier quoted context omitted.

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…

That's a methodology problem you're describing. The solution should not be hideous syntax.

There are a ton of companies out there doing interesting things that are willing to pay well for working on code that has that methodology problem. But they are not willing to undertake a full rewrite of their system at the outset before you can start doing anything useful.

At that point you can choose which is more important - the religious war or getting on with your life. When I encounter things like that my vote is a bit of both. I bring habits that allow me to cope with the methodological problem, and incrementally try to create a space where I don't have a problem.

In the process I've found that my preference for trailing commas was a useless idiosyncrasy that I could live without. When I tried living without it, I quickly found that leading commas made my life more pleasant, and I learned to like that formatting style.

Post reply on HN