Getting a distinct value across 2 union sql server tables

Question

I'm trying to get all distinct values across 2 tables using a union.

The idea is to get a count of all unique values in the columnA column without repeats so that I can get a summation of all columns that contain a unique columnA.

This is what I tried (sql server express 2008)

select 
    count(Distinct ColumnA) 
from 
( 
    select Distinct ColumnA as ColumnA from tableX where x = y
    union
    select Distinct ColumnA as ColumnA from tableY where y=z
)

And what isn't working exactly? You have some superfluous DISTINCTs but otherwise this should work. — David M, Dec 11 '09 at 21:45
What are you getting so far that leads you believe what you have is incorrect? — Sonny Boy, Dec 11 '09 at 21:46

score 23 · Accepted Answer · answered Dec 11 '09 at 21:43

23

SELECT COUNT(distinct tmp.ColumnA) FROM ( (SELECT ColumnA FROM TableX WHERE x=y) 
UNION (SELECT ColumnA FROM TableY WHERE y=z) ) as tmp

The extra distincts on TableX and TableY aren't necessary; they'll get stripped in the tmp.ColumnA clause. Declaring a temporary table should eliminate the ambiguity that might've prevented your query from executing.

answered Dec 11 '09 at 21:43

Jim Dagg

2,044
22
29

12

The DISTINCT in the COUNT(DISTINCT isn't necessary because `UNION` removes duplicates – OMG Ponies Dec 11 '09 at 21:45
THis worked for me. I simply had to add as ColumnA at the end of the union'd select statement – rockit Dec 11 '09 at 21:49
@OMG: Good call. Thanks for the assist. – Jim Dagg Dec 11 '09 at 21:49

score 17 · Answer 2 · answered Dec 11 '09 at 21:45

17

SELECT COUNT(*)
FROM
(
SELECT DISTINCT ColumnA From TableX WHERE x = y
UNION
SELECT DISTINCT ColumnA From TableY WHERE y = z
) t

Using a "UNION" will not return duplicates. If you used "UNION ALL" then duplicate ColumnA values from each table WOULD be return.

answered Dec 11 '09 at 21:45

AdaTheDev

142,592
28
206
200

3

DISTINCT isn't necessary because of the UNION, which removes duplicates. `UNION ALL` does **not** remove duplicates – OMG Ponies Dec 11 '09 at 21:46
True - I just thought including the distinct in there might be more performant (execution plan is different) - would require a bit more testing to prove/disprove that. Re: UNION ALL, that is what I said :) – AdaTheDev Dec 11 '09 at 21:52

score 4 · Answer 3 · answered Jul 21 '15 at 07:20

4

To get distinct values in Union query you can try this

Select distinct AUnion.Name,AUnion.Company from (SELECT Name,Company from table1 UNION SELECT Name,Company from table2)AUnion

answered Jul 21 '15 at 07:20

Prateek Gupta

880
1
10
21

score 2 · Answer 4 · edited Nov 07 '17 at 20:37

2

SELECT DISTINCT Id, Name
FROM   TableA
UNION ALL
SELECT DISTINCT Id, Name
FROM   TableB
WHERE  TableB.Id NOT IN (SELECT Id FROM TableA)

edited Nov 07 '17 at 20:37

Observer

3,506
1
16
32

answered Nov 07 '17 at 18:47

Everaldo Carneiro

21
1

Getting a distinct value across 2 union sql server tables

4 Answers4

Linked