SQL combining Union and Except

Question

I want to union the result of two sub-queries (say SUB1 and SUB2). The sub-queries have multiple columns including an ID column.

If an ID=1 exists in SUB1, I want the union result to include only the row of ID=1 from SUB1 and not include the ID=1 row from SUB2.

eg. if SUB1 had the following columns and rows

ID | Date
1  | 7/1
2  | 7/3

And SUB2 had the following:

ID | Date
1  | 7/4
3  | 7/8

I would like the union result to be

ID | Date
1  | 7/1
2  | 7/3
3  | 7/8

The only way I can think of is to do something like

SELECT * FROM (SUB1)

UNION

SELECT * FROM (SUB2)
WHERE ID NOT IN 
(SELECT ID FROM (SUB1) )

My only concern is that SUB1 and SUB2 are long queries. I would like to avoid pasting SUB1 twice in my query.

Is there a more concise way? Thanks

Answer 1

you can group by ID.

SELECT ID, MAX(Date) FROM (SUB1)
UNION
SELECT ID, MAX(Date) FROM (SUB2)
GROUP BY ID

Answer 2

SELECT  COALESCE(sub1.id, sub2.id), COALESCE(sub1.date, sub2.date)
FROM    sub1
FULL OUTER JOIN
        sub2
ON      sub1.id = sub2.id

Answer 3

If using SQL Server 2005 or greater, a Common Table Expression will help with not typing the code twice:

; with CTE_Sub1 as (select * from (Sub1))
select * from CTE_Sub1
union
select * from (sub2)
where ID not in (select id from CTE_Sub1)

Answer 4

What about a FULL OUTER JOIN and an ISNULL?

SELECT
 ISNULL(SUB1.ID, SUB2.ID),
 ISNULL(SUB1.[Date], SUB2.[Date])
FROM
 SUB1
FULL OUTER JOIN
 SUB2
ON
 SUB1.ID = SUB2.ID