What is a simple and efficient way to find rows with time-interval overlaps in SQL?

Question

I have two tables, both with start time and end time fields. I need to find, for each row in the first table, all of the rows in the second table where the time intervals intersect.

For example:

 <-----row 1 interval------->
<---find this--> <--and this--> <--and this-->

Please phrase your answer in the form of a SQL WHERE-clause, AND consider the case where the end time in the second table may be NULL.

Target platform is SQL Server 2005, but solutions from other platforms may be of interest also.

• Salman Arshad: possible duplicate of Determine Whether Two Date Ranges Overlap
• Jonathan Leffler: Presumably, if the end time in the second table is NULL, it should be treated as 'the period does not have an end time and continues into the future'. That's what the accepted answer does; it is a common interpretation, but it isn't the only possible interpretation.
• RBT: Merge Overlapping Intervals algorithm can give some leads.
• Steven A. Lowe: @RBT: "Please phrase your answer in the form of a SQL WHERE-clause" :-)

Answer 1

SELECT * 
FROM table1,table2 
WHERE table2.start <= table1.end 
AND (table2.end IS NULL OR table2.end >= table1.start)

• Oskar Berggren: Note that if start and end denotes instances in time, you may want to remove the equality from the comparisons, otherwise two ranges will be considered intersecting even if one end at the same instance in time as the other starts.
• Stanislav: Prove: If two intervals not overlap then (s2 > e1 || e2 < s1). If they overlap then invert the condition: (! (s2 > e1 || e2 < s1)). Now it can be simplified using !(a||b) = !a && !b. Hence (s2 <= e1 && e2 >= s1).

Answer 2

It's sound very complicated until you start working from reverse. Below I illustrated ONLY GOOD CASES (no overlaps)! defined by those 2 simple conditions, we have no overlap ranges if condA OR condB is TRUE, so we going to reverse those: NOT condA AND NOT CondB, in our case I just reversed signs (> became <=)

/*
|--------| A \___ CondA: b.ddStart > a.ddEnd
 |=========| B / \____ CondB: a.ddS > b.ddE
 |+++++++++| A /
*/
--DROP TABLE ran
create table ran ( mem_nbr int, ID int, ddS date, ddE date)
insert ran values 
(100, 1, '2012-1-1','2012-12-30'), ----\ ovl
(100, 11, '2012-12-12','2012-12-24'), ----/
(100, 2, '2012-12-31','2014-1-1'),
(100, 3, '2014-5-1','2014-12-14') ,
(220, 1, '2015-5-5','2015-12-14') , ---\ovl
(220, 22, '2014-4-1','2015-5-25') , ---/
(220, 3, '2016-6-1','2016-12-16') 
select DISTINCT a.mem_nbr , a.* , '-' [ ], b.dds, b.dde, b.id 
FROM ran a
join ran b on a.mem_nbr = b.mem_nbr -- match by mem#
 AND a.ID <> b.ID -- itself
 AND b.ddS <= a.ddE -- NOT b.ddS > a.ddE 
 AND a.ddS <= b.ddE -- NOT a.ddS > b.ddE 

• Steven A. Lowe: end date can be null :/

Answer 3

"solutions from other platforms may be of interest also."

SQL Standard defines OVERLAPS predicate:

Specify a test for an overlap between two events.

<overlaps predicate> ::=
 <row value constructor 1> OVERLAPS <row value constructor 2> 

Example:

SELECT 1
WHERE ('2020-03-01'::DATE, '2020-04-15'::DATE) OVERLAPS 
 ('2020-02-01'::DATE, '2020-03-15'::DATE) 
-- 1

db<>fiddle demo

Answer 4

select * from table_1 
right join 
table_2 on 
(
table_1.start between table_2.start and table_2.[end]
or
table_1.[end] between table_2.start and table_2.[end]
or
(table_1.[end] > table_2.start and table_2.[end] is null)
)

EDIT: Ok, don't go for my solution, it perfoms like shit. The "where" solution is 14x faster. Oops...

Some statistics: running on a db with ~ 65000 records for both table 1 and 2 (no indexing), having intervals of 2 days between start and end for each row, running for 2 minutes in SQLSMSE (don't have the patience to wait)

Using join: 8356 rows in 2 minutes

Using where: 115436 rows in 2 minutes

Answer 5

And what, if you want to analyse such an overlap on a minute precision with 70m+ rows? the only solution i could make up myself was a time dimension table for the join

else the dublicate-handling became a headache .. and the processing cost where astronomical

• Guillaume OSTORERO: i recommend to see this post for those needs to take in account the performance of sql query : red-gate.com/simple-talk/databases/sql-server/…