Add subquery in query – Oracle SQL

My query return all days in month.

SELECT 
       EXTRACT( DAY FROM day ) ||' '|| substr(TO_CHAR( day, 'fmDAY' ),0,3) AS day,
       EXTRACT( DAY FROM day ) as day_id
FROM (
  WITH temp ( col ) AS (
    SELECT to_date(2, 'mm') --2 is February
    FROM   dual 
  )
  SELECT col + level - 1 AS day
  FROM   temp 
  CONNECT BY level <= last_day(col) - col + 1
  ORDER BY day
)

How get all days from query where DAY_ID not in ==> (Select day_id from table1)

Eg. table1 return 5,10,15

Query resault need to display all days except 5,10,15

Answer

You can generate your calendar and then use NOT EXISTS:

WITH month ( col ) AS (
  SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 2 - 1)
  FROM   DUAL
),
calendar ( day, end_day ) AS (
  SELECT col, LAST_DAY(col)
  FROM month
UNION ALL
  SELECT day + INTERVAL '1' DAY, end_day
  FROM   calendar
  WHERE  day < end_day
)
SELECT EXTRACT( DAY FROM day ) ||' '|| TO_CHAR( day, 'fmDY' ) AS day,
       EXTRACT( DAY FROM day ) as day_id
FROM   calendar c
WHERE  NOT EXISTS (
  SELECT 1
  FROM   table1 t
  WHERE  c.day = t.day_id
)
ORDER BY c.day;

Which, for your sample data:

CREATE TABLE table1 ( day_id ) AS
SELECT DATE '2021-02-05' FROM DUAL UNION ALL
SELECT DATE '2021-02-10' FROM DUAL UNION ALL
SELECT DATE '2021-02-15' FROM DUAL;

Outputs:

DAY	DAY_ID
1 MON	1
2 TUE	2
3 WED	3
4 THU	4
6 SAT	6
7 SUN	7
8 MON	8
9 TUE	9
11 THU	11
12 FRI	12
13 SAT	13
14 SUN	14
16 TUE	16
17 WED	17
18 THU	18
19 FRI	19
20 SAT	20
21 SUN	21
22 MON	22
23 TUE	23
24 WED	24
25 THU	25
26 FRI	26
27 SAT	27
28 SUN	28

db<>fiddle here