MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE calendar (
  `ID` INTEGER,
  `date` VARCHAR(10)
);

INSERT INTO calendar
  (`ID`, `date`)
VALUES
  ('01', '2021-12-01'),
  ('02', '2021-12-02'),
  ('03', '2021-12-03'),
  ('04', '2021-12-04'),
  ('05', '2021-12-05'),
  ('06', '2021-12-06'),
  ('07', '2021-12-07'),
  ('08', '2021-12-08'),
  ('09', '2021-12-09'),
  ('10', '2021-12-10'),
  ('11', '2021-12-11');
CREATE TABLE users (
  `ID` INTEGER,
  `name` VARCHAR(6),
  `num_employee` INTEGER
);

INSERT INTO users
  (`ID`, `name`, `num_employee`)
VALUES
  ('01', 'Andrew', '101'),
  ('02', 'Mary', '102');
CREATE TABLE attendances (
  `ID` INTEGER,
  `date` VARCHAR(10),
  `num_employee` INTEGER
);

INSERT INTO attendances
  (`ID`, `date`, `num_employee`)
VALUES
  ('01', '2021-12-03', '101'),
  ('02', '2021-12-04', '101'),
  ('03', '2021-12-03', '102'),
  ('04', '2021-12-04', '102'),
  ('05', '2021-12-05', '101'),
  ('06', '2021-12-06', '102');
CREATE TABLE vacations (
  `id` INTEGER,
  `initial_date` VARCHAR(10),
  `final_date` VARCHAR(10),
  `num_employee` INTEGER
);

INSERT INTO vacations
  (`id`, `initial_date`, `final_date`, `num_employee`)
VALUES
  ('01', '2021-12-07', '2021-12-09', '101');
SELECT v.`num_employee`, GROUP_CONCAT(DAY(c.`date`))
FROM vacations v INNER JOIN calendar c ON c.`date` BETWEEN v.`initial_date` AND `final_date`
AND c. date BETWEEN '2021-12-01' AND '2021-12-31'
GROUP BY v.`num_employee`


num_employee
GROUP_CONCAT(DAY(c.`date`))


101
7,8,9


SELECT u.num_employee,
       u.name,
       a.date AS attendances,
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),IFNULL(a.date,''))
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),IFNULL(vac.vac_days,''))
           ) as faults 
FROM users u
LEFT JOIN  (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.num_employee = u.num_employee
LEFT JOIN (SELECT v.`num_employee`, GROUP_CONCAT(DAY(c.`date`)) vac_days
FROM vacations v INNER JOIN calendar c ON c.`date` BETWEEN v.`initial_date` AND `final_date`
AND c. date BETWEEN '2021-12-01' AND '2021-12-31'
GROUP BY v.`num_employee`) vac ON vac.`num_employee` = u.num_employee


num_employee
name
attendances
faults


101
Andrew
3,4,5
1,2,6,10,11

102
Mary
3,4,6
1,2,5,7,8,9,10,11