Ranges overlap - MySQL

Question

Does anyone know how to find ranges that overlap, using MySQL? Essentially, as seen on table below (just for illustrating the problem as the actual table contains 1000+ ranges), I am trying to fetch all ranges that overlap inside of a table.

Thanks!

RANGES

| count | Begin      | End  |        Comment                |
|  1    | 1001       | 1095 | overlaps with ranges 2, 3     |                            
|  2    | 1005       | 1030 | overlaps with ranges 1, 3     |                
|  3    | 1017       | 1020 | overlaps with ranges 1, 2     |                  
|  4    | 1110       | 1125 | no overlap                    |  

Answer 1

One method is a self join and aggregation:

select r1.count, r1.begin, r1.end,
       group_concat(r2.count order by r2.count) as overlaps
from ranges r1 left join
     ranges r2
     on r1.end >= r2.begin and
        r1.begin <= r2.end and
        r1.count <> r2.count
group by r1.count, r1.begin, r1.end;

On a table with 1000 rows, this will not be fast, but it should be doable. You may want to validate the logic on a smaller table.

This assumes that count is really a unique identifier for each row.

Note that count and end are poor choices for column names because they are SQL keywords.

Here is a db<>fiddle.