CODEWITHSUNDEEP
sqlsql-servert-sqlunion
dtc
dtc

Reputation: 10296

T-SQL Union, but exclude results from one table based on another without using a temp table?

I am looking to UNION two select statements, but have it so that the second select excludes some results if the first select contains certain records without using a temp table

I am trying to achieve something like this:

select customernumber, name into #tempCustomer1 from customers1

select customernumber, name from #tempCustomer1 
UNION
select customernumber, name from customers2 
where customernumber not in (select customernumber from #tempCustomer1)

order by customernumber

Is it possible to do this without a temp table?

Upvotes: 0

Views: 3174

Answers (3)

bkwdesign
bkwdesign

Reputation: 2097

Try the EXCEPT keyword in place of the UNION to get the simplest syntax

 SELECT Direction,[Foo] FROM DEV1.dbo.OUTB
 EXCEPT
 SELECT Direction,[Foo] FROM UAT1.dbo.OUTB

Upvotes: 0

jpw
jpw

Reputation: 44881

It's a bit unclear what your desired end result is, but to answer your question Is it possible to do this without a temp table? Yes it is - you could do this:

select customernumber, name from customers1
UNION
(
select customernumber, name from customers2 
where customernumber not in (select customernumber from customers1)
) 
order by customernumber

This will return a set made up from the rows in customers1 and customers2 with the customer1 ids removed from customers2, I think this is what you want.

If you want to get rid of duplicate rows, this is done by theUNIONsince you didn't add theALLoption to it.

With this test setup:

declare @customers1 table (customernumber int, name char)
declare @customers2 table (customernumber int, name char)

insert @customers1 values (1,'a'),(2,'b')
insert @customers2 values (3,'d'),(4,'e'),(1,'f')

select customernumber, name from @customers1 
UNION
(
select customernumber, name from @customers2 
where customernumber not in (select customernumber from @customers1)
) 
order by customernumber

The output would be:

customernumber  name
1   a
2   b
3   d
4   e

Upvotes: 2

Rahul
Rahul

Reputation: 77876

Your query with UNION should be doing exactly what you want cause Union discards the duplicate rows from the result set. So, you can just say

select customernumber, name from customers1

UNION

select customernumber, name from customers2

Per your comment, you can use a inline query to achieve the same without using temporary table like

SELECT * FROM
(
select customernumber, name from customers1

UNION

select customernumber, name from customers2
) tab
WHERE customernumber NOT IN (select customernumber from customers1)
ORDER BY customernumber

Upvotes: 2

Related Questions