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

21–30 of 84 posts

Re: How I write SQL

#21

Post by tragomaskhalos »

One thing I wish more people would do is to indent joins appropriately; e.g.

    ...
    Inner join a on a.... = main..
      Inner join b on b... = a...
        Inner join c on c... = b...
      Inner join x on x... = a...
The syntax of SQL is a real basket case, readers need all the help you can give them.

Re: How I write SQL

#22

Post by umsm »

fusiongyro wrote: post #19

The fact that the first thing he does with the tags is unnest them is, IMO, material evidence for a traditional 1NF formulation. It's worth considering that using arrays is a violation of the first normal form. That's a good indicator of how obvious Codd et. al. thought this rule was. Other than that, I use a variant of this style, where I put things on one line if possible (especially GROUP BY and ORDER BY). And I t…

I almost agree with you, but I put all reserved words (including ON) on a new line.

Re: How I write SQL

#23

Post by noblethrasher »

hdctambien wrote: post #18
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.

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

It's probably more pleasent and easier to read for the next person.

Re: How I write SQL

#24

Post by unoti »

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.

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 apparent when edit Erlang).

Being very dogmatic, and in a discussion of ideas saying "that's the worst thing in the world" is easily worse than comma placement. So surely commas as indentation isn't the worst thing in the world.

Re: How I write SQL

#25

Post by jradakov »

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.

I don't like having comma first listings, but it makes it incredibly easy to comment out parts of a query. I find commenting out parts of a query essential when trying to debug really long, complex queries.

Re: How I write SQL

#26

Post by rtfeldman »

hdctambien wrote: post #18
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.

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

That's where it goes in English.

Re: How I write SQL

#27

Post by NDizzle »

bigdubs wrote: post #10
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.

Another tip for using WITH on MSSQL - prefix it with semicolon like this: ;WITH

MSSQL makes it so that people pick up bad habits over the years and often you'll find missing semicolons at the end of statements. While the parser will intelligently handle missing semicolons for the majority of of scripts, it will bomb 100% of the time (at least up to 2008R2) on WITH.

Re: How I write SQL

#29

Post by jkeel »

hdctambien wrote: post #18
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.

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

That's what I was wondering. The comma is just a separator. Plus it's only really needed BEFORE each field not after the preceding field.

Re: How I write SQL

#30

Post by ExpiredLink »

iaskwhy wrote: post #15
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 ...

For the WHERE clauses one can use: WHERE 1=1 AND ... --AND ... AND ... I do not like having commas at the beginning of the lines though.

> WHERE 1=1

ORM frameworks frequently use this when they create SQL.

Post reply on HN