ORACLE NATURAL JOIN

ORACLE NATURAL JOIN

Oracle natural join joins tables on parent and child tables without condition clause.

SYNTAX:

select * from [table 1]

natural join

[table 2]

Table 1 &table 2  should have foreign and primary key relation ships.

for ex:  customers and orders tables.

Orders table must contain valid customer id, otherwise he/she can't place an order.

So Customer table has primary key customer ID

Orders tables has CustomerID as a foreign key(who placed the order).

SQL>DESC customers;

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 CUSTOMERID                                NOT NULL NCHAR(5)
 COMPANYNAME                               NOT NULL NVARCHAR2(40)
 CONTACTNAME                                        NVARCHAR2(40)
 CONTACTTITLE                                       NVARCHAR2(30)
 ADDRESS                                            NVARCHAR2(60)
 CITY                                               NVARCHAR2(15)
 REGION                                             NVARCHAR2(15)
 POSTACODE                                          NVARCHAR2(10)
 PHONE                                              NVARCHAR2(24)
 FAX                                                NVARCHAR2(24)

SQL> DESC ORDERS;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ORDERID                                   NOT NULL NUMBER(10)
 CUSTOMERID                                         NCHAR(5)
 EMPLOYEEID                                         NUMBER(10)
 ORDERDATE                                          DATE
 REQUIREDDATE                                       DATE
 SHIPPEDDATE                                        DATE
 SHIPVIA                                            NUMBER(10)
 FREIGHT                                            NUMBER(19,4)
 SHIPNAME                                           NVARCHAR2(40)
 SHIPADDRESS                                        NVARCHAR2(60)
 SHIPCITY                                           NVARCHAR2(15)
 SHIPREGION                                         NVARCHAR2(15)
 SHIPPOSTALCODE                                     NVARCHAR2(10)
 SHIPCOUNTRY                                        NVARCHAR2(15)

NATUARL JOIN ON CUSTOMER and ORDERS TABLES

select * from
customers
natural join
orders

Note: There is no ON condition.

Tags: oracle joins, oracle natural join, How to use oracle natural join, How to use natural joins in queries, Oracle natural join syntax, Oracle natural join usage.