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.

Answer 1

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