Oracle 18c

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

CREATE TABLE Employee (Emp_ID, Emp_Name) AS
SELECT 1, 'Alice' FROM DUAL UNION ALL
SELECT 2, 'Beryl' FROM DUAL;
2 rows affected

CREATE TABLE WorkDays (Emp_ID, "DATE") AS
WITH calendar (dt) AS (
  SELECT DATE '2021-10-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 32
)
SELECT 1, dt FROM calendar WHERE dt NOT IN (DATE '2021-10-01', DATE '2021-10-03', DATE '2021-10-06')
UNION ALL
SELECT 2, dt FROM calendar WHERE dt NOT IN (DATE '2021-10-02', DATE '2021-10-03')
59 rows affected

WITH calendar (day) AS (
  SELECT DATE '2021-10-01' FROM DUAL
UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2021-11-01'
)
SELECT w.emp_id,
       MAX(emp_name) AS emp_name,
       LISTAGG(TO_CHAR(c.day, 'YYYY-MM-DD'), ', ') WITHIN GROUP (ORDER BY c.day)
         AS non_work_days
FROM   calendar c
       LEFT OUTER JOIN workdays w
       PARTITION BY (w.emp_id)
       ON (c.day = w."DATE")
       LEFT OUTER JOIN employee e
       ON (w.emp_id = e.emp_id)
WHERE  w."DATE" IS NULL
GROUP BY w.emp_id;


EMP_ID
EMP_NAME
NON_WORK_DAYS


1
Alice
2021-10-01, 2021-10-03, 2021-10-06

2
Beryl
2021-10-02, 2021-10-03


WITH calendar (day) AS (
  SELECT DATE '2021-10-01' FROM DUAL
UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2021-11-01'
)
SELECT e.emp_id,
       MAX(e.emp_name) AS emp_name,
       LISTAGG(TO_CHAR(c.day, 'YYYY-MM-DD'), ', ') WITHIN GROUP (ORDER BY c.day)
         AS non_work_days
FROM   calendar c
       CROSS JOIN employee e
       LEFT OUTER JOIN workdays w
       ON (c.day = w."DATE" AND e.emp_id = w.emp_id)
WHERE  w."DATE" IS NULL
GROUP BY e.emp_id;


EMP_ID
EMP_NAME
NON_WORK_DAYS


1
Alice
2021-10-01, 2021-10-03, 2021-10-06

2
Beryl
2021-10-02, 2021-10-03

EMP_ID	EMP_NAME	NON_WORK_DAYS
1	Alice	2021-10-01, 2021-10-03, 2021-10-06
2	Beryl	2021-10-02, 2021-10-03