Find overlapping and non overlapping intervals in MySQL

Question

I have a typical events table: events(id, start, end)

I want to chop them into intervals:

           I1    I2    I3     I4       I5
EVENT 1    <---|-----|----|-------->
EVENT 2              <----|--------|------->
EVENT 3        <-----|--->|

If I write my query like this I don't get non overlapping parts:

SELECT greatest(E1.start, E2.start) as O1, least(E1.end, E2.end) as O2
FROM events E1, events E2
WHERE E1.id <> E2.id
HAVING O1 < O2

The expected result result should contain 5 records like in the graphic above.

MySQL version 8+

SQL fiddle

Answer 1

CREATE TABLE event (
  id      INT,
  start   INT, 
  end     INT
)

INSERT INTO
  event
VALUES
  (1, 1, 9),
  (2, 3, 7),
  (3, 2, 5)

WITH
  boundaries AS
(
  SELECT start FROM event
  UNION
  SELECT end   FROM event
),
  pieces AS
(
  SELECT
    start,
    LEAD(start)
      OVER (
        ORDER BY start
      )
        AS end
  FROM
    boundaries
)
SELECT
  *
FROM
  pieces
WHERE
  end IS NOT NULL
ORDER BY
  start

start	end
1	2
2	3
3	5
5	7
7	9

fiddle