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

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