SQL style guide

Or avoid expensive, uncooperative, proprietary SQL vendors. I'll keep pretending and stick with sqlite and PLSQL.

If you're using Rails, your life will be much simpler if you do use an 'id' column, use plurals for table names, and name your join tables 'bars_foos', i.e. using the plural on both sides and in alphabetical order.

Don't really agree on indenting joins that are at the same level as FROM. There's not much semantic difference between the table in the FROM clause and one in a JOIN clause. Subqueries should be definitely be indented when nested.

Don't really agree with the leading whitespace alignment either. Alignment across lines is generally troublesome when combined with team development and version control: it leads to merge conflicts. If you start using 'GROUP BY' that forces you to introduce whitespace in your 'SELECT' line to bring everything into sync, then chances of a conflict increase quite a bit.

Don't 100% agree on using IN either, in particular if you're using a tuple membership check. That is, `JOIN x ON (x.a, x.b) IN ((1, 2), (2, 3), (4, 5))` certainly looks neater than the equivalent mix of ORs wrapped around ANDs, but it usually performs quite a bit worse because not many people write their SQL that way and the DB is not normally tuned for it.

A much bigger concern from my perspective (reviewing PRs from other developers) is writing SQL that will perform well for the database and data sizes. There are massive perf pitfalls around NOT EXISTS vs NOT IN, and it's easy to get horrific performance out of MySQL especially if using a subquery in the WHERE clause.

Structuring things as a join gets much more predictable performance, and structuring a set of joins as nested subqueries instead of joins at the same level (or using extra parentheses in Postgres) gives the SQL author more control over what will be done. Don't trust the optimizer to know best. It often doesn't and if it chooses wrong, it can hose a release. Measure, as always.

Ian Mackinnon, is that you? (http://stackoverflow.com/a/3894235/1163893)

Weird, I really dislike a lot of the suggestions. In particular, an example:

  SELECT first_name AS fn
    FROM staff AS s1
    JOIN students AS s2
      ON s2.mentor_id = s1.staff_num;

We already agreed that staff is a good name for a table, so why are we renaming it to s1? There are all sorts of subtle bugs that arise when s2.mentor_id = s1.staff_num is wrong, and the variable names provide no help here that we're doing things right.

How about:

  SELECT first_name
    FROM staff
    JOIN students
      ON student.staff_id = staff.id;

If you're reading some 50+ line SQL query with a bug, things like this are easy to read and be confident in. Honestly, I think AS should basically never be used for tables.

Likewise, the guide gives the example of naming join tables 'services' instead of 'car_mechanics'. But if I see a table 'car', a table 'mechanic', and a table 'car_mechanic', I instantly know how to join against this in any direction. The hard part of understanding a schema is how it fits together, not what it represents, and when you have hundreds of tables and need to remember that join table's name, it's really, really nice if it's trivial to derive.

Lastly, the guide's recommended indentation for FROM vs JOIN statements seems off to me. Consider:

  FROM riders AS r
         INNER JOIN bikes AS b
         ON r.bike_vin_num = b.vin_num
            AND b.engines > 2

Well, the thing is, the riders table and bikes table have the exact same priority in the query, but this spacing really emphasizes the riders table and it's not clear why.

I only slightly disagree with a couple of your points.

1) They aliased it to show how it should look. This is a contrived example, and you normally wouldn't alias these.

2) Aliasing tables is essential when you have join tables that end up being over 30 characters long, or you need to self join for something. Short of using CTEs (which aren't available everywhere), I don't know of a way to self join without using aliases.