Is NATURAL (JOIN) dangerous in a production environment?

Question

I read about the abbreviated form of NATURAL for SQL joins and see some pitfalls:

• it just automatically accepts all the same column names (use USING to specify an explicit list of columns)
• If a new column is added, the connection output may also unexpectedly change, which may not be so obvious (even if you know how NATURAL works) in complex structures.

Answer 1

Syntax

NATURAL JOIN is an anti-pattern:

• The purpose of the request is less obvious;
  • the columns used by the application are not clear.
  • used columns may change unexpectedly
• The syntax goes against the rule of modularity , using strong typing when possible. Explicit is almost universally better.

Because of this, I do not recommend syntax in any environment.
I also do not recommend mixing the syntax (IE: using both NATURAL JOIN and the explicit INNER / OUTER JOIN syntax) - keep a consistent code format.

Answer 2

These "traps" that seem to argue about natural compounds cut both ways. Suppose you add a new column to table A, fully expecting it to be used when joining table B. If you know that each join A and B is a natural join, you are done. If each connection explicitly uses USE, you must track them all and modify them. Miss one and there is a mistake.

Use NATURAL joins when table semantics suggest that this is the right thing. Use explicit join criteria if you want to make sure that the join is done in a specific way, regardless of how table definitions can evolve.

Answer 3

One thing that completely destroys NATURAL for me is that most of my tables have an id column, which are obviously semantically different. You can argue that having user_id makes more sense than id , but then you end up writing things like user.user_id , a DRY violation. In addition, by the same logic, you would also have columns like user_first_name , user_last_name , user_age ... (which also makes sense in the sense that it will be different from, for example, session_age ) ... Horror.

I will stick to my JOIN ... ON ... , thanks very much. :)

Answer 4

I agree with other posters that the explicit connection should be used for clarity, and it is also easy to allow the transition to the OUTER connection if your requirements change.

However, most of your pitfalls have nothing to do with joins, but rather because of using "SELECT *" instead of explicitly naming the columns you need "SELECT a.col1, a.col2, b.col1, b. Col2". These traps occur whenever a list of wildcard columns is used.

Answer 5

Adding an additional reason not listed in any of the above answers. In postgres (not sure if this is the case for other databases), if column names do not appear between two tables when using NATURAL JOIN , then CROSS JOIN is executed. This means that if you have an existing query, and then you must subsequently change one of the column names in the table, you will still get a set of rows returned from the query, and not an error. If you used the JOIN ... USING(...) syntax instead, you would get an error if the join column was no longer there.

postgres documentation contains a note for this:

Note. USE is quite safe from column changes in a connected relationship, since only the listed columns are combined. NATURAL is significantly more risky, since any schema changes in any respect that result in a new name for a comparable column will result in the join also matching this new column.

Answer 6

You mean a syntax like this:

 SELECT * FROM t1, t2, t3 ON t1.id = t2.id AND t2.id = t3.id 

In comparison with this:

  SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id AND t2.id = t3.id 

I prefer the second syntax and also format it differently:

  SELECT * FROM T1 LEFT OUTER JOIN T2 ON T2.id = T1.id LEFT OUTER JOIN T3 ON T3.id = T2.id 

In this case, it is very clear which tables I am joining and which ON element I am using to join them. Using this first syntax is too simple not to insert the correct JOIN and get a huge set of results. I do this because I am prone to typos, and this is my insurance against this. In addition, visually easier to debug.