PLSQL Programming part 4 - SQL joins

/**
Do the following exercises for learning SQL joins
Pre-requisites: You must have CREATE privilege and some space allocated for you
**/

create table temp1
(
cust_id number,
cust_name varchar2(100),
addr1 varchar2(40),
addr2 varchar2(40),
addr3 varchar2(40),
addr4 varchar2(40),
pincode varchar2(40),
constraint cust_pk primary key ( cust_id )
);
/

create table service_tbl
(
cust_id number,
service_no number,
start_date date,
end_date date,
plan_type varchar2(10),
foreign key (cust_id) references temp1(cust_id)
);
/

alter table temp1 rename to cust_table;

Select * from cust_table;

insert into cust_table
values(100,'CUSTOMER 1','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(200,'CUSTOMER 2','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(300,'CUSTOMER 3','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert Into cust_table
values(400,'CUSTOMER 4','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(500,'CUSTOMER 5','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(600,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);
insert into cust_table
values(700,'CUSTOMER 6','ADDRESS CODE 1','ADDRESS CODE 2','ADDRESS CODE 3','ADDRESS CODE 4',11111);

commit;

Select *
from service_tbl;

insert into service_tbl
values(100,1032427,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032428,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032429,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032430,sysdate,null,'Post Paid');
insert into service_tbl
values(100,1032431,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032432,sysdate,null,'PrePaid');
insert into service_tbl
values(100,1032433,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(200,2032427,sysdate,null,'Post Paid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(300,3032427,sysdate,null,'PrePaid');
insert into service_tbl
values(400,4032427,sysdate,null,'Post Paid');
insert into service_tbl
values(500,5032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');
insert into service_tbl
values(600,6032427,sysdate,null,'Post Paid');

commit;

/***
CARTESIAN PRODUCT
***/

Select *
from cust_table,service_tbl;

--It returns 140 rows

select count(*) from cust_table; -- Returns 7
select count(*) from service_tbl; --Returns 20 rows


/***
SELF JOIN
***/

Select *
from cust_table A,CUST_TABLE B;

--It returns 49 rows -- 7x7 ROWS

/***
EQUI JOINS
***/

Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID;

--It returns 20 rows
--Returns only the records those satisy the condition
--Driving table or the leading table is duplicated for the no of records fetched in the joining table


/***
OUTER JOIN - LEFT

***/
--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID = SERVICE_TBL.CUST_ID(+);

--Old syntax
Select *
from cust_table left outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 21 rows
--Returns the records those satisfy the condition in serivce_tbl and those dont in cust_table
--For the cust_id 700 in cust_table, there are no child records in service_tbl
--So , in left outer join, it finds no matching records and adds a null set

/***
OUTER JOIN - RIGHT
***/

--Modern syntax
Select *
from cust_table,SERVICE_TBL
WHERE cust_table.CUST_ID(+) = SERVICE_TBL.CUST_ID;

--Old syntax
Select *
from cust_table right outer join SERVICE_TBL
on cust_table.CUST_ID = SERVICE_TBL.CUST_ID;
--It returns 20 rows
--Returns the records those satisy the condition in cust_table and those dont in service_tbl