"no join predicate" warning with outer join

Question

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.

Answer 1

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).

Answer 2

If we look at the query mentioned in the comment on the question:

FROM t_product
JOIN t_order 
    ON t_order.customerid = 101 
JOIN t_orderItem 
    ON t_product.productid = t_orderItem.productid 
WHERE t_order.orderid=t_orderItem.orderid

Since we are now dealing only with INNER JOINs we can logically move predicates between the JOIN clause and the WHERE clause. We can also change the order of the JOINs. I would think of this as:

• An ORDER has ORDERITEMS

  FROM t_order  
  JOIN t_orderItem 
      ON t_order.orderid=t_orderItem.orderid
  

• An ORDERITEM references a PRODUCT

       t_orderItem 
  JOIN t_product      
      ON t_product.productid = t_orderItem.productid
  

• A CUSTOMER orders an ORDER

       WHERE t_order.customerid = 101 
  

Gluing this together would be:

SELECT t_product.productid
    ,  t_order.orderid
    ,  t_orderItem.itemid
FROM t_order  
JOIN t_orderItem 
    ON t_order.orderid=t_orderItem.orderid
JOIN t_product      
    ON t_product.productid = t_orderItem.productid  
WHERE t_order.customerid = 101 

If you want to have ORDERs that not yet have any ORDERITEMS you could use a LEFT JOIN between ORDER and ORDERITEMS (I don't think it will be any use in having an ORDERITEM without a PRODUCT)

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

I would say that the intent of these queries is easier to understand than the query we started with.