11 ms·

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

by jethkl 3y ago

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.

youerbt 3y ago
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.
- amluto 3y ago
  Maybe some SQL server should require a token like “USING_WILD_GUESS” when doing a natural join :)
- chasil 3y ago
  The problem with USING is that it isn't supported everywhere.
hipadev23 3y ago
That’s not a join type, that’s syntactic sugar.
- bradleybuda 3y ago
  A right join is also syntactic sugar for a left join
  - bakuninsbart 3y ago
    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.
cldellow 3y ago
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.
- closeparen 3y ago
  It seems like the database should be able to figure this out when a foreign key constraint is explicitly declared in the DDL.
  - tqi 3y ago
    Should, but in practice I've rarely seen fk contraints used in analytics data warehouses (mostly for etl performance reasons)
- jethkl 3y ago
  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...)
  - Cyberdog 3y ago
    Isn't this a lot more work both for the user and the RDBMS than just using a relatively simple left join?
civilized 3y ago
> 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".
layer8 3y ago
> the columns in the resulting output appear exactly once No, records with the same join-column value are multiplied.

by jethkl 3y ago

youerbt 3y ago

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.

amluto 3y ago
Maybe some SQL server should require a token like “USING_WILD_GUESS” when doing a natural join :)
chasil 3y ago
The problem with USING is that it isn't supported everywhere.

hipadev23 3y ago

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

bradleybuda 3y ago
A right join is also syntactic sugar for a left join
- bakuninsbart 3y ago
  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.

cldellow 3y ago

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.

closeparen 3y ago
It seems like the database should be able to figure this out when a foreign key constraint is explicitly declared in the DDL.
- tqi 3y ago
  Should, but in practice I've rarely seen fk contraints used in analytics data warehouses (mostly for etl performance reasons)
jethkl 3y ago
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...)
- Cyberdog 3y ago
  Isn't this a lot more work both for the user and the RDBMS than just using a relatively simple left join?

civilized 3y ago

> 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".

layer8 3y ago

> the columns in the resulting output appear exactly once No, records with the same join-column value are multiplied.