How to change a query involving a subquery in select into a Join-CodePudding

The following is the image of the database,

I want to get the names of the Products along with the total quantity in which they are purchased. For the purpose, I have written the following query which is giving correct results,

select (select p.ProductName from Product p where p.id = o.Productid) 'Product Name', sum(o.quantity) 'Total Quantity' from orderitem o group by productid ORDER BY 'Total Quantity' desc

However, I believe that it is traversing through the product table for every different order and that is ineffective in terms of time complexity. Changing this from subquery to join might resolve the issue, but I can't figure how can I change this query into Join.

CodePudding user response：

Here is your solution, try following query using join

Select P.ProductName 'Product Name',
Sum(O.Quantity) 'Total Quantity' 
From PRODUCT1 P
Join ORDERITEM O ON O.ProductId = P.Id
Group By P.ID,P.ProductName

CodePudding user response：

SELECT p.ProductName "Product Name", sum(o.quantity) "Total Quantity"
FROM Product p
JOIN orderitem o
  ON p.id = o.Productid
GROUP BY p.id 
ORDER BY "Total Quantity" desc

CodePudding user response：

You're correct in that using a JOIN is almost always more efficient in SQL. Your query would look something like:

SELECT
p.productName
, SUM(oi.quantity) AS total_quantity
FROM orderitem oi
LEFT JOIN product p
   ON oi.ProductId = p.Id
GROUP BY p.productName

Note I'm using a LEFT JOIN here as it's usually best practice to start from your fact table (orderitem in this case) and left join onto your lookup tables (product in this case). This will give you a row with a NULL product name and the total quantity of all unmatched items, which is always a good thing to at least look at. But, depending on your use case you may wan t a different kind of join.