在SQL中查找具有时间间隔重叠的行的简单而有效的方法是什么？

Question

我有两个表，都有开始时间和结束时间字段。对于第一个表中的每一行，我需要查找第二个表中时间间隔相交的所有行。

例如：

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

请以SQL WHERE-clause的形式表述您的答案，并考虑第二个表中的结束时间可能为NULL的情况。

目标平台是SQL Server 2005，但也可能对其他平台的解决方案感兴趣。

Answer 1

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

Answer 2

“来自其他平台的解决方案可能也会感兴趣。

SQL标准定义了谓词：

指定两个事件之间重叠的测试。

::= <行值构造函数1>重叠<行值构造函数2>

示例：

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

Answer 3

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)
)

编辑：好的，不要用我的解决方案，它像狗屎一样完美。"where“解决方案的速度要快14倍。哦..。

一些统计数据:在数据库上运行，表1和表2都有大约65000条记录(没有索引)，每行的开始和结束间隔为2天，在SQLSMSE中运行2分钟(没有耐心等待)

使用join: 2分钟内有8356行

使用where: 2分钟内115436行