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

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

i looking union 2 select statements, have sec select excludes results if first select contains records without using temp table

i trying accomplish this:

select customernumber, name #tempcustomer1 customers1 select customernumber, name #tempcustomer1 union select customernumber, name customers2 customernumber not in (select customernumber #tempcustomer1) order customernumber

is possible without temp table?

your query union should doing want cause union discards duplicate rows result set. so, can say

select customernumber, name customers1 union select customernumber, name customers2

per comment, can utilize inline query accomplish same without using temporary table like

select * ( select customernumber, name customers1 union select customernumber, name customers2 ) tab customernumber not in (select customernumber customers1) order customernumber

sql sql-server tsql union