Hacker News Reader

Comment by extr | Hacker News Reader

Yes! I write SQL this way. I grant the comma thing is more a matter of taste, but aligning clauses vertically and maximizing rivers is super useful to me and it forever mystifies me that it isn't standard. This is an extreme example, but I like to keep join clauses vertically aligned as well:

    select a
         , b
         , count(*) as count
      from table
      join table2 on table.key1                    = table2.key1
                 and table.key2                    = table2.key2
                 and DATEADD('day', 1, table.key3) = table2.key3
     where cond1
       and cond2
     group by a, b
     order by count desc

For me this lets me read the SQL query almost like a picture at first glance, identifying blocks of rivers as semantically connected groups of statements. Some SQL formatters out there love to put join clauses at the same indentation level as the join itself, drives me nuts.

View on HN

This looks nearly the same, but it's much easier to write and generates less spurious diffs:

    select a
        , b
        , count(*) as count
    from table
    join table2
        on table.key1 = table2.key1
        and table.key2 = table2.key2
        and DATEADD('day, 1, table.key3) = table2.key3
    where cond1
        and cond2
    group by a, b
    order by count desc

The largest difference is that the equal operators aren't aligned anymore, but on my opinion, aligning them is really not worth the cost.

Nice changes, I like it! I will say I really like aligning the join clauses, but I'll try writing things this way for a little while and see if it sticks.

The only thing I require from an SQL formatter is to make typos and boolean mistakes super easy to spot, especially as it grows in size. At first glance it might seem strange, but I like indentation on even the first term of an AND.

    SELECT
        a,
        b,
        count(*) as count
    FROM
        table
    JOIN
        table2
    ON
            table.key1 = table2.key1
        AND
            table.key2 = table2.key2
        AND
            -- 1 day apart
            DATEADD('day', 1, table.key3) = table2.key3
    WHERE
            cond1
        AND
            cond2
        AND
            (
                cond3
            OR
                cond4
            )
    GROUP BY
        (a, b)
    ORDER BY
        count desc,
        a asc
    LIMIT
        1000
    ;

Yes, that's a lot of whitespace, but it helps for complex queries.

Well, there's an inherent conflict between making individual queries easier to read and making your entire code easier to read.

How much whitespace you need is completely dependent on what exactly you are writing.

Do you format your code this way by hand or does your IDE help you? Also, I can only imagine how noisy your diffs would be when you make changes and have to indent everything.

I'll use PyCharm's SQL formatter sometimes to massage spaghetti code that someone else wrote, but mostly I just write it this way by hand.

I will say my use case is probably different than others, I'm a Data Scientist so I write a ton of throwaway SQL and stuff that will only be seen by me. If I'm dealing with an existing SQL codebase or production stuff, definitely try to keep it more easily diff-able (like left justifying the keywords, really only caring about rivers in the join/where clauses), or just follow whatever patterns are already there.

> it forever mystifies me that it isn't standard

I can give a few thoughts on why here:

- it doesn't work for collaborative efforts unless everyone is using the same linter or IDE with the same configuration. Not friendly to edits outside of that ecosystem (e.g. open source). - there are other ways of writing SQL (e.g. dbt style guide) which are still very readable while also being much faster to write by hand without the need for an auto-formatter. - some of these patterns such as the from and join indentations have nothing to do with readability and are purely stylistic. Nothing wrong with that, but there's nothing objectively better about this pattern - it just comes down to how you're used to reading SQL.

> and it forever mystifies me that it isn't standard

I can take a stab at justifying that - commas are used to form lists (eg, 1, 2, 3) and text (like "order by count") are supposed to be left justified.

You may be right that there are good reasons to format SQL while ignoring the commas and rules for writing English text. I personally think you are right. But if most people believed that then there'd be enough support to rewrite SQL with a decent modern syntax. Say allowing training commas before a from.

So the existence of SQL is, in a weird way, evidence that your view is uncommon.

That formatting makes my eyes bleed.

Reply on news.ycombinator.com