Replacing UNION in TSQL 2012

  • 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

  • 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]

  • Here's an approach using the OUTER APPLY operator available since SQL 2008 (so, it's nothing new in 2012 ...).

    Please note that I changed UNION to UNION ALL for the query using #league since I wouldn't expect any duplicates. But if there are dups, please let us know how you'd differentiate where those are coming from (either the #league table or the #teamplayer table).

    SELECT DISTINCT

    CASE WHEN TP.TeamPlayerID IS NULL THEN NULL ELSE TP.PlayerID END as PlayerID,

    CASE WHEN TP.TeamPlayerID IS NULL THEN 'FreeAgent' ELSE x.PlayerName END as PlayerName

    From #teamplayer TP

    OUTER APPLY

    (

    SELECT P.PlayerName

    FROM #team T

    JOIN #player P on TP.PlayerID = P.PlayerID AND TP.TeamID = T.TeamID

    )x

    UNION ALL

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

    FROM #league l



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • My apologies for the 'gents' salutation. What was I thinking.

    Thanks for the response. I just created something to mimic what I have currently. But yes I do need all tables and the league table can have duplicates hence the union instead of Union all. I also noticed you still had the union even after the outer apply replacement.

    In the real scenario, all the unions share at least 3 tables in common. Is there any way to avoid the unions completely?

  • Did you try the solution I provided?

    If the league table can have duplicates, keep the UNION ALL and change the league query to

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

    FROM #league l

    GROUP BY L.LeagueID,L.LeagueName

    Therewith the duplicates are eliminated before union with the other result set.

    There is nothing wrong with a UNION ALL statement. A plain UNION will force a DISTINCT sort against the full result set leading to possible performance issues. Therefore, remove duplicates as early as possible in the process.

    If you simply "hate" the UNION operator, just create a staging table, insert the values returned by each single statement and run your query against that staging table. Voila! No UNION operator required!

    In the real scenario, all the unions share at least 3 tables in common.

    If the sample scenario you provided does not represent your current scenario, how would you expect our solutions to help you?

    If the query using #league would use other tables already used in the query, my solution would be different, too. But the sample data are presented as is and so is my solution.

    Keep in mind we can't look over your shoulder. All we have is what you've posted. If the original query is different, the solution most probably will be different, too.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Viewing 5 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply