By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 ( date_from, date_to ) AS
SELECT DATE '2020-01-01', DATE '2020-01-10' FROM DUAL UNION ALL
SELECT DATE '2020-02-10', DATE '2020-02-15' FROM DUAL UNION ALL
SELECT DATE '2020-03-15', DATE '2020-03-18' FROM DUAL;
3 rows affected
CREATE TABLE table2 ( date_from, date_to ) AS
SELECT DATE '2020-01-05', DATE '2020-01-15' FROM DUAL UNION ALL
SELECT DATE '2020-02-09', DATE '2020-02-16' FROM DUAL UNION ALL
SELECT DATE '2020-03-16', DATE '2020-03-18' FROM DUAL;
3 rows affected
CREATE TABLE table3 ( date_from, date_to ) AS
SELECT DATE '2020-01-01', DATE '2020-01-02' FROM DUAL UNION ALL
SELECT DATE '2020-01-09', DATE '2020-01-16' FROM DUAL UNION ALL
SELECT DATE '2020-02-08', DATE '2020-02-17' FROM DUAL UNION ALL
SELECT DATE '2020-03-15', DATE '2020-03-17' FROM DUAL;
4 rows affected
CREATE TABLE table4 ( date_from, date_to ) AS
SELECT DATE '2020-01-02', DATE '2020-01-12' FROM DUAL UNION ALL
SELECT DATE '2020-02-08', DATE '2020-02-17' FROM DUAL UNION ALL
SELECT DATE '2020-03-16', DATE '2020-03-19' FROM DUAL;
3 rows affected
SELECT t1.date_from AS t1_from,
t2.date_from AS t2_from,
t3.date_from AS t3_from,
t4.date_from AS t4_from,
t1.date_to AS t1_to,
t2.date_to AS t2_to,
t3.date_to AS t3_to,
t4.date_to AS t4_to
FROM table1 t1
INNER JOIN table2 t2
ON ( t1.date_to > t2.date_from
AND t1.date_from < t2.date_to )
INNER JOIN table3 t3
ON ( LEAST( t1.date_to, t2.date_to ) > t3.date_from
AND GREATEST( t1.date_from, t2.date_from ) < t3.date_to )
INNER JOIN table4 t4
ON ( LEAST( t1.date_to, t2.date_to, t3.date_to ) > t4.date_from
AND GREATEST( t1.date_from, t2.date_from, t3.date_from ) < t4.date_to );
| T1_FROM | T2_FROM | T3_FROM | T4_FROM | T1_TO | T2_TO | T3_TO | T4_TO |
|---|---|---|---|---|---|---|---|
| 15-MAR-20 | 16-MAR-20 | 15-MAR-20 | 16-MAR-20 | 18-MAR-20 | 18-MAR-20 | 17-MAR-20 | 19-MAR-20 |
| 10-FEB-20 | 09-FEB-20 | 08-FEB-20 | 08-FEB-20 | 15-FEB-20 | 16-FEB-20 | 17-FEB-20 | 17-FEB-20 |
| 01-JAN-20 | 05-JAN-20 | 09-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 16-JAN-20 | 12-JAN-20 |
SELECT t1.date_from AS t1_from,
t2.date_from AS t2_from,
t3.date_from AS t3_from,
t4.date_from AS t4_from,
t1.date_to AS t1_to,
t2.date_to AS t2_to,
t3.date_to AS t3_to,
t4.date_to AS t4_to
FROM table1 t1
INNER JOIN table2 t2
ON ( t1.date_to > t2.date_from
AND t1.date_from < t2.date_to )
INNER JOIN table3 t3
ON ( GREATEST( t1.date_to, t2.date_to ) > t3.date_from
AND LEAST( t1.date_from, t2.date_from ) < t3.date_to )
INNER JOIN table4 t4
ON ( GREATEST( t1.date_to, t2.date_to, t3.date_to ) > t4.date_from
AND LEAST( t1.date_from, t2.date_from, t3.date_from ) < t4.date_to );
| T1_FROM | T2_FROM | T3_FROM | T4_FROM | T1_TO | T2_TO | T3_TO | T4_TO |
|---|---|---|---|---|---|---|---|
| 15-MAR-20 | 16-MAR-20 | 15-MAR-20 | 16-MAR-20 | 18-MAR-20 | 18-MAR-20 | 17-MAR-20 | 19-MAR-20 |
| 10-FEB-20 | 09-FEB-20 | 08-FEB-20 | 08-FEB-20 | 15-FEB-20 | 16-FEB-20 | 17-FEB-20 | 17-FEB-20 |
| 01-JAN-20 | 05-JAN-20 | 01-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 02-JAN-20 | 12-JAN-20 |
| 01-JAN-20 | 05-JAN-20 | 09-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 16-JAN-20 | 12-JAN-20 |