Replacing UNION in TSQL 2012

• gaheinze

  Old Hand

  Points: 371

  More actions

  April 22, 2014 at 4:15 pm

  #396643

  Gents,

  I have been looking for the most optimal way to replace UNIONs all over the internet and I can't seem to find anything relating to my situation.

  I have a statement with about 3 unions. A couple of the unions select from the same table. I want to replace the union so that I can select those tables only once thereby reducing the reads and maybe even improve the time it takes.

  Are there any new inbuilt functions in 2012 that can help?

  Here is the ddl to make my request make more sense.

  I have 5 tables

  TeamPlayer

  Team

  Player

  Statistics

  League

  --drop table #teamplayer

  create table #teamplayer (teamplayerid int, teamid int, playerid int)

  insert into #teamplayer

  select 1, 1, 1 union all

  select 2, 2, 2 union all

  select 3, 3, 3 union all

  select 4, 4, 4 union all

  select 5, 5, 5 union all

  select null, null, 6 union all

  select null, null, 7

  --drop table #player

  create table #player (playerid int, playername varchar(100))

  insert into #player

  select 1, 'Lebron' union all

  select 2, 'Kobe' union all

  select 3, 'Paul' union all

  select 4, 'Durant' union all

  select 5, 'Carmelo' union all

  select 6, 'Barkley' union all

  select 7, 'Shaq'

  --drop table #team

  create table #team (teamid int, teamname varchar(100))

  insert into #team

  select 1, 'miami' union all

  select 2, 'lakers' union all

  select 3, 'clippers' union all

  select 4, 'okc' union all

  select 5, 'knicks'

  --drop table #league

  create table #league (leagueid int, teamid int, leaguename varchar(100))

  insert into #league

  select 10, 1, 'southwest' union all

  select 20, 2, 'northwest' union all

  select 30, 3, 'southeast' union all

  select 40, 4, 'northeast' union all

  select 50, 5, 'north'

  --drop table #statisticscount

  create table #statisticscount (statsid int, playerid int, ppg int, apg int, rpg int)

  insert into #statisticscount

  select 1, 1, 29, 7, 7 union all

  select 2, 2, 35, 5, 4 union all

  select 3, 3, 18, 11, 3 union all

  select 4, 4, 32, 5, 5 union all

  select 5, 5, 27, 4, 6 union all

  select 6, 6, 24, 13, 3 union all

  select 7, 7, 26, 2, 11

  Here is the union statement

  Select TP.PlayerID, P.PlayerName

  From #teamplayer TP

  JOIN #team T on TP.TeamID = T.TeamID

  JOIN #player P on TP.PlayerID = P.PlayerID

  UNION

  Select NULL as PlayerID, 'FreeAgent' as PlayerName

  From #teamplayer TP

  where TeamPlayerID is NULL

  UNION

  SELECT L.LeagueID as PlayerID, L.LeagueName as PlayerName

  FROM #league l

  For the first 2 unions, I hit the same table (teamplayer) twice. How can I rewrite this to hit the table once and hereby eliminating the UNION ?

  I welcome all approaches. i will test them all and let the community know about the fastest approach. Thanks

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  April 22, 2014 at 4:22 pm

  #1707925

  Firstly, let me say that there are plenty of excellent women that use this site, so you may want to rethink the use of "Gents" as you are excluding half the population...

  Select TP.PlayerID, ISNULL(P.PlayerName,'Free Agent') as PlayerName

  From #teamplayer TP

  -- JOIN #team T on TP.TeamID = T.TeamID

  LEFT OUTER JOIN #player P on TP.PlayerID = P.PlayerID

  I removed the join to #team as you were not SELECTing from it or using it in the JOIN to #player, but if it was there as a "filter" to only allow valid teams, then by all means add it back in by removing the comment marks.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]