SQL OVERLAPS

What is the SQL OVERLAPS operator used for?

Returns TRUE if two time periods intersect.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OVERLAPS: PostgreSQL, Oracle, DuckDB, H2, Firebird, IBM Db2, Standard SQL compliant engines. Not supported in MySQL, SQL Server, SQLite.

SQL OVERLAPS Full Explanation

OVERLAPS is a Standard SQL boolean operator that compares two date- or timestamp-based periods and evaluates to TRUE when the periods share at least one point in time. Each period is supplied as a row value of two temporal expressions: a start and an end. If a start value is later than its end, the values are automatically swapped before comparison, so the operator is order-agnostic. Boundaries are inclusive: an end instant equal to the other period’s start counts as an overlap. OVERLAPS is evaluated per row, making it useful in WHERE clauses and JOIN conditions for detecting scheduling conflicts, room or resource bookings, and temporal joins. The operator is deterministic and runs in O(1) time per row, but its availability is limited to dialects that implement the SQL standard feature F641.

SQL OVERLAPS Syntax

(start1, end1) OVERLAPS (start2, end2)

SQL OVERLAPS Parameters

- start1 TIMESTAMP/DATE - first period start value
- end1 (TIMESTAMP/DATE) - first period end value
- start2 TIMESTAMP/DATE - second period start value
- end2 (TIMESTAMP/DATE) - second period end value

Example Queries Using SQL OVERLAPS

-- Simple overlap test
SELECT (DATE '2024-01-01', DATE '2024-01-10') OVERLAPS
       (DATE '2024-01-05', DATE '2024-01-20') AS is_overlap;

-- Detect conflicting hotel bookings
SELECT b.booking_id, m.maintenance_id
FROM   bookings    AS b
JOIN   maintenance AS m
  ON  (b.start_date, b.end_date) OVERLAPS
      (m.start_date, m.end_date);

-- Allow unsorted endpoints
SELECT (TIMESTAMP '2024-02-10', TIMESTAMP '2024-02-01') OVERLAPS
       (TIMESTAMP '2024-02-05', TIMESTAMP '2024-02-07');

Expected Output Using SQL OVERLAPS

First query returns a single row with is_overlap = true
Second query returns all booking rows whose stay overlaps any maintenance window
Third query returns true because endpoints are swapped internally before comparison

Use Cases with SQL OVERLAPS

Checking schedule clashes between meetings or bookings
Validating that a new reservation does not intersect existing ones
Joining fact tables on overlapping validity periods (slowly changing dimensions)
Filtering logs for events active during a specified timeframe

Common Mistakes with SQL OVERLAPS

Assuming boundaries are exclusive; they are inclusive.
Supplying DATETIME values with mismatched data types (e.g., DATE vs TIMESTAMP) leading to implicit casts.
Expecting support in MySQL or SQL Server where OVERLAPS is not implemented.
Forgetting that (end,start) order is auto-corrected, masking data entry errors.

First Introduced In

SQL:1992 standard; first widely implemented in PostgreSQL 7.3

Frequently Asked Questions

What does SQL OVERLAPS return?

It returns a boolean value: TRUE if the two supplied periods intersect, otherwise FALSE.

Are the start and end arguments required to be in chronological order?

No. If a period is supplied with the end earlier than the start, the database engine swaps them internally before performing the comparison.

Does OVERLAPS consider boundary points inclusive?

Yes. If one period ends exactly when the other begins, OVERLAPS counts this as an overlap.

How can I emulate OVERLAPS in databases that do not support it?

Use explicit comparisons: `(start1 <= end2) AND (end1 >= start2)`. Make sure to handle inclusive boundaries consistently.