SQL - Cross Join when Left Join isn't met

Question

I am unsure if this is even the correct way to do this but I'll explain the situation.

I have two tables, the first table is a list of products while the second is a list of channels. I want to do a LEFT JOIN if the join condition is met else I want to explode the rows for every channel in the CHANNEL table. I've read about CROSS JOINs but not sure if that is the way to go.

SELECT P.PRODUCT_ID, C.CHANNEL, C.REGION, C.COUNTRY, C.LANGUAGE FROM PRODUCT P LEFT JOIN CHANNEL C ON C.CHANNEL = P.CHANNEL;

Product Table

Product_ID | Product_Name | Channel
------------------------------------
12345678   | Product1     | Ecommerce
87654321   | Product2     | 
14785236   | Product3     | Outlet

Channel Table

CHANNEL   | REGION   | COUNTRY | LANGUAGE
--------------------------------------
Ecommerce | Americas | Canada  | EN
Ecommerce | Americas | Canada  | FR
Outlet    | Americas | Canada  | EN
Outlet    | Americas | Canada  | FR
[...]

I'm expecting this output:

PRODUCT_ID | CHANNEL | REGION | COUNTRY | LANGUAGE
---------------------------------------------------
12345678 | Ecommerce | Americas | Canada  | EN
12345678 | Ecommerce | Americas | Canada  | FR
87654321 | Ecommerce | Americas | Canada  | EN
87654321 | Ecommerce | Americas | Canada  | FR
87654321 | Outlet    | Americas | Canada  | EN
87654321 | Outlet    | Americas | Canada  | FR
[...]
14785236 | Outlet    | Americas | Canada  | EN
14785236 | Outlet    | Americas | Canada  | FR

I am working in an Oracle DB.

Guidance would very much appreciate for this.

Answer 1

I think you need an inner join where the condition is a channel match (so as to get that one channel joined) or product.channel is null (so as to get all channels joined):

SELECT p.product_id, c.channel, c.region, c.country, c.language
  FROM product p
  JOIN channel c on c.channel = p.channel OR p.channel IS NULL;