Date range overlap detection in SQL - Step-by-Step Execution

Concept Flow - Date range overlap detection

Start with two date ranges

↓

Check if start1 <= end2

Yes↓

Check if end1 >= start2

Yes↓

Date ranges overlap

No↓

No overlap

↓

End

We check if two date ranges overlap by verifying if the start of one range is before or on the end of the other, and vice versa.

Execution Sample

SQL

SELECT * FROM events e1
JOIN events e2 ON e1.id != e2.id
WHERE e1.start_date <= e2.end_date
  AND e1.end_date >= e2.start_date;

This query finds pairs of events where their date ranges overlap.

Execution Table

Step	e1.start_date	e1.end_date	e2.start_date	e2.end_date	Condition e1.start_date <= e2.end_date	Condition e1.end_date >= e2.start_date	Overlap?
1	2024-01-01	2024-01-10	2024-01-05	2024-01-15	True	True	Yes
2	2024-01-01	2024-01-10	2024-01-11	2024-01-20	True	False	No
3	2024-02-01	2024-02-10	2024-01-25	2024-02-05	True	True	Yes
4	2024-03-01	2024-03-10	2024-03-11	2024-03-20	True	False	No
5	2024-04-01	2024-04-10	2024-03-25	2024-04-01	True	True	Yes
6	2024-05-01	2024-05-10	2024-05-11	2024-05-20	False	False	No
Exit	-	-	-	-	-	-	No more pairs to check

💡 All pairs checked; no more rows to compare.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
e1.start_date	2024-01-01	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	-	-
e1.end_date	2024-01-10	2024-01-10	2024-02-10	2024-03-10	2024-04-10	2024-05-10	-	-
e2.start_date	2024-01-05	2024-01-11	2024-01-25	2024-03-11	2024-03-25	2024-05-11	-	-
e2.end_date	2024-01-15	2024-01-20	2024-02-05	2024-03-20	2024-04-01	2024-05-20	-	-
Condition e1.start_date <= e2.end_date	True	True	True	True	True	False	-	-
Condition e1.end_date >= e2.start_date	True	False	True	False	True	False	-	-
Overlap?	Yes	No	Yes	No	Yes	No	-	-

Key Moments - 2 Insights

Why do we check both conditions e1.start_date <= e2.end_date and e1.end_date >= e2.start_date?

Because both conditions together ensure the date ranges actually overlap. Checking only one condition can give false positives. See execution_table rows 2 and 4 where one condition is true but the other is false, so no overlap.

Why do we exclude pairs where e1.id = e2.id in the join?

Because an event cannot overlap with itself. The condition e1.id != e2.id avoids comparing the same row to itself, which would always overlap.

Visual Quiz - 3 Questions

Look at the execution_table, at step 3, what is the value of condition e1.end_date >= e2.start_date?

A Unknown

B True

C False

D Not checked

💡 Hint

Check the column 'Condition e1.end_date >= e2.start_date' at row 3 in execution_table.

At which step does the overlap condition evaluate to No because e1.end_date < e2.start_date?

A Step 2

B Step 1

C Step 5

D Step 6

💡 Hint

Look for rows where 'Overlap?' is No and 'Condition e1.end_date >= e2.start_date' is False.

If e1.start_date was after e2.end_date, what would happen to the overlap condition?

A Overlap would be Unknown

B Overlap would be Yes

C Overlap would be No

D Overlap would depend on other conditions

💡 Hint

Refer to the first condition 'e1.start_date <= e2.end_date' in the concept_flow and execution_table.

Concept Snapshot

Date range overlap detection:
Check if two ranges overlap by verifying:
start1 <= end2 AND end1 >= start2
If both true, ranges overlap.
Use in SQL JOIN conditions to find overlapping rows.

Full Transcript

This visual execution shows how to detect if two date ranges overlap in SQL. We compare two date ranges by checking if the start of the first range is on or before the end of the second, and if the end of the first range is on or after the start of the second. The execution table walks through example pairs of dates, showing which conditions are true or false and whether the ranges overlap. The variable tracker shows how the dates and conditions change step by step. Key moments clarify why both conditions are needed and why we exclude comparing the same row. The quiz tests understanding of the conditions and their results. This method helps find overlapping events or bookings in databases.