Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: How to use more than two tables in Natural Join

    Hi,
    I am using Oracle9i R-2. I want help in writing a Query using Two Tables in SQL*PLUS. I am using ANSI/ISO Standrard for table-joins:

    select col1, col2, descr
    from tab1 natural join tab2

    There are two columns col1 & col2 is common between thse two tables. So, it will join them and query execute well.

    How can i use 3rd table tab3 in the same way in Natural Join...? If column col1 & col2 is available in tab3 also.

    I tried this way, but it gives me error:
    select col1, col2
    from tab1 natural join tab2 natural join tab3

    Is it possible to specify more than two tables in Natural Join Clause.

    Please check that out & help please. Thanks.

    Regards,
    Kamesh Rastogi
    - KR

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Natural Joins are best avoided in production code.

    There is no reason why they should not work with multiple tables, so long as all participating tables have common column names, which also form the keys, and their datatypes match. What error message do you get?
    Code:
    SQL*Plus: Release 9.2.0.1.0 - Developer's Release
    
    SQL> CREATE TABLE njtest1 (id INT, col1 VARCHAR2(30));
    
    Table created.
    
    SQL> CREATE TABLE njtest2 (id INT, col2 VARCHAR2(30));
    
    Table created.
    
    SQL> CREATE TABLE njtest3 (id INT, col3 VARCHAR2(30));
    
    Table created.
    
    SQL> INSERT ALL
      2  INTO njtest1 VALUES (1, 'Row one')
      3  INTO njtest1 VALUES (2, 'Row two')
      4  INTO njtest2 VALUES (1, 'Row one in njtest2')
      5  INTO njtest3 VALUES (1, 'Row one in njtest3')
      6  INTO njtest3 VALUES (3, 'Row three')
      7  SELECT * FROM dual;
    
    5 rows created.
    
    SQL> SELECT *
      2  FROM   njtest1 
      3         NATURAL JOIN njtest2
      4         NATURAL JOIN njtest3
      5  /
    
         ID COL1                         COL2                         COL3
    ------- ---------------------------- ---------------------------- ----------------------------
          1 Row one                      Row one in njtest2           Row one in njtest3
    
    1 row selected.

Posting Permissions

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