8 ms·

How I write SQL

51 points by rachbelaid 13y ago

btilly 13y ago
My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out. See http://bentilly.blogspot.com/2011/02/sql-formatting-style.html http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.
- Svip 13y ago
  You also write FROM/JOIN/ON indentations like I do.
- radiowave 13y ago
  I put the commas in front as well, but the reason I started doing it that way is that you can then comment out (and uncomment) any column without breaking the query.
- r00fus 13y ago
  I use this formatting style as well - it's also easier to format as the columns can all be aligned with tabs.
Svip 13y ago
I don't write SQL like that. He has a good point about using a line for each and/or condition in the where clause and starting with either one (easier to remove). But I don't think all queries should be written in several lines. If a query is simple enough (e.g. no aggregation, group by, having and at maximum one where condition and few fields to select), it can be written in one line, like: SELECT field FROM table WHERE value = @value That's easy and straightforward to understand and readable (which was initially part of SQL's goals, although we can discuss at a later date how successful that was). Complicated SQL queries - which are bound to happen - follow - in my style - a more straightforward approach: There are new lines after every grouping (FROM, WHERE, GROUP BY, SORT BY, HAVING, etc.) and indenting. In fact, I prefer to indent my fields twice, because I consider FROM, WHERE, etc. to be subject to SELECT (even if part of an INSERT). I also tend to avoid extra keywords such as 'OUTER' in 'LEFT OUTER JOIN' (there is no such thing as 'LEFT INNER JOIN'). And then I follow each JOIN statement (indented compared to the ground table in from) by the table name, a new line, an indent and the ON condition. If another table relies on that table being joined, I indent it further to indicate that, so I can quickly look at my code to see what tables goes through which (which is useful if you have a lot of relationship tables). Should be noted, however, I most commonly write towards Sybase at work, which tends to have no preference - style wise - on whether you write the keywords in upper case. My co-workers do not, so I don't either. But when I write my own projects against MySQL/MariaDB, I use upper case. But I generally follow the same style.
- jmelloy 13y ago
  If I'm doing a simple query, I still structure it as select blah, blha, blah from dual where ... I find that even for short ones, lines quickly get very long and illegiable if you're shmushing them all onto one line.
