SQL combining Union and Except

Question

I want to combine the result of two subqueries (e.g. SUB1 and SUB2). Subqueries have several columns, including an identifier column.

If ID1 = 1 exists in SUB1, I want the join result to include only ID = 1 from SUB1 and not include ID = 1 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 result of the union 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 problem is that SUB1 and SUB2 are long queries. I would like to avoid inserting 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

Answer 2

SQL Server 2005 , :

; 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 3

You can group by ID.

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

Answer 4

What about full-featured insertion and ISNULL?

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