The answer depends on the statistics/optimizer etc. The first query logically asks you to take the two result sets and union them(which implicitly removes duplicates reference). The second says take each result set and remove duplicates, then union them.

In the random data case I would expect better performance from the first one. But if your data is appropriately skewed the second could be better. For example if Table1 had millions of results and no duplicates and Table2 had millions of results but distinct brings it down to just a few records, then the later might work better since it couldn’t compare the Table2 entries to the Table1 entries before getting rid of them.

I ran it against some data I had laying around which produced the same query plan for both in SQL Server. Really you need to profile it with your data.

UNION in and of itself will deliver DISTINCT results.

UNION ALL is faster than UNION, but it does not remove duplicates.

Including DISTINCT in a UNION query does not add anything.

Based on the query you have provided I am assuming the DistinctValue column does not initially give you a set of distinct values so one approach you may try is the following:

SELECT DISTINCT DistinctValue 
FROM Table1
UNION
SELECT DistinctValue
FROM Table2

The key here is that you limit the initial dataset so that the union has less data to manipulate and therefore will decrease overall cost. When applying this on a test set of data it shaved off a few seconds but of course, at scale, the time should improve.