5 ms·

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 t

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. Instead, I prefer to my SQL to be more compact, so I can view as much of the total script as possible. To accomplish this, and to maintain readability, I structure each statement something like this: CREATE/INSERT/UPDATE line SELECT line FROM line JOINS (if present) ON (if present) WHERE (if present) AND (if present) Additional SQL (qualify, group by, order by) ; If I have a lot of columns, the select portion will get split into several lines, usually where there is a case when, column operations, or when the line is 200ish characters long. This makes the most sense to me, as each command (create, update, insert, select, from, join, on, where, and, group by) is on its own line, followed by the information relevevant to it.

btilly 13y ago
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.
- larsolefson 13y ago
  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.
  - btilly 13y ago
    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.)
    - larsolefson 13y ago
      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?).

btilly 13y ago

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.

larsolefson 13y ago
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.
- btilly 13y ago
  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.)
  - larsolefson 13y ago
    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?).