Demonstrate a FULL outer join : Full Outer Join « Table Joins « Oracle PL / SQL






Demonstrate a FULL outer join

 
SQL> --
SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

1 row created.

SQL>
SQL> select * from ord;

  ORDER_NO    CUST_NO ORDER_DATE           TOTAL_ORDER_PRICE DELIVER_DATE         DELIVER PA     EMP_NO DELIVER_NAME
---------- ---------- -------------------- ----------------- -------------------- ------- -- ---------- -----------------------------------
GIFT_MESSAGE
----------------------------------------------------------------------------------------------------
         1          1 14-FEB-2002 00:00:00                23 14-FEB-2002 00:00:00 12 noon CA          1
Gift for wife

         2          1 14-FEB-2003 00:00:00            510.98 14-FEB-2003 00:00:00 5 pm    NY          7 Rose Ted
Happy Valentines Day to Mother

         3          2 14-FEB-2004 00:00:00            315.99 14-FEB-2004 00:00:00 3 pm    VS          2 Ani Forest
Happy Valentines Day to Father

         4          2 14-FEB-1999 00:00:00            191.95 14-FEB-1999 00:00:00 2 pm    NJ          2 O. John
Happy Valentines Day

         5          6 04-MAR-2002 00:00:00            101.95 05-MAR-2002 00:00:00 2:30 pm MO          2 Cora
Happy Birthday from John

         6          9 07-APR-2003 00:00:00            221.95 07-APR-2003 00:00:00 3 pm    MA          2 Sake Keith
Happy Birthday from Joe

         7          9 20-JUN-2004 00:00:00            315.95 21-JUN-2004 00:00:00 12 noon BC          2 Jessica Li
Happy Birthday from Jessica

         8         12 31-DEC-1999 00:00:00            135.95 01-JAN-2000 00:00:00 12 noon DI          3 Larry
Happy New Year from Lawrence

         9         12 26-DEC-2003 00:00:00            715.95 02-JAN-2004 00:00:00 12 noon SK          7 Did
Happy Birthday from Nancy

        10          4 15-JUN-2008 18:03:05            119.95 18-JUN-2008 18:03:05 6:30 pm VG          2 P. Jing
Happy Valentines Day to Jason

        11          2 16-JUN-2008 18:03:05               310 18-JUN-2008 18:03:05 3:30 pm DC          2 C. Late
Happy Birthday Day to Jack

        12          7 13-JUN-2008 18:03:05            121.95 14-JUN-2008 18:03:05 1:30 pm AC          2 W. Last
Happy Birthday Day to You

        13          7 16-JUN-2008 18:03:05            211.95 12-JUN-2008 18:03:05 4:30 pm CA          2 J. Bond
Thanks for hard working


13 rows selected.

SQL>
SQL> create table customer(
  2           cust_no                   integer                 primary key
  3          ,lastname                  varchar2(20)    not null
  4          ,firstname                 varchar2(15)    not null
  5          ,midinit                   varchar2(1)
  6          ,street                    varchar2(30)
  7          ,city                      varchar2(20)
  8          ,state                             varchar2(2)
  9          ,zip                               varchar2(5)
 10          ,zip_4                             varchar2(4)
 11          ,area_code                         varchar2(3)
 12          ,phone                             varchar2(8)
 13          ,company_name                      varchar2(50)
 14  );

Table created.

SQL>
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(1, 'Allen', 'Joe','J','10 Ave','London','CA','11111','1111','111', '111-1111','Big Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(2,'Ward','Sue','W','20 Ave','New York','NY','44444','4444','444', '436-4444','B Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(3,'Jason','Pure','J','50 St','Longli','CA','55555','5555','555', '234-4444','C Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(4,'Bird','Jill', null,'30 St','Pais','NY','22222','2222','222', '634-7733','D Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(5,'Hill','Carl','H','19 Drive','A Town','CA','66666','6566','666', '243-4243','E Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(6,'Peter','Yari','P','38 Ave','Small City','NY','77777','7777','777', '454-5443','F Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(7,'Joe','Paula','J','78 St. Apt 3A','Queen City','NY','32322','2323','888', '664-4333','E Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(8,'Chili','Steve','C','38 Ave Apt 62','Mili','CA','88888','8888','787', '456-4566','G Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(9,'Mona','Joe','M','930 Ave933','Kansas City','MO','12345','1234','412', '456-4563','H Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(10,'Hack','Kisi','H','Kings Rd','Bellmore','NY','54321','3898','516', '767-5677','I Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(11,'Bill','Jose','B','12 Giant Rd.','Newton','NJ','23454','1234','958', '123-7367','J Associates');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(12,'Taker','Lawrence','T','1 Sask Rd.','Camp','NJ','19191','3298','928', '123-7384','K Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(13,'Richer','Doris','R','213 Easy Street','WarPease','RI','34343','2112','501', '123-7384','L Inc');

1 row created.

SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(14,'Pete','Doris','P','9 Ave','New York','NY','45454','4222','112', '123-1234','M Company');

1 row created.

SQL>
SQL> select * from customer;
   CUST_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- --------
COMPANY_NAME
--------------------------------------------------
         1 Allen                Joe             J 10 Ave                         London               CA 11111 1111 111 111-1111
Big Company

         2 Ward                 Sue             W 20 Ave                         New York             NY 44444 4444 444 436-4444

   CUST_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- --------
COMPANY_NAME
--------------------------------------------------
B Company

         3 Jason                Pure            J 50 St                          Longli               CA 55555 5555 555 234-4444
C Company

         4 Bird                 Jill              30 St                          Pais                 NY 22222 2222 222 634-7733
D Company

         5 Hill                 Carl            H 19 Drive                       A Town               CA 66666 6566 666 243-4243
E Company

         6 Peter                Yari            P 38 Ave                         Small City           NY 77777 7777 777 454-5443
F Inc

         7 Joe                  Paula           J 78 St. Apt 3A                  Queen City           NY 32322 2323 888 664-4333
E Inc

         8 Chili                Steve           C 38 Ave Apt 62                  Mili                 CA 88888 8888 787 456-4566
G Inc

         9 Mona                 Joe             M 930 Ave933                     Kansas City          MO 12345 1234 412 456-4563
H Inc

        10 Hack                 Kisi            H Kings Rd                       Bellmore             NY 54321 3898 516 767-5677
I Inc

        11 Bill                 Jose            B 12 Giant Rd.                   Newton               NJ 23454 1234 958 123-7367
J Associates

        12 Taker                Lawrence        T 1 Sask Rd.                     Camp                 NJ 19191 3298 928 123-7384
K Company

        13 Richer               Doris           R 213 Easy Street                WarPease             RI 34343 2112 501 123-7384
L Inc

        14 Pete                 Doris           P 9 Ave                          New York             NY 45454 4222 112 123-1234
M Company


14 rows selected.

SQL>
SQL>
SQL> SELECT lastname,
  2         count(distinct order_no) AS count
  3  FROM customer, ord
  4  WHERE customer.cust_no = ord.cust_no(+)
  5  GROUP BY lastname, firstname
  6  UNION
  7  SELECT nvl(lastname, 'No Customer'),
  8         count(distinct order_no) AS count
  9  FROM customer, ord
 10  WHERE customer.cust_no(+) = ord.cust_no
 11  GROUP BY lastname, firstname
 12  ORDER BY 2
 13  /
LASTNAME                  COUNT
-------------------- ----------
Bill                          0
Chili                         0
Hack                          0
Hill                          0
Jason                         0
Pete                          0
Richer                        0
Bird                          1
Peter                         1
Allen                         2
Joe                           2
Mona                          2
Taker                         2
Ward                          3

14 rows selected.

SQL>
SQL> drop table ord;

Table dropped.

SQL> drop table customer;

Table dropped.

SQL>
SQL> --

 








Related examples in the same category

1.Full outer join Demo