MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Shift (shift_id INT, shift_day INT, shift_start VARCHAR(20), shift_end VARCHAR(20), shift_function_id INT);
INSERT INTO Shift VALUES (45,
6,
'16:30:00',
'20:00:00',
27), (46,
6,
'16:30:00',
'20:00:00',
27),(47,
6,
'16:30:05',
'20:00:00',
28)
SELECT * FROM Shift


shift_id
shift_day
shift_start
shift_end
shift_function_id


45
6
16:30:00
20:00:00
27

46
6
16:30:00
20:00:00
27

47
6
16:30:05
20:00:00
28


CREATE TABLE Schedule AS (SELECT 310 schedule_id, 
                                 '2020-01-11' schedule_date,
                                 '16:30:00' schedule_start,
                                 '20:00:00' schedule_end,
                                 27 schedule_function_id);
SELECT * FROM Schedule


schedule_id
schedule_date
schedule_start
schedule_end
schedule_function_id


310
2020-01-11
16:30:00
20:00:00
27


SELECT S1.shift_id, S1.shift_day, S1.shift_start, S1.shift_end
FROM Shift S1
WHERE NOT EXISTS (
    SELECT 1
    FROM Schedule
    WHERE schedule_start = S1.shift_start
    AND schedule_end = S1.shift_end
    AND schedule_function_id = S1.shift_function_id
)
UNION ALL
SELECT MIN(S1.shift_id), S1.shift_day, S1.shift_start, S1.shift_end
FROM Shift S1
JOIN Shift S2 ON S1.shift_id <> S2.shift_id
              AND S1.shift_day = S2.shift_day
              AND S1.shift_start = S2.shift_start
              AND S1.shift_end = S2.shift_end
GROUP BY S1.shift_day, S1.shift_start, S1.shift_end
ORDER BY shift_start, shift_end;


shift_id
shift_day
shift_start
shift_end


45
6
16:30:00
20:00:00

47
6
16:30:05
20:00:00

shift_id	shift_day	shift_start	shift_end	shift_function_id
45	6	16:30:00	20:00:00	27
46	6	16:30:00	20:00:00	27
47	6	16:30:05	20:00:00	28