SQL – Performance Difference Between Group By and Nested Select Statements

group byselect

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.

Best Answer

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;

Related Solutions

Sql-server – How to avoid duplicating information in a series of nested SELECT statements

;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
always use dbo. (or whatever schema prefix is appropriate).
as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.

Oracle error “too many values”, must be solved with nested SELECTs

Using a join?

SELECT 
  minion.boss,
  emp.name, 
  minion.minion
FROM acg_employees emp 
JOIN    
    (SELECT b_id as boss,
                 LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
                 KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
    FROM (SELECT b_id,
                 e_id,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) AS curr,
                 ROW_NUMBER() OVER (PARTITION BY b_id ORDER BY e_id) -1 AS prev
          FROM acg_boss_of)
    GROUP BY b_id
    CONNECT BY prev = PRIOR curr AND b_id = PRIOR b_id
    START WITH curr = 1) minion
ON (minion.boss=emp.e_id)

Best Answer

Related Solutions

Sql-server – How to avoid duplicating information in a series of nested SELECT statements

Oracle error “too many values”, must be solved with nested SELECTs

Related Question