Multiple column Union Query without duplicates

Question

I'm trying to write a Union Query with multiple columns from two different talbes (duh), but for some reason the second column of the second Select statement isn't showing up in the output. I don't know if that painted the picture properly but here is my code:

Select empno, job
From EMP
Where job = 'MANAGER'
Union
  Select empno, empstate
  From EMPADDRESS
  Where empstate = 'NY'
  Order By empno

The output looks like:

EMPNO   JOB
4600    NY
5300    MANAGER
5300    NY
7566    MANAGER
7698    MANAGER
7782    MANAGER
7782    NY
7934    NY
9873    NY

Instead of 5300 and 7782 appearing twice, I thought empstate would appear next to job in the output. For all other empno's I thought the values in the fields would be (null). Am I not understanding Unions correctly, or is this how they are supposed to work?

Thanks for any help in advance.

Answer 1

If you want the data in a separate column you will want a JOIN not a UNION:

Select e.empno, e.job, a.empstate
From EMP e
left join EMPADDRESS a
    on e.empno = a.empno
Where job = 'MANAGER'
    AND empstate = 'NY'
Order By e.empno

A UNION combines the two results into a single set but the data is listed in the same columns. So basically they are placed on top of one another:

select col1, col2, 'table1' as src
from table1
union all
select col1, col2, 'table2' as src
from table2

Will result in:

col1 | col2 | src
t1   | t1   | table1
t2   | t2   | table2

If you want to have the data in a separate column which is sounds like you do then you will use a join of the tables.