1

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.

4

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

  • Yes thanks I can finally see it now. Because my original query was actually much more complicated and had aggregation in, I didn't spot that this fragment actually returns a whole cross product - i thought the optimizer was just using a cross product internally. – Andy May 4 '18 at 5:44
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.

  • Thanks this is really useful. I still need to do a bit more research to try and find out whether the intention was to outer join on orders or items or neither (this fragment is a much smaller part of the real query) but I will definitely restructure it to use this approach. I've marked @ypercube's reply as the actual answer because that was the one that flicked the light bulb in my brain so I could understand how the original syntax worked. – Andy May 4 '18 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.