unoti 13y ago
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 ...
- ebbv 13y ago
  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.
  - hdctambien 13y ago
    Why is "after the element" the proper place for a comma?
    - noblethrasher 13y ago
      It's probably more pleasent and easier to read for the next person.
    - rtfeldman 13y ago
      That's where it goes in English.
    - jkeel 13y ago
      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.
    - hdctambien 13y ago
      (I can't reply to my reply) Comma first seems more consistent to me.... unless you write your WHERE clauses like this: WHERE a=b AND a=d AND e=f
mjt0229 13y ago
I have developed a very similar style, for the same reasons. It's funny to see this blog post, because I have gobs of SQL just like it on my other monitor.
rachbelaid 13y ago
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?
- bigdubs 13y ago
  [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.
  - rachbelaid 13y ago
    Thanks for the details.
  - NDizzle 13y ago
    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.
- bsg75 13y ago
  Its part of the ANSI SQL-99 standard: http://en.wikipedia.org/wiki/SQL:1999 http://en.wikipedia.org/wiki/SQL:1999
larsolefson 13y ago
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.
leeoniya 13y ago
i recently dealt with a lot of this while discussing/resolving some of these issues @ https://github.com/jdorn/sql-formatter https://github.com/jdorn/sql-formatter
jmduke 13y ago
I'm not a big fan of this style. It's technically 'organized' (in the sense that there's a definite consistency), but it's vertical to the point of reduced readability: trivial queries can take up dozens of lines.
- kbenson 13y ago
  > trivial queries can take up dozens of lines. My general rule-of-thumb is that trivial things can have syntax constraints relaxed, as long as it makes them more readable, not less. I may adopt the style of the article for a complex query, but for something simpler I may go with: SELECT foo, bar, baz FROM my_table WHERE foo=1 AND bar > 10 GROUP BY bar ORDER BY baz But that becomes less readable after more than a couple fields or conditions for each line. Until that point, I think it's fairly concise and efficient. That said, if your SQL is intermixed with code whose sole purpose isn't to deal with that SQL, you are probably in for hurt later on anyway. If it is safely quarantined into data access routines of some sort, the size doesn't really matter, since the function or method containing the query should really be about that query.
fusiongyro 13y ago
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 tend to line up my joins like so: FROM foo JOIN bar ON b.foo_id = f.id LEFT JOIN baz ON ...
- umsm 13y ago
  I almost agree with you, but I put all reserved words (including ON) on a new line.
- AlisdairO 13y ago
  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 trying to build up.
  - fusiongyro 13y ago
    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.
    - AlisdairO 13y ago
      > 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.
xpose2000 13y ago
I follow a similar style as well.... One of the things I love about SQLyog is that it has a 'format query' button that will make any query readable. Its a huge help when analyzing bad queries that newrelic might spit out.
tragomaskhalos 13y ago
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.
- bcgraham 13y ago
  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.
dmourati 13y ago
Without further ado. http://grammar.quickanddirtytips.com/ado-versus-adieu.aspx http://grammar.quickanddirtytips.com/ado-versus-adieu.aspx
NicolasBeuzeboc 13y ago
Postgres WITH SELECT ... is very handy but in that case I would create three views instead. It seems that wine_ratings tags and detail could be used in other queries and having the views created would prevent duplicating their code.
joulethief 13y ago
I wonder why so many people hate writing sql with such a poisonous intensity these days. I remember my database-systems class being one of my favorite classes when I was in school. Whats wrong with relational algebra and writing sql? I always thought they were fun.
tom_b 13y ago
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 protocol_code, application_code, description, lab, proj."seqno", proj."project", proj."protocol", 'false' "nexttag", 'true' "materialtag" from protocol, xmltable('for $a in /* for $n at $nidx in //*/MATERIAL return <result> <project>{fn:data($n/@project)}</project> <protocol>{fn:data($n/@protocol)} </protocol> </result>' passing xmltype(protocol.xml) columns "seqno" for ordinality, "project" varchar2(100), "protocol" varchar2(100)) proj where active = 'Y' union select protocol_code, application_code, description, lab, proj."seqno", proj."project", proj."protocol", 'true' "nexttag", 'false' "materialtag" from protocol, xmltable('for $a in /* for $n at $nidx in //*/NEXT return <result> <project>{fn:data($n/@project)}</project> <protocol>{fn:data($n/@protocol)}</protocol> </result>' passing xmltype(protocol.xml) columns "seqno" for ordinality, "project" varchar2(100), "protocol" varchar2(100)) proj where active='Y' order by 1,2,4 ) select * from (select distinct protocol_code, sys_connect_by_path(protocol_code,'||||'), connect_by_isleaf "isleaf" from bgu where protocol_code like 'PROTNAME%' connect by nocycle prior "protocol" = protocol_code) where "isleaf"=1
- ajw0100 13y ago
  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.
- swalsh 13y ago
  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.
prawks 13y ago
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.
- btilly 13y ago
  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.
mast 13y ago
I don't often deal with SQL and when I do, it is usually quite simple. But, I think there is actually an error in the first step: SELECT avg(rating), wine_id FROM app_wine GROUP BY wine_id; The query should be against the app_rating table. It does get corrected later on, but I was confused for a while.
rosser 13y ago
Please, please, please don't perform your JOINs with comma-separated items in the FROM clause. As soon as you need also to do an OUTER JOIN, you're going to get unexpected results. Per the SQL spec, explicit JOIN-style syntax binds more tightly than FROM-list elements, which can not only constrain the query planner in the choices it can make, but can actually yield a different result.
iblaine 13y ago
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.

51 points by rachbelaid 13y ago

My personal style has a lot of similarities, but with some glaring differences. The biggest is that I put the comma in front of the next item, rather than trailing the one before. What this means is that when I add a new thing to the list of columns, I'm less likely to leave one out. See http://bentilly.blogspot.com/2011/02/sql-formatting-style.html http://bentilly.blogspot.com/2011/02/sql-formatting-style.ht... for what this looks like in practice.

Svip 13y ago
You also write FROM/JOIN/ON indentations like I do.
radiowave 13y ago
I put the commas in front as well, but the reason I started doing it that way is that you can then comment out (and uncomment) any column without breaking the query.
r00fus 13y ago
I use this formatting style as well - it's also easier to format as the columns can all be aligned with tabs.

Svip 13y ago

I don't write SQL like that. He has a good point about using a line for each and/or condition in the where clause and starting with either one (easier to remove). But I don't think all queries should be written in several lines. If a query is simple enough (e.g. no aggregation, group by, having and at maximum one where condition and few fields to select), it can be written in one line, like: SELECT field FROM table WHERE value = @value That's easy and straightforward to understand and readable (which was initially part of SQL's goals, although we can discuss at a later date how successful that was). Complicated SQL queries - which are bound to happen - follow - in my style - a more straightforward approach: There are new lines after every grouping (FROM, WHERE, GROUP BY, SORT BY, HAVING, etc.) and indenting. In fact, I prefer to indent my fields twice, because I consider FROM, WHERE, etc. to be subject to SELECT (even if part of an INSERT). I also tend to avoid extra keywords such as 'OUTER' in 'LEFT OUTER JOIN' (there is no such thing as 'LEFT INNER JOIN'). And then I follow each JOIN statement (indented compared to the ground table in from) by the table name, a new line, an indent and the ON condition. If another table relies on that table being joined, I indent it further to indicate that, so I can quickly look at my code to see what tables goes through which (which is useful if you have a lot of relationship tables). Should be noted, however, I most commonly write towards Sybase at work, which tends to have no preference - style wise - on whether you write the keywords in upper case. My co-workers do not, so I don't either. But when I write my own projects against MySQL/MariaDB, I use upper case. But I generally follow the same style.

jmelloy 13y ago
If I'm doing a simple query, I still structure it as select blah, blha, blah from dual where ... I find that even for short ones, lines quickly get very long and illegiable if you're shmushing them all onto one line.

unoti 13y ago

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

ebbv 13y ago
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.
- hdctambien 13y ago
  Why is "after the element" the proper place for a comma?
  - noblethrasher 13y ago
    It's probably more pleasent and easier to read for the next person.
  - rtfeldman 13y ago
    That's where it goes in English.
  - jkeel 13y ago
    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.
  - hdctambien 13y ago
    (I can't reply to my reply) Comma first seems more consistent to me.... unless you write your WHERE clauses like this: WHERE a=b AND a=d AND e=f

mjt0229 13y ago

I have developed a very similar style, for the same reasons. It's funny to see this blog post, because I have gobs of SQL just like it on my other monitor.

rachbelaid 13y ago

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?

bigdubs 13y ago
[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.
- rachbelaid 13y ago
  Thanks for the details.
- NDizzle 13y ago
  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.
bsg75 13y ago
Its part of the ANSI SQL-99 standard: http://en.wikipedia.org/wiki/SQL:1999 http://en.wikipedia.org/wiki/SQL:1999

larsolefson 13y ago

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.

leeoniya 13y ago

i recently dealt with a lot of this while discussing/resolving some of these issues @ https://github.com/jdorn/sql-formatter https://github.com/jdorn/sql-formatter

jmduke 13y ago

I'm not a big fan of this style. It's technically 'organized' (in the sense that there's a definite consistency), but it's vertical to the point of reduced readability: trivial queries can take up dozens of lines.

kbenson 13y ago
> trivial queries can take up dozens of lines. My general rule-of-thumb is that trivial things can have syntax constraints relaxed, as long as it makes them more readable, not less. I may adopt the style of the article for a complex query, but for something simpler I may go with: SELECT foo, bar, baz FROM my_table WHERE foo=1 AND bar > 10 GROUP BY bar ORDER BY baz But that becomes less readable after more than a couple fields or conditions for each line. Until that point, I think it's fairly concise and efficient. That said, if your SQL is intermixed with code whose sole purpose isn't to deal with that SQL, you are probably in for hurt later on anyway. If it is safely quarantined into data access routines of some sort, the size doesn't really matter, since the function or method containing the query should really be about that query.

fusiongyro 13y ago

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 tend to line up my joins like so: FROM foo JOIN bar ON b.foo_id = f.id LEFT JOIN baz ON ...

umsm 13y ago
I almost agree with you, but I put all reserved words (including ON) on a new line.
AlisdairO 13y ago
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 trying to build up.
- fusiongyro 13y ago
  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.
  - AlisdairO 13y ago
    > 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.

xpose2000 13y ago

I follow a similar style as well.... One of the things I love about SQLyog is that it has a 'format query' button that will make any query readable. Its a huge help when analyzing bad queries that newrelic might spit out.

tragomaskhalos 13y ago

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.

bcgraham 13y ago
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.

dmourati 13y ago

Without further ado. http://grammar.quickanddirtytips.com/ado-versus-adieu.aspx http://grammar.quickanddirtytips.com/ado-versus-adieu.aspx

NicolasBeuzeboc 13y ago

Postgres WITH SELECT ... is very handy but in that case I would create three views instead. It seems that wine_ratings tags and detail could be used in other queries and having the views created would prevent duplicating their code.

joulethief 13y ago

I wonder why so many people hate writing sql with such a poisonous intensity these days. I remember my database-systems class being one of my favorite classes when I was in school. Whats wrong with relational algebra and writing sql? I always thought they were fun.

tom_b 13y ago

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 protocol_code, application_code, description, lab, proj."seqno", proj."project", proj."protocol", 'false' "nexttag", 'true' "materialtag" from protocol, xmltable('for $a in /* for $n at $nidx in //*/MATERIAL return <result> <project>{fn:data($n/@project)}</project> <protocol>{fn:data($n/@protocol)} </protocol> </result>' passing xmltype(protocol.xml) columns "seqno" for ordinality, "project" varchar2(100), "protocol" varchar2(100)) proj where active = 'Y' union select protocol_code, application_code, description, lab, proj."seqno", proj."project", proj."protocol", 'true' "nexttag", 'false' "materialtag" from protocol, xmltable('for $a in /* for $n at $nidx in //*/NEXT return <result> <project>{fn:data($n/@project)}</project> <protocol>{fn:data($n/@protocol)}</protocol> </result>' passing xmltype(protocol.xml) columns "seqno" for ordinality, "project" varchar2(100), "protocol" varchar2(100)) proj where active='Y' order by 1,2,4 ) select * from (select distinct protocol_code, sys_connect_by_path(protocol_code,'||||'), connect_by_isleaf "isleaf" from bgu where protocol_code like 'PROTNAME%' connect by nocycle prior "protocol" = protocol_code) where "isleaf"=1

ajw0100 13y ago
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.
swalsh 13y ago
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.

prawks 13y ago

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.

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

mast 13y ago

I don't often deal with SQL and when I do, it is usually quite simple. But, I think there is actually an error in the first step: SELECT avg(rating), wine_id FROM app_wine GROUP BY wine_id; The query should be against the app_rating table. It does get corrected later on, but I was confused for a while.

rosser 13y ago

Please, please, please don't perform your JOINs with comma-separated items in the FROM clause. As soon as you need also to do an OUTER JOIN, you're going to get unexpected results. Per the SQL spec, explicit JOIN-style syntax binds more tightly than FROM-list elements, which can not only constrain the query planner in the choices it can make, but can actually yield a different result.

iblaine 13y ago

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.