SQL Query Efficiency

[RHelman]

Hello,
I have a query where I am essentially Summing the total cost of each order by customer. I have several tables: Customers, Orders, OrderedUpgrades, Upgrades; each with foreign keys linking them.

I have two SQL queries that work, my question is, which is more efficient and is it preferable to use one format over another? Essentially, one uses the INNER JOIN command while the other does not.

Query 1:

SELECT Customers.ID, Customers.FirstName, Customers.LastName, SUM(OrderedUpgrades.Quantity*Upgrades.Cost) As Total
FROM (((Customers INNER JOIN Orders ON Customers.ID = Orders.Customer) INNER JOIN OrderedUpgrades ON Orders.ID = OrderedUpgrades.Order) INNER JOIN Upgrades ON Upgrades.ID = OrderedUpgrades.Upgrade) GROUP BY Customers.ID, Customers.FirstName, Customers.LastName;

Query 2:

SELECT Customers.ID, Customers.FirstName, Customers.LastName, SUM(OrderedUpgrades.Quantity*Upgrades.Cost) As Total FROM Customers, Orders, OrderedUpgrades, Upgrades WHERE
Customers.ID = Orders.Customer AND Orders.ID = OrderedUpgrades.Order AND OrderedUpgrades.Upgrade = Upgrades.ID GROUP BY Customers.ID, Customers.FirstName, Customers.LastName;

 

[PHV]

IMHO, inner joins are better than filtered cartesian products.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886