IMO in the modern day there is no place for any indentation styling that can't be achieved automatically via a pretty printer such as golang has.

Also, could uppercase go away and never come back? Please?

Agreed. Fwiw, Mozilla’s style guide prohibits rivers like this.

https://docs.telemetry.mozilla.org/concepts/sql_style

I find splitting out over lines like that harder to read because the table-like columns now overlap with each other and aren't aligned with the keyword they belong to.

A week before being laid off last month, I solved a decade+ old open problem at our company which first occurred since Django doesn't natively support CTE's, leading to years of technical debt from the ersatz sql/query plans produced by our fragile queries.

I ended up manually overloading get_extra_restriction on a custom ForeignKey class (we couldn't use FilteredRelation b/c we were still on django 1.11), which ensured that the JOIN ON ... clause limited the tables being joined to their correct partition/schema while being accessed through a view

The view thing is a long story — it was a legacy PAC codebase from the '90s which used 13+ schemas in a mysql db that was then being synced to our postgres db through Amazon DMS. All of the tables on each view contain identical source_schema/CompanyID columns, hence the

    '%(remote_alias)s. "source_schema" = %(fk_alias)s. "source_schema" AND '
    '%(remote_alias)s. "CompanyID" = %(fk_alias)s. "CompanyID"

before/after query plan in depesz: https://imgur.com/a/HQbNSIL

CTEs would be such a blessing. I am stuck using mysql 5.6. So many queries would just get simpler.

This is a much better style guide in my opinion.

It’s still highly readable but also much much easier to write and modify.

Though I am biased because it’s also how I used to write SQL back when PL/SQL was my day job. Albeit I fell into this design because it proved to be the easiest way to write and maintain readable code.

Thanks for sharing this!

It looks so much cleaner in my eyes.

Plus it uses constant-sized indents, which means less futzing about with spaces and all that.

Also means you can comment out the first select item, something you can't do with the article's approach.

I am definitely not a fan of that style. Wastes too much vertical space without much benefit.

I think this guide misses the point that “JOIN” is not a root keyword but a modification on “FROM”. It is more akin to logical “AND”, “OR”, etc.

And this stacks much better once you start doing complex joins especially when you can add parentheses to change where you actually join

    FROM a JOIN b JOIN c

    FROM a JOIN (b JOIN C)

Back In The Day at uni in the early 2000s, we were taught that table names should always be singular, and that's mostly what I've seen in the real world since.

I also think the advice around join table naming is a bit silly. Calling a join table between `cars` and `mechanics` `services` (rather than `cars_mechanics`) does not make the relationship clearer, and only works when the relationships maps to a the real world where the relationship has a commonly used name.

The more I read of this guide, the uglier the SQL is.

You can always alias to a singular … like

join users as user on user….

Then do as you please without the that if you are dealing with a user or leave it plural if multiple…

And if we’re talking personal preference I really dislike caps in reserved words in sql, even before highlighting was everywhere it still just feels archaic for no good reason

You are not alone at all, I also prefer singular names for the same reason. I reserve plural names for the rare cases where the single row of a table actually contains information about more than one item, which is usually when I'm doing something denormalized or non-relational e.g. CREATE TABLE user_settings ( user_id INT, settings_data JSON)

You left out the where.

SELECT employee.name where role = 'developer'

Vs

SELECT staff.name” where role = 'developer'

Then the plural one reads better

According to the guide it would be e.first_name or s.first_name.

Yes, you are indeed alone in this

[dead]

Old school SQL gurus will tell you the ORM is wrong and just to override it. It breaks USING

IMHO, not all good. In this style guide, the person says to "SELECT, FROM, etc. are all right aligned while the actual column names and implementation-specific details are left aligned."

Seems like a lot of work to me. And I don't know any formatter that'd do that work.

> Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used However, using 'table_name.table_name_id' and then having another table with an FK that references it with the same name i.e. 'table_2.table_name_id' allows you to use a shorthand 'USING' clause instead of 'ON' in databases that support it.

I have found that naming ids as <thing>_id helps downstream code when trying to figure out which thing's id you are dealing with. It also helps with avoiding renaming fields when a structure contains multiple ids.

I do agree it makes joins more verbose.

If the table names are all short one word like that, they are already basically aliases. What do you do when you end up on some legacy project (you did not make the schema) where_the_table_names_look_like_this? Seems some sort of alias might be more appropriate. Also, when you are writing longer / sub queries or using Common Table Expressions it’s impossible to not use an alias.

Agree. In my experience people use aliases to use aliases. They know them but don't understand them.

Aliases are the SQL equivalent of ternary operators. Are you trying to write clean code or are you trying to express to other people you know things?

How do you know which table first_name came from?

Also aliasing can be as much for legibility as anything.

  SELECT
      ZZ.first_name
  FROM staff AS ZZ
  INNER JOIN students AS Q ON Q.mentor_id = ZZ.staff_num;

SQL keywords have been upper case for decades. I prefer it because it is faster to match visually.

Just like I don’t like uppercase paragraphs because of the same reason

[dead]