Hacker News Reader

Comment by jethkl | Hacker News Reader

The article omits the "natural join". A natural join automatically joins tables on columns having the same name, and the columns in the resulting output appear exactly once. Natural joins often simplify queries, and they automatically adapt to schema changes in source tables. Natural joins also encourage standardized naming conventions, which has longer-term benefits.

View on HN

Natural joins also automatically break your queries when two columns happen to share a name but not the same meaning.

Step 1: use natural join. Life is great. Step 2: someone adds a `comment` field on table A. Life is great. Step 3: someone adds a `comment` field on table B. Ruh roh.

I'll use them in short-lived personal projects, but not on something where I'm collaborating with other people on software that evolves over several years.

a defense against collisions like this is through CTEs that select a minimal set of columns, with column names suitably selected and standardized:

  CTE_A AS (SELECT ... comment as comment_a from A...),
  CTE_B AS (SELECT ... comment as comment_b from B...)

Isn't this a lot more work both for the user and the RDBMS than just using a relatively simple left join?

It seems like the database should be able to figure this out when a foreign key constraint is explicitly declared in the DDL.

Should, but in practice I've rarely seen fk contraints used in analytics data warehouses (mostly for etl performance reasons)

I feel like natural joins simplify writing queries, but not exactly reading them (especially if you are not familiar with the database). IMO a good compromise is the USING clause, which acts like a natural join, but columns have to be named explicitly.

Maybe some SQL server should require a token like “USING_WILD_GUESS” when doing a natural join :)

The problem with USING is that it isn't supported everywhere.

> Natural joins also encourage standardized naming conventions, which has longer-term benefits.

This is a very positive spin on "you have to manage column names very rigorously for this strategy to be sustainable".

That’s not a join type, that’s syntactic sugar.

A right join is also syntactic sugar for a left join

Exactly, that's why they shouldn't be used. If you feel the need to use a right join, swap the direction around. And only use inner joins if it is the only type of join you use, otherwise specify conditions in the where clause. Both serve to significantly lower mental overhead when dealing with queries.

Natural joins are naturally more implicit, and while SQL tends to be a little bit verbose, given the significance of data integrity and the dififculty of testing SQL, the trade-off goes very clearly towards being explicit.

> the columns in the resulting output appear exactly once

No, records with the same join-column value are multiplied.

Reply on news.ycombinator.com