How I write SQL • Hacker News Board

Re: How I write SQL

#71

Post by ebbv » Wed, May 01, 2013, 7:03 PM UTC

cptskippy wrote: post #64

ebbv wrote: post #49

Earlier quoted context omitted.

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.

Again, you can comment out lines with the normal syntax too. There's no reason for this gimmick syntax. In theory it makes the last 2 elements easier to deal with but it makes the first two harder. It's silly, and it's ugly.

Re: How I write SQL

#72

Post by btilly » Wed, May 01, 2013, 7:08 PM UTC

prawks wrote: post #43

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

My rule is that if I'm mixing ANDs and ORs in a WHERE, I use parentheses and extra indentation.

Not everyone knows that AND binds more tightly. Nor should they have to.

Re: How I write SQL

#73

Post by larsolefson » Wed, May 01, 2013, 9:04 PM UTC

btilly wrote: post #41

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…

Perhaps I didn't explain myself well. I also noticed that my spacing got messed up. Each SQL command (create, update, insert, select, from, join, on, and, where, group by, etc...) is on its own line, followed by the portion relevant to it. My SQL is still formated, but the preference is towards putting each "section" of the statement on its own line, rather than on separate lines.

For example (i've added \ as line break in case those get lost again):

select \ a., \ b.column1, \ b.column2, \ b.column3 \ from \ my_table a \ left join \ my_other_table b \ on \ a.col = b.col \ and \ a.col2 = b.col2 \ where \ a.col = condition \ group by \ column1, column2 \ ;

vs.

select a., b.column1, b.column2, b.column3 \ from my_table a \ left join my_other_table b \ on a.col = b.col \ and a.col2 = b.col2 \ where a.col = condition \ group by column1, column2 \ ;

I'll take the 2nd approach any day, particularly when I have statements above and below that reference that statement, because I can more easily understand the context of the entire script. I also prefer this, because in my mind, there is greater continuity, my_table is related to from, so it makes sense that it should follow it. I read left to right, and don't need to move down to a new line.

Re: How I write SQL

#74

Post by btilly » Wed, May 01, 2013, 9:18 PM UTC

larsolefson wrote: post #73

btilly wrote: post #41

Earlier quoted context omitted.

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…

Perhaps I didn't explain myself well. I also noticed that my spacing got messed up. Each SQL command (create, update, insert, select, from, join, on, and, where, group by, etc...) is on its own line, followed by the portion relevant to it. My SQL is still formated, but the preference is towards putting each "section" of the statement on its own line, rather than on separate lines. For example (i've added \ as line br…

That is more reasonable than what I thought you were saying.

However what happens when your list of columns is long? What happens if you want to include a CASE statement in a field?

I use vim and personally solve the scrolling problem with :split. This is particularly important in making sure that the SELECT and GROUP BY match up. (I am perpetually annoyed that the GROUP BY is not inferred from the SELECT. Unfortunately multiple databases have invented different inconsistent behavior for missing stuff in a GROUP BY, so there is absolutely no possibility of getting agreement on the convenient default of grouping on all non-aggregate functions that appear in the SELECT and HAVING clauses.)

Re: How I write SQL

#75

Post by fusiongyro » Wed, May 01, 2013, 9:24 PM UTC

AlisdairO wrote: post #38

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 would tend to normalise arrays too, but I can see the argument that when getting an 'app' and turning it into an object in $programming_language (say), it's rather a pain to have to go through the sql result set building up an object from the denormalised data (and arguably results in a lot of wasted data being transmitted). It gets particularly bad if you have more than one 1-many relationship on the object you're…

If you're using an ORM, and you almost certainly are, it's equal to all the other work you do to map your objects. If you're querying directly, array_agg on the way out is the same work as unnest is on the way in. You can have your cake and eat it to. Tagging has been discussed a lot recently as a good excuse to use arrays in Postgres, but since the whole point of tagging is to enhance searching I think it really misses the point to do it this way. It appeals to our intuitions only because we think of rows as being large and string arrays as being small, but in practice this sort of intuitive "optimization" is almost always wrong.

Re: How I write SQL

#76

Post by Someone » Wed, May 01, 2013, 10:22 PM UTC

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 the same in C++:

    myClass::myClass(int aa,int bb)
       : a(aa)
       , b(bb)
       , p(new int[aa])
       , c(17)
    {}

Yes, that looks weird at first, but you get used to it (and, to be honest, everything in C looks weird at first).

Re: How I write SQL

#77

Post by AlisdairO » Wed, May 01, 2013, 10:44 PM UTC

fusiongyro wrote: post #75

AlisdairO wrote: post #38

Earlier quoted context omitted.

I would tend to normalise arrays too, but I can see the argument that when getting an 'app' and turning it into an object in $programming_language (say), it's rather a pain to have to go through the sql result set building up an object from the denormalised data (and arguably results in a lot of wasted data being transmitted). It gets particularly bad if you have more than one 1-many relationship on the object you're…

If you're using an ORM, and you almost certainly are, it's equal to all the other work you do to map your objects. If you're querying directly, array_agg on the way out is the same work as unnest is on the way in. You can have your cake and eat it to. Tagging has been discussed a lot recently as a good excuse to use arrays in Postgres, but since the whole point of tagging is to enhance searching I think it really mis…

> If you're using an ORM, and you almost certainly are

I'm not a web developer - I spend more of my time in raw-sql-land, so this discussion is largely academic to me :-). That said, even if you're using an ORM, it's presumably not exactly free for the ORM to produce an object out of the thousands of distinct rows you can end up getting if you have multiple one to many relationships.

In general, the availability of arrays for the purposes of data transport - less so actual storage - in postgres is rather helpful for these sort of problems. I find working with other DBs to do object storage quite constricting by comparison.

Re: How I write SQL

#78

Post by dennisgorelik » Thu, May 02, 2013, 12:22 AM UTC

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…

Lowercase for table names? I think table names should be in PascalCase.

And I personally prefer the rest of SQL to be in lowercase, so it does not attract too much attention. Usually in SQL table names and column names is the most important information.

Re: How I write SQL

#79

Post by larsolefson » Thu, May 02, 2013, 12:34 AM UTC

btilly wrote: post #74

larsolefson wrote: post #73

Earlier quoted context omitted.

Perhaps I didn't explain myself well. I also noticed that my spacing got messed up. Each SQL command (create, update, insert, select, from, join, on, and, where, group by, etc...) is on its own line, followed by the portion relevant to it. My SQL is still formated, but the preference is towards putting each "section" of the statement on its own line, rather than on separate lines. For example (i've added \ as line br…

That is more reasonable than what I thought you were saying. However what happens when your list of columns is long? What happens if you want to include a CASE statement in a field? I use vim and personally solve the scrolling problem with :split. This is particularly important in making sure that the SELECT and GROUP BY match up. (I am perpetually annoyed that the GROUP BY is not inferred from the SELECT. Unfortunat…

Sorry for the confusion.

When my list of columns is long, I split it up into multiple lines, usually 7-8 per line (where I work, column names are capped at 30 chars, hence the 200 character estimate), unless it is a case statement. In case statements, each case/when clause get its own line, so you'll have:

case when condition then result \ when condition_2 then result_2 \ ...\ when condition_n then result_n end as column_name \

All of my queries and statements follow the same logic and set of rules, I just prefer a more compact view than most it seems (which could indicate that I'm optimizing for a different set of constraints/preferences).

I'll have to look into using :split.

Honestly I don't think about formatting that much anymore, as I've started storing most of my statements as "metrics" which can be easily repurposed for use in another script. Each metric contains the name of the metric, the columns added, the metric source (usually a table, but can also be a select stmt), the possible join conditions, extra SQL like where, group by, qualify, and any indices. When I want to use that metric in another script, I can do so by specifying the metric name, the table or select statement I'd like append the metric's columns to, and my join conditions. It works great for simple to moderately complex queries, and provides you a good starting point for really complex queries. Plus, it auto-formats everything how I like it. I also use comments to explain what the statement is doing, so someone theoretically should be able to get a pretty good idea of whats going just by reading that.

I really only think about formatting when I'm reading others code and trying to make sense of it, which is where I find the the formatting like the author mentioned most annoying/frustrating (perhaps because it's different than how I think/do things?).

Re: How I write SQL

#80

Post by btilly » Thu, May 02, 2013, 1:52 AM UTC

larsolefson wrote: post #79

btilly wrote: post #74

Earlier quoted context omitted.

That is more reasonable than what I thought you were saying. However what happens when your list of columns is long? What happens if you want to include a CASE statement in a field? I use vim and personally solve the scrolling problem with :split. This is particularly important in making sure that the SELECT and GROUP BY match up. (I am perpetually annoyed that the GROUP BY is not inferred from the SELECT. Unfortunat…

Sorry for the confusion. When my list of columns is long, I split it up into multiple lines, usually 7-8 per line (where I work, column names are capped at 30 chars, hence the 200 character estimate), unless it is a case statement. In case statements, each case/when clause get its own line, so you'll have: case when condition then result \ when condition_2 then result_2 \ ...\ when condition_n then result_n end as co…

My personal experience when I tried to clump things together is that I lost track of what columns were where. My eyes scan vertically really fast. And once I was used to it for large queries, well, what works for large queries works for small as well if you're used to it.

Honestly I don't think about formatting that much anymore, as I've started storing most of my statements as "metrics" which can be easily repurposed for use in another script. Each metric contains the name of the metric, the columns added, the metric source (usually a table, but can also be a select stmt), the possible join conditions, extra SQL like where, group by, qualify, and any indices. When I want to use that metric in another script, I can do so by specifying the metric name, the table or select statement I'd like append the metric's columns to, and my join conditions. It works great for simple to moderately complex queries, and provides you a good starting point for really complex queries. Plus, it auto-formats everything how I like it. I also use comments to explain what the statement is doing, so someone theoretically should be able to get a pretty good idea of whats going just by reading that.

I do something different that you might like to borrow. If you're working in a database with real tmp tables (PostgreSQL, MySQL and MS SQL all do, Oracle does not) you can control execution plans for complex queries by creating a series of tmp tables in order, adding indexes if you want, then joining them down the chain. I initially did this for performance reasons, but quickly found that I could easily reuse the logic to create specific tmp tables in different places, and that a series of tmp tables was much more readable than complex subqueries.

You may wonder why I specifically dissed Oracle, after all they claim to have tmp tables. They do..but all tmp table definitions are in a global namespace that requires special permissions to write to, and are shared across all database handles. All of the other databases that I named allow you to create/destroy them on the fly without special permissions. And do not have an issue if two database handles want to use the same tmp table name with different definitions.

This opens you up for all sorts of nasty interaction problems. And increases the cognitive overhead of the technique to the point where it becomes not worthwhile.