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.