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?
3条答案
按热度按时间mrfwxfqh1#
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 sayPer your comment, you can use a inline query to achieve the same without using temporary table like
myss37ts2#
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:
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 the
UNION
since you didn't add theALL
option to it.With this test setup:
The output would be:
eni9jsuy3#
Try the
EXCEPT
keyword in place of theUNION
to get the simplest syntax