GROUP BY, UNION ALL, etc. incompatible with "river" about sqlstyle.guide HOT 1 CLOSED

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.

from sqlstyle.guide.