Grzegorz Szpetkowski Grzegorz Szpetkowski - 1 month ago 12
SQL Question

Is NATURAL (JOIN) considered harmful in production environment?

I am reading about NATURAL shorthand form for SQL joins and I see some traps:


  • it just takes automatically all same named column-pairs (use USING to specify explicit column list)

  • if some new column is added, then join output can be "unexpectedly" changed too, which may be not so obvious (even if you know how NATURAL works) in complicated structures


Answer

NATURAL JOIN syntax is anti-pattern:

  • The purpose of the query is less obvious;
    • the columns used by the application is not clear
    • the columns used can change "unexpectedly"
  • The syntax goes against the modularity rule, about using strict typing whenever possible. Explicit is almost universally better.

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