How could I get the value as Y or N in one table if value is exists on another that meets certain condition?
Seems like you need all combinations of products and customers (so a CROSS JOIN between the two tables) and then a correlated EXISTS subquery. This could either be a correlated subquery in the SELECT list:

SELECT 
    c.customer_id, 
    p.product_id,
    CASE WHEN EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) 
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c ; 


or a LEFT JOIN to a dummy table with one row (the dual does that in Oracle):

SELECT 
    c.customer_id, 
    p.product_id,
    CASE WHEN d.dummy IS NOT NULL 
         THEN 'Y' ELSE 'N'
    END AS order_stat
FROM
    product  p
  CROSS JOIN
    customer  c 
  LEFT JOIN
    dual  d
      ON EXISTS
              ( SELECT *
                FROM "order"  o
                WHERE o.product_id = p.product_id
                  AND o.customer_id = c.customer_id
              ) ; 




Random notes:


It's not very good to use reserved keywords in table or column names, like the order. Because you'll have to quote them every time they are used.