Finding Overlaps Using SQL

Question

I want to extract rows from a table whose interval intersects with the interval specified in the query. Assuming I have a simple table ID, DATE_START, DATE_ENDand two query parameters, P_DATE_STARTand P_DATE_ENDwhat is the easiest way to express a query, so that I find all rows for which it [DATE_START, DATE_END]has at least one common element with [P_DATE_START, P_DATE_END]?

---

Update:

To make the desired result clearer, find the list of input values ​​and expected results below. Column DATE_START, DATE_END, P_DATE_START, P_DATE_END, MATCH.

16, 17, 15, 18, YES
15, 18, 16, 17, YES
15, 17, 16, 18, YES
16, 18, 15, 17, YES
16, 17, 18, 19, NO
18, 19, 16, 17, NO

Answer 1

Even simpler:

SELECT id, date_start, date_end 
FROM thetable 
WHERE date_start <= p_date_end 
AND date_end >= p_date_start

Answer 2

Depending on your dbms, you can use the OVERLAPS statement.

select * from your_table
where (date '2011-01-15', date '2011-01-18') overlaps (date_start, date_end)

Answer 3

SELECT id, date_start, date_end From the table WHERE no (date_end <p_date_start OR p_date_end <date_start)