Results 1 to 3 of 3

Thread: Natural Joins

  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: Natural Joins

    If the columns having the same names have different data types, an error is returned.


    SQL> desc test1;

    Name Null? Type
    EMPID NUMBER
    NAME VARCHAR2(10)

    ------------------------------------------------------
    SQL> desc test2;

    Name Null? Type
    EMPID VARCHAR2(10)
    NAME VARCHAR2(10)


    ---------------------------------------------------------------
    SQL> select * from test1 natural join test2;

    EMPID NAME

    1060 sam
    1061 rose
    1062 chris
    1062 sona
    1062 maya
    1062 maya
    1060 sam
    1062 maya
    1062 maya

    9 rows selected.

    -------------------------------------------------------

    Different data types

    If the columns having the same names have different data types, an error is returned.

    empid in test1 as number;
    empid in test2 as varchar;

    why Oracle doesn't throw ERROR ?? MAy i know cause of Reason ????

    --------------------------------------------------------------


    " Oracle can implicitly convert the number column to varchar datatype and then compare. "

    How oracle matches here ?



    Thanks and Regards
    Thiyagusham .G

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    By the way, I would strongly discourage the use of the Natural join. Always implicitly join columns. The natural join is a lazy way to join tables and will always come back to bit you. For example if you rename a column or add a new one to a table that matches the column in the other table your coding will fail.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •