Understanding JOINING.(LEFT/RIGHT/FULL OUTER, Equijoins)

Understanding JOINING.(LEFT/RIGHT/FULL OUTER, Equijoins)

/* create the customer table */

Create Table Customers (
CustNo Integer Not Null Primary Key,
CustName Char (20),
Address Char (40)
);

/* create the orders table */

Create Table Orders (
OrderNo Integer Not Null Primary Key,
CustNo Integer,
OrderDate Date
);

/* put some data into the customer table */

Insert into Customers Values (1,'P. Jones','Leeds');
Insert into Customers Values (2,'A. Chan','Hong Kong');
Insert into Customers Values (3,'K. Green','Columbia');
Insert into Customers Values (4,'B. Smith','Leeds');
Insert into Customers Values (5,'A. Khan','');

/* put some data into the orders table */

Insert into Orders Values (1,1,'24-JAN-96');
Insert into Orders Values (2,1,'31-JAN-96');
Insert into Orders Values (3,2,'04-FEB-96');
Insert into Orders Values (4,4,'12-FEB-96');
Insert into Orders Values (5,6,'12-FEB-96');

/*Now have a look at both tables' data.*/

select * from customers;

/*CUSTNO CUSTNAME ADDRESS
---------- -------------------- ----------------------------------------
1 P. Jones Leeds
2 A. Chan Hong Kong
3 K. Green Columbia
4 B. Smith Leeds*/

select * from orders;

/*ORDERNO CUSTNO ORDERDATE
---------- ---------- ---------
1 1 24-JAN-96
2 1 31-JAN-96
3 2 04-FEB-96
4 4 12-FEB-96
5 6 12-FEB-96*/


/*Example of Left Outer Join:
-------------------------------*/
select c.custno,o.custno,CustName, OrderDate, Address
  from Customers c, Orders o
 where c.CustNo = o.CustNo(+)
   and c.address = 'Leeds';

/*or recommended later 9i syntax,*/
select c.custno,o.custno,CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo
;

/*CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
B. Smith 12-FEB-96 Leeds

Right Outer Join Example:
----------------------------*/
select c.custno,o.custno,CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo(+) = o.CustNo;

/*CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
or recommended later 9i syntax*/

select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo;

/*CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Full outer join Example:
----------------------------*/
select c.custno,CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo;

/*CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Example of Equijoins
--------------------------*/
select * from customers;
select * from orders;
select c.custno,address, orderdate from customers c, orders o where c.custno=o.custno order by orderdate;

/*ADDRESS ORDERDATE
---------------------------------------- ---------
Leeds 24-JAN-96
Leeds 31-JAN-96
Hong Kong 04-FEB-96
Leeds 12-FEB-96*/