Query Performance of joining before or after UNION

sql, sql-server, t-sql

Solution

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

Problem

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) ```

Original source