Subquery to Joins

• parthi-1705

  SSCrazy Eights

  Points: 9277

  More actions

  March 1, 2010 at 2:11 am

  #100110

  Hi

  How to convert Subquery into Joins

  Id wise Min Drawtime and Max Drawtime

  DECLARE @TEMP TABLE (Rid INT IDENTITY,id INT ,Drawtime Varchar(20) )

  INSERT INTO @TEMP

  SELECT 1,'09:10 AM'

  UNION ALL SELECT 1,'09:30 AM'

  UNION ALL SELECT 1,'09:50 AM'

  UNION ALL SELECT 2,'09:05 AM'

  UNION ALL SELECT 2,'09:25 AM'

  UNION ALL SELECT 2,'09:45 AM'

  UNION ALL SELECT 2,'10:05 AM'

  UNION ALL SELECT 3,'05:18 PM'

  UNION ALL SELECT 3,'05:38 PM'

  UNION ALL SELECT 3,'05:58 PM'

  UNION ALL SELECT 3,'06:18 PM'

  UNION ALL SELECT 3,'06:38 PM'

  UNION ALL SELECT 4,'09:15 AM'

  UNION ALL SELECT 4,'09:35 AM'

  UNION ALL SELECT 4,'09:55 AM'

  UNION ALL SELECT 4,'10:15 PM'

  UNION ALL SELECT 4,'10:35 PM'

  UNION ALL SELECT 5,'04:17 PM'

  UNION ALL SELECT 5,'05:17 PM'

  UNION ALL SELECT 5,'09:17 PM'

  UNION ALL SELECT 5,'10:00 PM'

  UNION ALL SELECT 6,'09:00 AM'

  UNION ALL SELECT 6,'09:10 AM'

  UNION ALL SELECT 6,'10:40 PM'

  UNION ALL SELECT 7,'09:50 AM'

  UNION ALL SELECT 7,'10:47 AM'

  UNION ALL SELECT 7,'05:02 PM'

  UNION ALL SELECT 7,'09:52 PM'

  -- Select * from @TEMP

  Select B.Id,(SELECT Drawtime FROM @Temp AS A WHERE RId=MIN(B.Rid)) MinDrawTime,

  (SELECT Drawtime FROM @Temp AS A WHERE RId=MAX(B.Rid)) MaxDrawTime from @TEMP AS B

  GROUP BY Id

  Which is good Sub query or Joins ?

  Thanks

  Pathi

  Thanks
  Parthi

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  March 1, 2010 at 4:16 am

  #1126410

  You can convert it to joins this way:

  SELECT B.Id, MinDrawTime = C.DrawTime, MaxDrawTime = D.DrawTime

  FROM (

  SELECT B.ID, MinRid = MIN(B.Rid), MaxRid = MAX(B.Rid)

  FROM @TEMP AS B

  GROUP BY B.Id

  ) AS B

  LEFT JOIN @TEMP AS C

  ON C.Rid = B.MinRid

  LEFT JOIN @TEMP AS D

  ON D.Rid = B.MaxRid

  Which is better? I don't know. You should see which performs best with your indexes and data volumes. I know nothing about that, look at the execution plans and you'll find out quickly.

  -- Gianluca Sartori

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  March 2, 2010 at 3:39 am

  #1126932

  parthi-1705 (3/1/2010)

  ---

  Hi

  Which is good Sub query or Joins ?

  Thanks

  Pathi

  i did some testing like convert your table variable into temp table and placed clustered index on RID.

  and see execution plan. but i didnt find any differnce in sub query and join (posted as reply)

  i found everything ( seek, scan and sort ) with same resource usage and percentage

  so for you data we cant say which is better:cool:

  i am attaching both execution plan for your reference with modiifed code CREATE table #TEMP (Rid INT IDENTITY,id INT ,Drawtime Varchar(20) )

  create clustered index idx on #temp(rid)

  INSERT INTO #temp

  SELECT 1, '09:10 AM'

  UNION ALL SELECT 1, '09:30 AM'

  UNION ALL SELECT 1, '09:50 AM'

  UNION ALL SELECT 2, '09:05 AM'

  UNION ALL SELECT 2, '09:25 AM'

  UNION ALL SELECT 2, '09:45 AM'

  UNION ALL SELECT 2, '10:05 AM'

  UNION ALL SELECT 3, '05:18 PM'

  UNION ALL SELECT 3, '05:38 PM'

  UNION ALL SELECT 3, '05:58 PM'

  UNION ALL SELECT 3, '06:18 PM'

  UNION ALL SELECT 3, '06:38 PM'

  UNION ALL SELECT 4, '09:15 AM'

  UNION ALL SELECT 4, '09:35 AM'

  UNION ALL SELECT 4, '09:55 AM'

  UNION ALL SELECT 4, '10:15 PM'

  UNION ALL SELECT 4, '10:35 PM'

  UNION ALL SELECT 5, '04:17 PM'

  UNION ALL SELECT 5, '05:17 PM'

  UNION ALL SELECT 5, '09:17 PM'

  UNION ALL SELECT 5, '10:00 PM'

  UNION ALL SELECT 6, '09:00 AM'

  UNION ALL SELECT 6, '09:10 AM'

  UNION ALL SELECT 6, '10:40 PM'

  UNION ALL SELECT 7, '09:50 AM'

  UNION ALL SELECT 7, '10:47 AM'

  UNION ALL SELECT 7, '05:02 PM'

  UNION ALL SELECT 7, '09:52 PM'

  -- Select * from #temp

  Select B.Id,(SELECT Drawtime FROM #temp AS A WHERE RId=MIN(B.Rid)) MinDrawTime,

  (SELECT Drawtime FROM #temp AS A WHERE RId=MAX(B.Rid)) MaxDrawTime from #temp AS B

  GROUP BY Id

  SELECT B.Id, MinDrawTime = C.DrawTime, MaxDrawTime = D.DrawTime

  FROM (

  SELECT B.ID, MinRid = MIN(B.Rid), MaxRid = MAX(B.Rid)

  FROM #temp AS B

  GROUP BY B.Id

  ) AS B

  LEFT JOIN #temp AS C

  ON C.Rid = B.MinRid

  LEFT JOIN #temp AS D

  ON D.Rid = B.MaxRid

  DROP table #TEMP

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 4, 2010 at 6:26 am

  #1128464

  parthi-1705 (3/1/2010)

  ---

  Which is good Sub query or Joins?

  It is possible to write any JOIN as the equivalent sub-query. The reverse is not true, however.

  SQL Server doesn't care too much how you write your query - it returns data that matches your logical request. Whether you choose to write a sub-query or a join is often a matter of style. I prefer to write a JOIN as a JOIN 😉

  Frequently, it makes no difference at all, since exactly* the same plan is used for either, if the queries are logically the same.

  Paul

  * Or trivially different

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Vonid

  Grasshopper

  Points: 14

  More actions

  March 8, 2010 at 12:33 am

  #1129866

  I agree with Paul. The convention I follow is that

  1. If I need the columns in the select list from only 1 of the tables then I use subquery. So if I have table T1 and T2 and all the columns in the select list are from T1 then I will use a subquery as it would also help in avoiding duplicate rows that might be in T2 and join to T1 producing 2 rows.

  2. If the columns are required from both the tables I use JOIN.

  Cheers

  Vonid