SQL query efficiency - impact of UNION ALL

Question

I have recently written a stored procedure which has this part

select T.Id from Teams T
inner join 
(
    select SG.Team1Id, SG.Team2Id from SG
    union all
    select SP.Team1Id, SP.Team2Id from SP
) G
on T.Id in (G.Team1Id, G.Team2Id)

I am doubtful about this query efficiency

Will this query fetch all the records from SG and SP in the subquery and then will apply join condition? If yes, then I think this is not efficient

or

Sql server is smart enough to only fetch the rows from both the tables that matches the criteria of join?

Post the query plan so we can understand how the engine will execute this query. — Gordon Linoff, Apr 21 '13 at 18:56
`on T.Id in (G.Team1Id, G.Team2Id)` means you will always get a nested loops join. It is `T.Id = G.Team1Id or T.Id = G.Team2Id`. See [Is having an 'OR' in an INNER JOIN condition a bad idea?](http://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea/5901901#5901901) — Martin Smith, Apr 21 '13 at 19:25

Martin Smith · Answer 1 · 2013-04-21T19:50:37.163

The UNION ALL in the derived table will likely be more efficient than the alternative formulation of

SELECT T.Id
FROM   Teams T
       INNER JOIN SG
         ON T.Id IN ( SG.Team1Id, SG.Team2Id )
UNION ALL
SELECT T.Id
FROM   Teams T
       INNER JOIN SP
         ON T.Id IN ( SP.Team1Id, SP.Team2Id )

Because the version in your question will only need one pass through Teams. If the tables SP and SG have constraints such that Team1Id cannot be the same as Team2Id and the individual columns are indexed then the following could perform better (for some data distributions, especially if Teams is relatively large compared to the other tables involved )

SELECT T.Id
FROM   Teams T
       INNER JOIN (SELECT SG.Team1Id
                   FROM   SG
                   UNION ALL
                   SELECT SG.Team2Id
                   FROM   SG
                   UNION ALL
                   SELECT SP.Team1Id
                   FROM   SP
                   UNION ALL
                   SELECT SP.Team2Id
                   FROM   SP) G(TeamId)
         ON T.Id = G.TeamId

Also, the optimizer can go from 2 to one but not from one to two. This is an unfortunate limitation as of 2012. — usr, Apr 21 '13 at 21:23

SQL query efficiency - impact of UNION ALL

1 Answers1