Use Temporary Tables or Nested Select in retrievin

问题:

I've got a query similar to this below where data are retrieve from multiple tables.. The problem is if this table is to retrieve multiple data... the process would definitely would it be better or more efficient to use nested select or temp table to optimize my select statement... and how should I be grouping my joins...

 Select a.Name,
    b.type,
    c.color,
    d.group,
    e.location
    f.quantity
    g.cost
from Table1 a 
INNER JOIN Table2 b ON a.ID=b.ItemCode
INNER JOIN TABLE3 c ON b.ItemCOde = c.groupID
INNER JOIN TABLE4 d ON c.groupID  = d.batchID
LEFT JOIN TABLE5 e ON d.batchID = e.PostalID
LEFT JOIN TABLE6 f ON e.PostalID = f.CountID
LEFT JOIN TABLE7 g ON f.CountID = g.InventoryNo 

回答1:

1. The order of join could be important: start with the most selective table(s) and continue with least selective table(s).

2. Nested queries vs. temp table: it's old dilemma and there is no "magic" solution. In some cases temp table can improve performance. The truth is: every query is different story. Try with both solution and analyze query execution plan.

回答2:

This might work..!!!

Select a.Name,b.type,c.color,d.group,e.locationf.quantity,g.cost
from Table1 a,Table2 b,TABLE3 c,TABLE4 d,TABLE5 e,TABLE6 f,TABLE7 g
where a.ID=b.ItemCode,b.ItemCOde = c.groupID,c.groupID  = d.batchID,
      d.batchID =     e.PostalID,
      e.PostalID = f.CountID,f.CountID = g.InventoryNo;