Alternative of UNION in sql server

Question

I have 2 tables which contains 5 unique cities each. I want all 10 cities but i don't want to use UNION. Is there any alternative for UNION.

SELECT DISTINCT CITY FROM TABLE1
UNION
SELECT DISTINCT CITY FROM TABLE2

Answer 1

You can apply Full Outer join instead of Union

SELECT DISTINCT ISNULL(t.City,t1.City)
FROM dbo.TABLE1 t
FULL OUTER JOIN dbo.TABLE2 t1 ON t.City = t.City;

This query provides you the same result as union

Answer 2

Here is an alternate way

SELECT DISTINCT CASE WHEN a.city is null then b.city else a.city end 
                         FROM Table1 FULL JOIN Table2b ON 1 = 0

it offers no advantage over UNION - but you might be interested in seeing FULL JOIN, which has its similarities to UNION