CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 5456

SQL combining Union and Except

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

Upvotes: 4

Views: 1485

Answers (4)

Quassnoi
Quassnoi

Reputation: 425331

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

Upvotes: 4

Robin Day
Robin Day

Reputation: 102478

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

Upvotes: 0

Shannon Severance
Shannon Severance

Reputation: 18410

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)

Upvotes: 4

Josh
Josh

Reputation: 6322

you can group by ID.

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

Upvotes: 0

Related Questions