SQL combining Union and Except

sql, sql-server

Solution

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

Problem

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

Original source