5

Situation: I need distinct results from two tables, that both have duplicates. I would like to know if I should combine UNION and DISTINCT.

Example 1 - Only use distinct

SELECT DistinctValue
FROM Table1
UNION
SELECT DistinctValue
FROM Table2

Example 2 - Combine distinct and union

SELECT DISTINCT DistinctValue
FROM Table1
UNION
SELECT DISTINCT DistinctValue
FROM Table2

The results are the same, but are there any performance differences?

annemartijn
  • 225
  • 1
  • 2
  • 8
  • 1
    [Sharing your research helps everyone](http://meta.programmers.stackexchange.com/questions/6559/why-is-research-important). Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see [ask] – gnat Oct 01 '14 at 08:43
  • 1
    My hunch is that the only difficulty in UNION lies in appending lines. If anything `DISTINCT` might have a more severe negative performance impact. However, if that is what you must do, that is what you must do. I don't see the issue. – Neil Oct 01 '14 at 10:58
  • 1
    @DocBrown results are the same, because union does a distinct on the results of the two queries. – annemartijn Oct 01 '14 at 11:26
  • Try viewing the query plan with your data. I'll bet the query optimiser will reduce your two expressions to the same operation. – Benjamin Hodgson Nov 11 '14 at 09:13
  • Go 1 and let the query optimizer decide until you have evidence the query optimizer is not properly optimizing. – paparazzo Jun 28 '15 at 16:27

3 Answers3

5

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.

Kennah
  • 268
  • 2
  • 6
3

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.

Sign
  • 2,643
  • 19
  • 22
2

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.

JordanMSchall
  • 21
  • 1