OVERLAPS predicate

OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE.

Let us create a test table with three rows – one with overlapping dates, second with non-overlapping dates and third with matching end points.

SQL> CREATE TABLE t
  2  AS
  3  SELECT 'Overlapping Row' ID, SYSDATE-10 s1, SYSDATE-5 e1, SYSDATE-7 s2, SYSDATE e2
  4  	FROM DUAL
  5    UNION ALL
  6    SELECT 'Non-Overlapping Row' ID, SYSDATE-10 s1, SYSDATE-5 e1, SYSDATE-4 s2, SYSDATE e2
  7    FROM DUAL
  8    UNION ALL
  9    SELECT 'Matching End Points' ID, SYSDATE s1, SYSDATE e1, SYSDATE s2, SYSDATE e2
 10    FROM DUAL;

Table created.

SQL> SELECT * FROM t;

ID                  S1        E1        S2        E2
------------------- --------- --------- --------- ---------
Overlapping Row     05-MAR-10 10-MAR-10 08-MAR-10 15-MAR-10
Non-Overlapping Row 05-MAR-10 10-MAR-10 11-MAR-10 15-MAR-10
Matching End Points 15-MAR-10 15-MAR-10 15-MAR-10 15-MAR-10

As you see, when you apply the OVERLAPS predicate as shown below – First row is returned by the query as it overlaps perfectly. Second row does not overlap; hence it is not part of the result returned by the query. If you watch closely, the third row is an overlapping candidate, but the overlaps predicate not considering the matching end points as overlaps.

SQL> SELECT *  FROM t
  2  WHERE (s1,e1) OVERLAPS (s2,e2);

ID                  S1        E1        S2        E2
------------------- --------- --------- --------- ---------
Overlapping Row     05-MAR-10 10-MAR-10 08-MAR-10 15-MAR-10                          

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298                                                          

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    47 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    47 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------           

Predicate Information (identified by operation id):
---------------------------------------------------                                  

   1 - filter((INTERNAL_FUNCTION("S1"),INTERNAL_FUNCTION("E1"))OVERLAPS(
              INTERNAL_FUNCTION("S2"),INTERNAL_FUNCTION("E2")))

The OVERLAPS predicate can be represented in a pseudo form below:

WHERE (s1 > s2 AND NOT (s1 >= e2 AND e1 >= e2))
   OR (s2 > s1 AND NOT (s2 >= e1 AND e2 >= e1))
   OR (s1 = s2 AND (e1 <> e2 OR e1 = e2))

Oracle does have some references to OVERLAPS in documentation – 8i, 9i, 10g (Error Messages, Reserve Words), and 11g (Error Messages, Reserve Words).

ORA-30085: syntax error was found in overlaps predicate
    Cause: A syntax error was found during parsing an overlaps predicate.
    Action: Correct the syntax.

For some reason the OVERLAPS predicate remains undocumented for many years; it should not be used unless instructed by Oracle support.