Query to find records without time intersection

Question

Say I have the following tables :

tab1

for_key1 (key)
val1,
start time 1 (key),
end time 1 (key) 

tab2

for_key2 (key)
val2
start time 1 (key)
end time 2 (key)

Notes :

• key1 and key2 are the foreign keys between the two tables.
• It's guaranteed there aren't two records with the same (key, start time, end time) combination

I would like to produce a query which returns all the records from tab1 which doesn't intersect (in a time perspective) with any record from tab2.

For example :

tab1
----
key, val1, start time = 1, end time = 4
key, val2, start time = 6, end time = 10
key, val3, start time = 13, end time = 17

tab2
----
key, val, start time = 5, end time = 8

The query will return :

key, val1, start time = 1, end time = 4
key, val3, start time = 13, end time = 17

Do you have an idea how can I do it?

Answer 1

See this answers Determine Whether Two Date Ranges Overlap on how to determine whether two ranges overlap.

According to the above answer, the below query will give overlapped ranges:

SELECT a.*
FROM tab1 a
JOIN tab2 b
ON a.start_time <= b.end_time
   AND
   a.end_time >= b.start_time
;

Since not overlapped ranges are required, just negate the join condition in the above query:

SELECT a.*
FROM tab1 a
JOIN tab2 b
ON not ( a.start_time <= b.end_time
         AND
         a.end_time >= b.start_time
   )
;

In accordance with deMorgan's laws, this can be further simplified into:

SELECT a.*
FROM tab1 a
JOIN tab2 b
ON a.start_time > b.end_time
   OR
   a.end_time < b.start_time
;

See a demo here: http://www.sqlfiddle.com/#!15/d46fc/3