SQL NATURAL JOIN

SQL using NATURAL JOIN.

  • NATURAL JOIN - Works only when the two tables have a single identical named and type column as the joining key.
  • NATURAL JOIN is nearly the same as INNER JOIN except that repeated column is avoided.
  • When using NATURAL JOIN, the ON and USING keywords should not be allowed, even for some system you can.
  • Not every RDBMS has implemented NATURAL JOIN.
  • A skeleton version of NATURAL JOIN syntax is this:
    SELECT select_list
    FROM table_1 
    NATURAL JOIN table_2
    [WHERE search_conditions ]
    [ORDER BY column1, column2, ... ]
    Example using NATURAL JOIN between the tables:
    SQL server does not support NATURAL JOIN.
    MySQL, Oracle, PostgreSQL :
    Select First_name "First name",
      Last_name "Last name",
      email, Telephone_type "Phone type",
      Telephone_number as "Phone number"
     from customers 
     NATURAL JOIN telephones 
     WHERE  Telephone_type<>'Unknown'
     order by Last_name;
     
    The result should be:
    First name Last name email Phone type Phone number
    Judith Bowman J.Bowman@imb.com Cell (371) 153-54
    Judith Bowman J.Bowman@imb.com Work (371) 143-54
    Ricard Nixon Ricard.Nixon@hotmail.com Work (978) 667-94
    Ricard Nixon Ricard.Nixon@hotmail.com Home (978) 667-94
    Robert Redford R.Redford@google.com Home (679) 234-94
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.