Mysql Select Query to find items also partially free between two given dates

Without window function :

There is a way to extract by dividing into three parts: "From start date", "Till end date" and "Others", and filter the rows by each query, like this:

SET @sdate = DATE'2021-07-24';
SET @edate = DATE'2021-08-06';

-- From start date
SELECT
    b.id_item id_item,
    @sdate from_date,
    DATE_ADD(MIN(b.occupancy_start_date), INTERVAL -1 DAY) to_date
FROM TableB b
GROUP BY b.id_item
HAVING from_date <= to_date
-- Till end date
UNION ALL
SELECT
    b.id_item id_item,
    DATE_ADD(MAX(b.occupancy_end_date), INTERVAL 1 DAY) from_date,
    @edate to_date
FROM TableB b
GROUP BY b.id_item
HAVING from_date <= to_date
-- Others
UNION ALL
SELECT
    b1.id_item,
    DATE_ADD(b1.occupancy_end_date, INTERVAL 1 DAY),
    DATE_ADD(b2.occupancy_start_date, INTERVAL -1 DAY)
FROM TableB b1 INNER JOIN TableB b2
    ON b1.id_item=b2.id_item AND
    b1.occupancy_end_date < b2.occupancy_start_date AND
    -- Exclude inappropriate rows
    NOT EXISTS (SELECT 1 FROM TableB b WHERE
        b1.id_item = b.id_item AND (
        ( b1.occupancy_end_date < b.occupancy_start_date AND
          b.occupancy_start_date < b2.occupancy_start_date) OR
        ( b1.occupancy_end_date < b.occupancy_end_date AND
          b.occupancy_end_date < b2.occupancy_start_date) ) )
ORDER BY 1,2
;

DB Fiddle

With window function :

If MySQL8, you can use the LAG (or LEAD) function, like this:

SET @sdate = DATE'2021-07-24';
SET @edate = DATE'2021-08-06';

SELECT
    a.ITEM_NAME,
    w.id_item,
    w.from_date,
    w.to_date
FROM (
  -- Non-free period(s) exist
  SELECT * FROM (
    SELECT
      id_item,
      DATE_ADD(LAG(occupancy_end_date, 1)
        OVER (PARTITION BY id_item ORDER BY occupancy_end_date),
        INTERVAL 1 DAY) from_date,
      DATE_ADD(occupancy_start_date, INTERVAL -1 DAY) to_date
    FROM TableB
    WHERE @sdate < occupancy_end_date AND occupancy_start_date < @edate
  ) t
  WHERE from_date <= to_date
  -- From start date
  UNION ALL
  SELECT
      id_item,
      @sdate from_date,
      DATE_ADD(MIN(occupancy_start_date), INTERVAL -1 DAY) to_date
  FROM TableB
  GROUP BY id_item
  HAVING from_date <= to_date
  -- Till end date
  UNION ALL
  SELECT
      id_item,
      DATE_ADD(MAX(occupancy_end_date), INTERVAL 1 DAY) from_date,
      @edate to_date
  FROM TableB
  GROUP BY id_item
  HAVING from_date <= to_date
  -- No occupancy
  UNION ALL
  SELECT
      ID,
      @sdate from_date,
      @edate to_date
  FROM TableA a
  WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE a.ID=b.id_item)
) w
INNER JOIN TableA a ON w.id_item=a.ID
ORDER BY
    w.id_item,
    w.from_date
;

DB Fiddle

Here's another starting point (using the fiddle generously provided by others - pushed up to a more recent version) ... I'll post a more complete answer next week, if no one's beaten me to it...

WITH RECURSIVE cte AS (
   SELECT '2021-07-24' dt
    UNION ALL
   SELECT dt + INTERVAL  1 DAY
     FROM cte
    WHERE dt < '2021-08-06')
   SELECT x.dt
        , a.*
     FROM cte x 
     JOIN TableA a
     LEFT
     JOIN TableB b
       ON b.id_item = a.id
      AND x.dt BETWEEN b.occupancy_start_date AND b.occupancy_end_date
    WHERE b.id_item IS NULL