Left Join not good?

Greetings,

My DBA was just by discussing some problems we have had with our 5.1 install. In particular how some queries were making a bunch of "temp" tables and not cleaning up after themselves.

I then said, that most of my queries were very simple (indeed classified as simply by "explain") and that at most I was using left joins to string tables together.

The DBA said: "Oh, Left joins ... that may not be the best way".

Question: What is wrong with left joins on keys?

ie:

Select A, B, C
FROM TABLE OrderDetail
LEFT JOIN OrderHeader on OrderDetail.Order_ID = OrderHeader.ID
LEFT JOIN Customer on OrderHeader.Cust_ID = Customer.ID
WHERE Customer.ID = 123

Isn't that the proper way to grab all the OrderDetail lines for a Customer if the Customer ID is only stored in the OrderHeader table?

And if the items on the order don't have product names, would the query not change to:

Select A, B, C, Item.ProductName
FROM TABLE OrderDetail
LEFT JOIN Item on OrderDetail.Item_ID = Item.ID
LEFT JOIN OrderHeader on OrderDetail.Order_ID = OrderHeader.ID
LEFT JOIN Customer on OrderHeader.Cust_ID = Customer.ID
WHERE Customer.ID = 123

Without comment on data structure :-), isn't that the fastest, cleanest way to get the data out of the DB?

if you don't look at the data structure, how do you know what data to get and if you would need inner or left ?




it don't use index at the joined table.

with LEFT you are saying you want all orderDetails, no matter if it does or does not contains any items or orderHeader.
same with getting all orderHeader, even if user don't exists.


try read this post for more about INNER vs LEFT

and last, i would change the WHERE in

to a AND in the join


as using WHERE would perform a CROSS JOIN and get all orders for each user first, and then limit the result for the specified user.

LEFT JOIN is using indexes if properly set.

Use LEFT JOIN, if the relation you have is 0,*. If you have 1,* use a simple join (In WHERE clause) instead.

Is your DBA a MySQL DBA or a "generic" DBA? You should work with him (trace to see the slow/big queries) to identify the exact query type which is causing problem.

He will have then to finetune the config or you will have to modify your queries/model.

I meant more of a "that data structure sucks" view


The joins are fully indexed as they are really reaching back up the data tree. There can not be a OrderDetail, without an OrderHead, and there can not be a OrderHead, without a Customer.

And those are the actual UK fields in the tables.


For some reason, when I think of "inner join", I think of it joining upon itself. I can't seem to wrap my head around when I would want to use a left vs a inner vs an outer (and isn't left / outer the same?)



Does that actually "speed" performance?

I currently put the Join statements in to get at Data fields not present in the base table. The where statement is dynamically built based upon what is passed into the method.

So unless there is a speed boost, I would want to keep using a dynamic where clause that is independent of the join statements.




That's actually not what is happening in the explain though. Since it's a backwards chain of indexed fields, it seems to be pulling exactly what it should ... so maybe my example was lacking something.

Main question was: Is left join the *fastest* method if everything is indexed properly (and in this case, they are actually the UK when linking upwards).

I think the answer was "yes" :-)

That is what I thought. Left Join, when indexed properly, is the way to go.


Not sure what that means.

0,* = 1 -> n
1,* = n -> n

Yes no?


I think a cross between the two :-) I believe more experience on the MSSQL then MySQL ... I think the statement made to me was more of a blanket statement.

0,* = 0, N (where N is bigger or equal to 1)
1,* = 1, N (where N is bigger or equal to 1)

Doesn't make sense in a context of 1-to-1 and 1-to-N (many) relationship.

So I am lost on that notation

can try give a examples, where you want customer and orders done.

you will use LEFT JOIN to get all customer, including those who haven't create a order yet, and inner INNER JOIN to only get those customer that has orders.

david, others have given you some good info, i will try to relate it back to your original query

Select A, B, C
FROM OrderDetail
LEFT JOIN OrderHeader on OrderDetail.Order_ID = OrderHeader.ID
LEFT JOIN Customer on OrderHeader.Cust_ID = Customer.ID
WHERE Customer.ID = 123

this query says "start with all OrderDetails" i.e. every item that was ever purchased

then join to OrderHeader, whether or not the OrderDetail has an OrderHeader = highly unlikely situation!!

knowing something about the data (by guessing the role of each table), i would have to conclude that this should not be a LEFT OUTER JOIN but rather an INNER JOIN

okay, now we have every item ever ordered, together with its order header

now join to the Customer table, whether or not the OrderHeader has a Customer = another highly unlikely situation!!

highly unlikely that you would accept an order from a customer who doesn't exist

so now we have all the items ever ordered, with their order headers, and their customers...

... and now you throw most of them away with the WHERE clause!!

doesn't sound efficient, right?



okay, now let's walk through this --

Select A, B, C
FROM Customer
LEFT JOIN OrderHeader on OrderHeader.Cust_ID = Customer.ID
LEFT JOIN OrderDetail on OrderDetail.Order_ID = OrderHeader.ID
WHERE Customer.ID = 123

here's how this works

start with customer 123

wait a sec! how did it know to start with only that customer? because the WHERE clause operates on the first table in the FROM clause -- the optimizer can figure this out, whereas it could not do so from the previous query

then get all the orders if any for customer 123, then all the order items for each order if any (and naturally, an order will have at least one item)

much faster, yes? because you're dealing only with customer 123's data, not every item that was ever purchased

does this explanation help?