Query Performance of joining before or after UNION

Question

Let's say we have a query that is essentially using a union to combine 2 recordsets into 1. Now, I need to duplicate the records by way of typically using a join. I feel option 1 is in my opinion the best bet for performance reasons but was wondering what the SQL Query experts thought.

Basically, I "know" the answer is "1". But, I am also wondering, could I be wrong - is there a side of this I might be missing?

(SQL Server) Here are my options.

pseudo-code

Original Query:

Select Name, Category from t1
Union
Select Name, Category from t2

Option 1)

Select Name, Category from t1
Inner Join (here)
Union
Select Name, Category from t2
Same inner Join (here)

Option 2)

Select * from (
Select Name, Category from t1
Union
Select Name, Category from t2
) t
(Inner Join Here)

Answer 1

SELECT  Name, Category
FROM    t1
JOIN    t_right
ON      right_category = category
UNION
SELECT  Name, Category
FROM    t2
JOIN    t_right
ON      right_category = category

SELECT  *
FROM    (
        SELECT  Name, Category
        FROM    t1
        UNION
        SELECT  Name, Category
        FROM    t2
        ) t
JOIN    t_right
ON      right_category = category

These queries are not identical: the second one can return duplicates if more than two records in the right table can satisfy the join condition, like this:

t1

Name   Category
---    ---
Apple  1


t2

Name   Category
---    ---
Apple  1

t_right

Category
---
1
1

The first query will return Apple, 1 once, the second query will return it twice.

Performance-wise, it's hard to tell which query will be more efficient until we see your data:

• The first option can gain efficiency by applying different algorithms to each query.

• The second option can gain efficiency by reading the right table only once.

As a very rough rule of thumb, the first option will be more efficient if the join condition is selective on t1 and t2, while the second option will be more efficient if it is not.

However, in simple cases (a join on a sargable condition with few values of high cardinality) SQL Server's optimizer will push the concatenation out of the subquery so that it will be identical to the following query:

SELECT  Name, Category
FROM    t_right
CROSS APPLY
        (
        SELECT  Name, Category
        FROM    t1
        WHERE   t1.Category = t_right.category
        UNION
        SELECT  Name, Category
        FROM    t2
        WHERE   t2.Category = t_right.category
        ) t

Answer 2

There are several different factors that could affect performance in this case. For instance, maybe putting it into a temporary table first (from doing the union in a subquery) would be worth the tradeoff from having to do two index scans as a result of doing the join twice.

We could yap about it all day, but... Simple answer: test each one and see which has the most efficient query plan and/or best execution time. That's the only way to really tell.

Answer 3

As a baseline, I'd go with option 2, because--if all else is equal (there are always special cases and exceptions)--it should be quicker.

In option 1, you read t1, then join to via a read to "here", then you read t2, then join to another read to "here", then union them together.

In option 2, you read t2, then read t2, join them together, and then join the merged set (distinct or not, depending on the use of UNION ALL) to "here".

In other words, in option 1 you read table "here" twice, and in option 2 you read it once. It could be one row in a table in memory, but it's still a read.