Examples of All outer joins and Equijoins in oracle

In previous post I have define all types of joins in oracle. In this post I will show example of all outer joins one by one. Also I have shown an example of equijoins. Hopefully I next post I will give example of semijoin, antijoin, selfjoin and cartesian product.

To demonstrate example I have used two table customers and orders and insert some data into it.

/* 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.
SQL> select * from customers;

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

SQL> 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:
-------------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo = o.CustNo(+) and c.address='Leeds';

or recommended later 9i syntax,
SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo
where c.Address = 'Leeds'; 2 3 4
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:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo(+) = o.CustNo and o.OrderDate > '31-JAN-96';

CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
or recommended later 9i syntax
SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Full outer join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';

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

Without condition here is the example of full, left and right outer join.
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia
12-FEB-96

6 rows selected.

SQL> select CustName, OrderDate, Address
from Customers c right outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 31-JAN-96 Leeds
P. Jones 24-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

SQL> select 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
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia

Example of Equijoins
--------------------------
SQL> select 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