GROUP BY, UNION ALL, etc. incompatible with "river"

[emlazzarin]

  SELECT one,
         two,
         three,
         five
    FROM some_table
         INNER JOIN (SELECT five,
                            six,
                            seven
                       FROM another_table
                      WHERE field = 'special'
                   GROUP BY 1

                  UNION ALL

                     SELECT five,
                            eight,
                            nine
                       FROM third_table
                      WHERE something > 2
                   GROUP BY 1)
         USING (five)
   WHERE field = 'default'
GROUP BY 1, 2

Please see the inner GROUP BY statements and the UNION ALL: is this the recommended style? It preserves the river, but at the cost of the GROUP BY and UNION ALL "overflowing" left of the open parentheses, violating the alignment. One possible fix is to add space to the left side of the subquery:

  SELECT one,
         two,
         three,
         five
    FROM some_table
         INNER JOIN (   SELECT five,
                               six,
                               seven
                          FROM another_table
                         WHERE field = 'special'
                      GROUP BY 1

                     UNION ALL

                        SELECT five,
                               eight,
                               nine
                          FROM third_table
                         WHERE something > 2
                      GROUP BY 1)
         USING (five)
   WHERE field = 'default'
GROUP BY 1, 2

Another fix is to simply move the entire subquery left, breaking alignment with the parentheses:

  SELECT one,
         two,
         three,
         five
    FROM some_table
         INNER JOIN (
             SELECT five,
                    six,
                    seven
               FROM another_table
              WHERE field = 'special'
           GROUP BY 1

          UNION ALL

             SELECT five,
                    eight,
                    nine
               FROM third_table
              WHERE something > 2
           GROUP BY 1)
         USING (five)
   WHERE field = 'default'
GROUP BY 1, 2

[treffynnon]

I would tend to lay it out so that the GROUP keyword is west of the river and BY is east of the river. Having just reviewed similar queries in Celko's books again this is the style he uses as well. In fact I think I might add some information to the guide to document GROUP BY.

So using some of your example code we could lay it out like so:

SELECT one,
       two,
       three,
       five
  FROM some_table
 WHERE field = 'default'
 GROUP BY 1, 2

I like this because GROUP is really enough to spot that particular part of the statement when scanning to the west of the river as you read a query.

Now onto your other issue with UNION or UNION ALL; I tend to treat these as exceptions (like jokers in a pack of playing cards) as they're outside the query in question generally. I put them on a new line with clear whitespace surrounding them so it is quick and easy to see where the queries end and begin. I've used the shortened version of your code from above to illustrate this below:

(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)

UNION ALL

(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)

Celko does put them on their own line and he ignores the river for a UNION ALL, but he dispenses with the extra surrounding whitespace lines I have included above. So for the above query he might write:

(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)
UNION ALL
(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)

I find the UNION ALL a little jarring in this final example as it ignores the river without any leading or following whitespace to help negate the effect of non-conformity.

A further option would be to take the lead of the GROUP BY we discussed earlier:

(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)

  UNION ALL

(SELECT one,
        two,
        three,
        five
   FROM some_table
  WHERE field = 'default'
  GROUP BY 1, 2)

This is probably the one that makes the most sense given that the keyword is really UNION and ALL is but a modifier to it.

I am thinking that adding a few, more complicated, SQL examples to the guide might help to cover off some of these more advanced usages and their optimal layout.