Is there any performance difference between a group by statement and nested select statements in sql?

Question

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following:

tblPurchase 
ID 
SellerID
BuyerID
Date 
Price

tblPerson 
ID 
Name
Mail
Mobile

Now i want to show the list of purchases along with the associated names of the seller and the buyer Since sellerID and BuyerID refers to the same table, i need two nested selects to fetch their names, so i would write:

Select tblPurchase.ID, tblPurchase.Date,
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.SellerID ) as [Seller's Name],
       select (tblPerson.Name from tblPerson where tblPerson.ID = tblPurchase.BuyerID ) as [Buyer's Name]
from tblPurchase 

this is the first method, and i guess it can be done using group by statement as well ( though myself couldn't do it, since both fields refer to one able, i got stuck at the join section!)

Is there any difference between these two statements ? since i think group by itself is actually translated to the same nested selects later and then executed.

Answer 1

You're mixing apples and oranges, my friend. Your query is equivalent to:

Select p.ID, p.Date,
       s.Name AS [Seller's Name],
       b.Name AS [Buyer's Name]
FROM tblPurchase AS p
LEFT JOIN tblPerson AS s ON p.SellerID=s.ID
LEFT JOIN tblPerson AS b ON p.BuyerID=b.ID;

.. with one exception: Your subquery won't work if it returns more than one record (i.e. if ID isn't unique in the tblPerson table).

GROUP BY, on the other hand, is used to aggregate data. That is, to calculate sums, counts, and so on. If you want to know how many orders each buyer placed, you would use GROUP BY along with an aggregate function (SUM(), MAX(), COUNT(), etc), like this:

SELECT BuyerID, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID;

If you want to see the number or purchases per buyer and date, you would add the Date column to the SELECT as well as the GROUP BY:

SELECT BuyerID, Date, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID, Date;