Hacker News Reader

Comment by blendo | Hacker News Reader

I worked with a programmer who decided, one day, that all his joins were from now on going to be “natural joins”, and code like this started appearing:

select job_name, comp_name from jobs natural join companies;

This is a foot gun just waiting for someone to unsuspectingly add a new column to “jobs” with the same name as one already in “companies”, thereby silently breaking the query logic.

Sadly, the boss declined to rein in such shenanigans, and I left soon after.

View on HN

Natural join is actually nice mental test of db semantics - if it does not just work, you have probably mixup of column names (same name meaning different things). If you have really clean and good patterns (I know you dont and you dont discover it before this major incident) it would work fine.

I've been longing for our db to add support for something ala

    select j.job_name, c.comp_name 
    from companies c
    join jobs j on foreign key

Could take optional name in case there's more than one foreign key linking the two.

I do feel like this is an obvious thing for SQL to allow and databases to support. Given that it's not part of the standard (and NATURAL JOIN is) I can only assume that there's a compelling reason that I haven't thought about.

If anyone out there knows why this isn't a thing, please chime in!

As soon as you have multiple relationships to a table relying on different foreign / composite keys, I’m pretty sure this completely breaks down. And the second you add an additional foreign key, you break all queries — so you’ve turned foreign key constraints from an internally managed constraint to a public interface whose addition/removal at any point is a breaking change.

Natural joins however depend on already publicly exposed metadata (column names), and if that changed you’d break queries anyways.

At least they could make a syntax for joining on a “foreign key references” clause from create statement. E.g. “from a join b by a.b_id[, a.b_compound_id_2]”.

But that’s just a sugar for still too low-level SQL. I’d better have well named relations and use these names in code. This way, there would be no situation when you add/remove a constraint but forget to add/remove a condition.

  create table a (id, b_id);
  create table b (id);
  create relation atob from a to b on a.b_id = b.id;

  select from a left join b using atob;

This could also define classes of relationships to check in runtime. E.g. I’ve never sent all purchases with right join in my life, but had enough exploding relationships where 1:1 was expected.

But 99.99% of my child tables have only a single foreign keys linking it to its parent and will not have more. And breaking queries is much better than silently doing the wrong thing like NATURAL JOIN.

For the edge cases it could support taking the name of the foreign key as an optional parameter.

Reply on news.ycombinator.com