Grzegorz Szpetkowski Grzegorz Szpetkowski - 1 year ago 75
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download