4 ms·
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. usi
by barrkel 10y ago
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.
- Stratoscope 10y ago> 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. I'm definitely with you there. I've written about the disadvantages of this kind of column alignment several times on HN, so won't repeat them here. (Well, if anyone asks, I'll dig up a specific comment or two.) I have a suggestion that can help anyone cure the temptation to column align all the things: Try coding in a proportional font for a while. Contrary to popular wisdom, proportional fonts work fine for most kinds of programming. The only thing you can't do in them is column alignment, and in most cases this limitation is a good thing. Instead of column alignment, you'll be forced to use indentation, and you may discover that indentation alone gives you enough ways to make your code structure clear without arbitrary alignment. In my case, it happened the other way around. I got tired of the problems that column alignment caused, so I stopped using it and switched to indentation only. Some time later I was curious and tried a proportional font, and the code was still just as readable as before. So now I use proportional fonts all the time, and I'm no longer tempted to use column alignment.