Database Tutorial

Course Tutorial Site

Site Admin

Natural Join

with 4 comments

The NATURAL JOIN matches every row in one table against every row in another based on common values found in columns that share a common name and data type. It is like an INNER JOIN without the join clause. The NATURAL JOIN is new to ANSI SQL:92 and has no equivalent in ANSI SQL:89 database instances.

The syntax of a NATURAL JOIN is a hybrid between the syntax of a CROSS JOIN and INNER JOIN. It’s syntax is closest to the CROSS JOIN because you don’t use a join clause, like the ON or USING clauses. It’s performance is a mirror to an INNER JOIN with a caveat. The caveat, or restriction, is that the two tables may only share column names when they are members of the table’s respective PRIMARY KEY or FOREIGN KEY.

Any columns that aren’t members of the table’s respective PRIMARY KEY or FOREIGN KEY are used by a NATURAL JOIN because it builds the join condition by reading the database catalog. A NATURAL JOIN doesn’t distinguish whether columns are part of a PRIMARY KEY or FOREIGN KEY. If it did take that in consideration, it would perform only when referential integrity is enforced. As a result of this behavior, a NATURAL JOIN can return an unexpected, or unnatural, result set.

A NATURAL JOIN acts like a nested loop where you match a key from the outer loop against a key from the inner loop. It discards all non-matching values from the outer and inner loops. It’s equivalent programmatic resolution is found in a PHP example for the INNER JOIN page.

Written by michaelmclaughlin

January 18th, 2010 at 10:15 pm

Posted in

4 Responses to 'Natural Join'

Subscribe to comments with RSS or TrackBack to 'Natural Join'.

  1. Nice explanation!!!

    ErikCC

    5 Aug 14 at 5:06 pm

  2. Just spelling mistakes…

    “This type of behavior can cause a business to loose [lose] visibility to data. It’s also hard to find unless you know that natural joins where [were] used in the implementation of the product.”

    Cole

    31 Jan 15 at 8:44 pm

  3. Cole, Great catch! It’s fixed.

    michaelmclaughlin

    7 Mar 15 at 3:09 am

  4. nice

    vrushali

    27 Jul 15 at 3:49 am

Leave a Reply