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

41–50 of 84 posts

Re: How I write SQL

#41

Post by btilly »

larsolefson wrote: post #8

My personal style diverges considerably. First, most of my SQL scripts are multiple statements, typically 6+, ranging up as high as 100. When I'm reading and trying to digset such scripts, the long format described by the author, particularly putting each column on its own row, makes it difficult to easily digest the script. I'm forced to scroll constantly to make sense of the statements in relation to one another. I…

The same argument applies to writing normal code. The semi-colon exists so that you can put more statements on a line, and make your code fit into your editor, right?

You don't agree?

What is the difference between coding in that language and SQL? I submit that it is only the amount of it you write.

I spent several years of my life focusing on reporting, and spending more time writing/maintaining SQL than writing any other language. In that time I discovered that complex SQL queries are a language like anything else. For "hello world" you can get away with anything. But as soon as you are doing complex stuff, the layout matters.

Did you know that it can make a difference whether a condition is in your ON or your WHERE? It can. (Think left joins.) Did you know that the location/order of the ON statements can make a difference? It does. Is it visually obvious where this particular condition is? It should be. Did you know that the order you put things in in your query can have performance impacts? It shouldn't, but it does (particularly for MySQL - MySQL is stupid).

If you've got 200ish character lines and you are unwilling to format, well, I'm glad that I don't work with you. Because I'm likely to be asked to figure it out at some point, and I don't want to maintain crap like that.

Re: How I write SQL

#42

Post by btilly »

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.

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

Re: How I write SQL

#43

Post by prawks »

I was hoping to see some examples of more complex WHERE clauses. They can get a little messy when you start mixing ANDs and ORs at different levels of nesting.

Also, why are

  wine_tags as (
    SELECT DISTINCT
      unnest(tags) as tag,
      wine_id
    FROM 
      app_rating
    GROUP BY 
      wine_id, tags),

  wine_detail as (
    SELECT
      app_wine.name as name,
      app_wine.id,
      app_winery.name as winery
    FROM
      app_wine,
      app_winery
    WHERE app_wine.winery_id = app_winery.id
   )
handling closing parentheses differently? This affects how you organize nested parentheses.

Re: How I write SQL

#44

Post by ajw0100 »

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…

This is a breeze. Don't make me paste some of the wacky SQL I've seen generated from Oracle's BI tools when they are being abused.

Re: How I write SQL

#45

Post by bcgraham »

tragomaskhalos wrote: post #21

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.

Whenever I see it, it takes too long to differentiate it from a bunch of random indentation. Whether this indentation system is needlessly time-consuming or necessary depends a lot on the type of query and the underlying schema.

Re: How I write SQL

#46

Post by ebbv »

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…

That's a methodology problem you're describing.

The solution should not be hideous syntax.

Re: How I write SQL

#47

Post by ebbv »

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…

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.

Re: How I write SQL

#48

Post by swalsh »

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'm just biting my time until someone makes a "Daily WTF" website for monster queries. My worst was this sync that took data from one database, and put it into another one. The thing though is the source database was a CRM system that is on its 3rd iteration. So there's a million random fields from old systems that have accumulated over the years that need to be checked.

Re: How I write SQL

#49

Post by ebbv »

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?

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 languages. The fact that most compilers and interpreters don't require it is not an excuse for abusing it.

Re: How I write SQL

#50

Post by ajw0100 »

swalsh wrote: post #48
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'm just biting my time until someone makes a "Daily WTF" website for monster queries. My worst was this sync that took data from one database, and put it into another one. The thing though is the source database was a CRM system that is on its 3rd iteration. So there's a million random fields from old systems that have accumulated over the years that need to be checked.

http://oracle-wtf.blogspot.com/

This might have been what you're looking for if it was still updated. Still some funny stuff there though.

Post reply on HN