An inner join to : Inner Joins « Table Joins « Oracle PL / SQL






An inner join to

   
SQL>
SQL>
SQL> create table product(
  2          product_id              integer           primary key
  3          ,price                  number(7,2)
  4          ,description            varchar2(75)
  5          ,onhand                 number(5,0)
  6          ,reorder                number(5,0)
  7          ,supplier_no            integer
  8  );

Table created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,'Oracle',100,20);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,'SQL Server',null,null);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,'MySQL',null,null);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,'DB2',50,10);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,'Java',100,20);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,'C++',null,null);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,'Javascript',3,5);

1 row created.

SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,'Ruby',null,null);

1 row created.

SQL>
SQL> select * from product;

PRODUCT_ID      PRICE DESCRIPTION                                                                     ONHAND    REORDER SUPPLIER_NO
---------- ---------- --------------------------------------------------------------------------- ---------- ---------- -----------
         1        2.5 Oracle                                                                             100         20
         2         23 SQL Server
         3            MySQL
         4        1.5 DB2                                                                                 50         10
         5       10.5 Java                                                                               100         20
         6         45 C++
         7      19.99 Javascript                                                                           3          5
         8        4.5 Ruby

8 rows selected.

SQL>
SQL> create table product_supplier(
  2             product_id               integer
  3            ,supplier_no              integer
  4            ,price                    number(7,2)
  5            ,primary key (product_id, supplier_no)
  6  );

Table created.

SQL> insert into product_supplier values(1,10,2.25);

1 row created.

SQL> insert into product_supplier values(1,11,2.10);

1 row created.

SQL> insert into product_supplier values(1,12,2.85);

1 row created.

SQL> insert into product_supplier values(2,10,22.25);

1 row created.

SQL> insert into product_supplier values(2,11,22.00);

1 row created.

SQL> insert into product_supplier values(2,12,21.25);

1 row created.

SQL>
SQL> select * from product_supplier;
PRODUCT_ID SUPPLIER_NO      PRICE
---------- ----------- ----------
         1          10       2.25
         1          11        2.1
         1          12       2.85
         2          10      22.25
         2          11         22
         2          12      21.25

6 rows selected.

SQL>
SQL> create table supplier(
  2          supplier_no             integer           primary key
  3          ,supplier_name          varchar2(50)
  4          ,address                varchar(30)
  5          ,city                   varchar(20)
  6          ,state                  varchar2(2)
  7          ,area_code              varchar2(3)
  8          ,phone                  varchar2(8)
  9  );

Table created.

SQL> insert into supplier(supplier_no, supplier_name)values(10,'ABC Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(11,'BCD Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(12,'WWW Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(13,'XYZ Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(14,'R Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(15,'D Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(16,'B Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(17,'W Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(18,'P Gift Supply Co.');

1 row created.

SQL> insert into supplier(supplier_no, supplier_name)values(19,'R Gift Supply Co.');

1 row created.

SQL>
SQL> select * from supplier;
SUPPLIER_NO SUPPLIER_NAME                                      ADDRESS                        CITY         ST ARE PHONE
----------- -------------------------------------------------- ------------------------------ -------------------- -- --- --------
         10 ABC Gift Supply Co.
         11 BCD Gift Supply Co.
         12 WWW Gift Supply Co.
         13 XYZ Gift Supply Co.
         14 R Gift Supply Co.
         15 D Gift Supply Co.
         16 B Gift Supply Co.
         17 W Gift Supply Co.
         18 P Gift Supply Co.
         19 R Gift Supply Co.

10 rows selected.

SQL>
SQL> select supplier_name,
  2          description
  3  from supplier s,product_supplier ps,product p
  4  where s.supplier_no = ps.supplier_no
  5  and ps.product_id = p.product_id;
SUPPLIER_NAME                                      DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
ABC Gift Supply Co.                                Oracle
BCD Gift Supply Co.                                Oracle
WWW Gift Supply Co.                                Oracle
ABC Gift Supply Co.                                SQL Server

SUPPLIER_NAME                                      DESCRIPTION
-------------------------------------------------- ---------------------------------------------------------------------------
BCD Gift Supply Co.                                SQL Server
WWW Gift Supply Co.                                SQL Server

6 rows selected.

SQL>
SQL> drop table supplier;

Table dropped.

SQL> drop table product_supplier;

Table dropped.

SQL> drop table product;

Table dropped.

SQL> --

   
    
  








Related examples in the same category

1.Inner joins use join conditions in the FROM clause just like natural joins
2.Three inner joins query
3.Inner join primary key and foreign key
4.Inner join with null foreign key
5.Reference inner query column in outter select statement
6.Inner and Outer Joins
7.Using Inner Joins