Table result as a table parameter

I have a table with dates parameters PERIODS, and another one with dates and price PRICES.

I want to use dates from table PERIODS as parameters on PRICES, but to return zero when there is no date in the PRICES table.

WITH PERIODS AS 
(
    SELECT
        1                                   cd,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_1_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_1_end,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_2_start,
        TO_DATE('2024-06-04', 'YYYY-MM-DD') period_2_end
    FROM
        dual
    UNION
    SELECT
        2                                   cd,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_1_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_1_end,
        TO_DATE('2024-06-01', 'YYYY-MM-DD') period_2_start,
        TO_DATE('2024-06-02', 'YYYY-MM-DD') period_2_end
    FROM
        dual
), 
PRICES AS  
(
    SELECT 
        1 cd, TO_DATE('2024-06-01', 'YYYY-MM-DD') dates, 10 price      
    FROM dual 
    UNION 
    SELECT 
        1 cd, TO_DATE('2024-06-02', 'YYYY-MM-DD') dates, 20 price  
    FROM dual 
    UNION
    SELECT 
        1 cd, TO_DATE('2024-06-03', 'YYYY-MM-DD') dates, 30 price   
    FROM dual 
    UNION 
    SELECT
        2 cd, TO_DATE('2024-06-03', 'YYYY-MM-DD') dates, 40 price 
    FROM dual
)
SELECT *  
FROM prices pc 
JOIN PERIODS prs ON pc.cd = prs.cd
                 AND prs.period_1_start = pc.dates --- ????
                 -- AND prs.period_2_start = pc.dates  --- ???
  

Expected result:

  select 1 cd, 10 + 20 total_price1,20 + 0  total_price2       from dual union

If I understood the requirements correctly, you can:

1. switch JOIN to LEFT JOIN
2. add a condition the LEFT JOIN
3. for the second price add another LEFT JOIN
4. use CASE WHEN in total_price calculation

SELECT
  pc.cd,
  SUM(CASE WHEN prs1.cd IS NOT NULL THEN pc.price ELSE 0 END) AS total_price1,
  SUM(CASE WHEN prs2.cd IS NOT NULL THEN pc.price ELSE 0 END) AS total_price2
FROM prices pc 
LEFT JOIN periods prs1
    ON pc.cd = prs1.cd
    AND pc.dates >= prs1.period_1_start
    AND pc.dates <= prs1.period_1_end 
LEFT JOIN periods prs2
    ON pc.cd = prs2.cd
    AND pc.dates >= prs2.period_2_start
    AND pc.dates <= prs2.period_2_end
GROUP BY pc.cd