SQL Logic to Fetch Effective Dates Overlaps

create table Effective_date_comparision(
row_wid number,
scd1_num number,
effective_from_date date,
effective_to_date date);

insert into Effective_date_comparision(row_wid,scd1_num,effective_from_date,effective_to_date) values (100,1,'1/JAN/2014','1/DEC/2014');
insert into Effective_date_comparision(row_wid,scd1_num,effective_from_date,effective_to_date) values (101,2,'1/JAN/2015','1/DEC/2015');
insert into Effective_date_comparision(row_wid,scd1_num,effective_from_date,effective_to_date) values (102,3,'1/DEC/2015','31/DEC/2015');
insert into Effective_date_comparision(row_wid,scd1_num,effective_from_date,effective_to_date) values (103,3,'1/DEC/2015','31/DEC/2015');
commit;

---To Fetch all Overlap records from one single table  
--Distinct is must, if we have same record overlaps with more than 1 records

select distinct  a.* from Effective_date_comparision a
join Effective_date_comparision b
on (a.row_wid <> b.row_wid)  /*a.primary Key <> b.primary Key as always same primary Keys records will overlap as we gave '=' operator in comparision*/
where (a.effective_from_date <= b.effective_to_date
and a.effective_to_date >= b.effective_from_date)

/*To fix the data using Update statements one of the overlapped records would be enough to update. Hence, we have to change the effective logic condition as below
so that only one of the overlapped record will be fetched.*/
--To fetch records with Effective_to_date overlap i.e. earlier record will be fetched
--Distinct is must, if we have same record overlaps with more than 1 records

select distinct a.* from Effective_date_comparision a
join Effective_date_comparision b
on (a.row_wid <> b.row_wid)  /*a.primary Key <> b.primary Key as always same primary Keys records will overlap as we gave '=' operator in comparision*/
where (a.effective_to_date >= b.effective_from_date
and a.effective_to_date <= b.effective_to_date)

--To feetch records with Effective_from_date overlap i.e. latest record will be fetched
--Distinct is must, if we have same record overlaps with more than 1 records

select distinct a.* from Effective_date_comparision a
join Effective_date_comparision b
on (a.row_wid <> b.row_wid)  /*a.primary Key <> b.primary Key as always same primary Keys records will overlap as we gave '=' operator in comparision*/
where (a.effective_from_date >= b.effective_from_date
and a.effective_from_date <= b.effective_to_date)

General Sql to find Overlaps w.r.t Effective Dates within the same table:

select A.*
from MyTable A
inner join MyTable B
on (B.start <= A.end)
and (B.end >= A.start)

or something like that (assuming dates are not nullable and equal dates count as an overlap).