"no join predicate" warning with outer join
Not a complete answer but the joins in the FROM clause are equivalent to a CROSS JOIN and then a LEFT JOIN to the 3rd table. This rewrite may help you understand better what the query is doing:

FROM
        t_product
    CROSS JOIN 
        (SELECT * FROM t_order WHERE customerid = 101) AS t_order

    LEFT OUTER JOIN 
        t_orderItem 
    ON  t_product . productid = t_orderItem . productid
    AND t_order   . orderid   = t_orderItem . orderid


It will basically return all combinations of all the orders of a specific customer and all products (and whether each product was an item of each order).