"no join predicate" warning with outer join I have a database schema that looks something like this:

create table t_order (orderid int, customerid int)
create unique clustered index ix_order on t_order(orderid)
create index ix_order_1 on t_order(customerid)

create table t_product (productid int)
create unique clustered index ix_product on t_product(productid)

create table t_orderItem (itemid int, orderid int, productid int)
create unique clustered index ix_orderItem on t_orderItem(itemid)
create index ix_orderItem_1 on t_orderItem(orderId, productId)
create index ix_orderItem_2 on t_orderItem(productId, orderId)


(i.e. the orderItem table is a many-to-many between orders and products). Now I have an SQL statement like this:

SELECT 
    t_product.productid,
    t_order.orderid,
    t_orderItem.itemid
FROM
    t_product
    JOIN t_order ON t_order.customerid = 101 
    LEFT OUTER JOIN t_orderItem ON t_product.productid = t_orderItem.productid AND t_order.orderid=t_orderItem.orderid 


when there are a lot of rows in the database (and with statistics updated), I get the "no join predicate" warning on the left outer join, and the query takes a long time to execute.

Unfortunately it's not code I wrote myself and I don't really understand what that outer join is trying to do. I quite expect the SQL is "wrong" (i.e. doesn't do what the author intended) but I can't fix it unless I can get a handle on what it's actually doing at the moment.

I have seen other examples on the net of using AND inside the ON clause, but not involving other tables that aren't part of the join.

Can someone explain what the effect of this join is and why it causes a Cartesian product to be generated.
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).