4 ms·

> By far the most common case for joins is following foreign keys. SQL has no special syntax for this You can use NATURAL JOIN select * from foo natural join

> By far the most common case for joins is following foreign keys. SQL has no special syntax for this You can use NATURAL JOIN select * from foo natural join bar Works as long as the keys are named the same. However, a lot of people have a habit of naming keys differently in the two tables.

faho 5y ago
And it also breaks if two non-key columns are named the same. This makes naming key columns differently a defence technique, so you stop people from using natural joins.
- thom 5y ago
  I generally use a prefix for columns based on the relation name, but preserving the name of keys (so a foreign key to user_id is user_id, not order_user_id etc). You obviously can't use natural joins if you end up with _multiple_ foreign keys with different roles, but generally I find this a better way to live all round. Never having to rename five 'name' columns in some output to make it clear which is which etc.
rswail 5y ago
or ON: select * from foo join bar on (foo.x = bar.y) if the columns have a different name. I tend to write my joins first, then use where clauses as filters. A select * from foo left inner join on (foo.x = bar.y) is semantically equivalent to foo, bar where foo.x = bar.y, but keeping the joins separate from the filters makes the query more clear.
vertere 5y ago
And what if there is more than one way of joining two tables, or a table to itself? A foreign key is effectively a reference to another column, but to de-reference it you have to tell the database which table and column it's a reference to. Every time. Even when this information is already specified in a foreign key constraint. The author is talking about (not) being able to specify the "from" table and column without having specify the "to" table and column (i.e. tell the database how to de-reference it) on each query. A natural join removes the need to specify the columns, but still requires specifying both tables. So besides requiring a de facto single namespace for columns across tables and generally seeming like a footgun, it doesn't achieve the same thing.

faho 5y ago

And it also breaks if two non-key columns are named the same. This makes naming key columns differently a defence technique, so you stop people from using natural joins.

thom 5y ago
I generally use a prefix for columns based on the relation name, but preserving the name of keys (so a foreign key to user_id is user_id, not order_user_id etc). You obviously can't use natural joins if you end up with _multiple_ foreign keys with different roles, but generally I find this a better way to live all round. Never having to rename five 'name' columns in some output to make it clear which is which etc.

rswail 5y ago

or ON: select * from foo join bar on (foo.x = bar.y) if the columns have a different name. I tend to write my joins first, then use where clauses as filters. A select * from foo left inner join on (foo.x = bar.y) is semantically equivalent to foo, bar where foo.x = bar.y, but keeping the joins separate from the filters makes the query more clear.

vertere 5y ago

And what if there is more than one way of joining two tables, or a table to itself? A foreign key is effectively a reference to another column, but to de-reference it you have to tell the database which table and column it's a reference to. Every time. Even when this information is already specified in a foreign key constraint. The author is talking about (not) being able to specify the "from" table and column without having specify the "to" table and column (i.e. tell the database how to de-reference it) on each query. A natural join removes the need to specify the columns, but still requires specifying both tables. So besides requiring a de facto single namespace for columns across tables and generally seeming like a footgun, it doesn't achieve the same thing.