remove duplicates from sql union

Question

I'm doing some basic sql on a few tables I have, using a union(rightly or wrongly)

but I need remove the duplicates. Any ideas?

select * from calls
left join users a on calls.assigned_to= a.user_id
where a.dept = 4 
union
select * from calls
left join users r on calls.requestor_id= r.user_id
where r.dept = 4

Answer 1

Union will remove duplicates. Union All does not.

Answer 2

Using UNION automatically removes duplicate rows unless you specify UNION ALL: http://msdn.microsoft.com/en-us/library/ms180026(SQL.90).aspx

Answer 3

Others have already answered your direct question, but perhaps you could simplify the query to eliminate the question (or have I missed something, and a query like the following will really produce substantially different results?):

select * 
    from calls c join users u
        on c.assigned_to = u.user_id 
        or c.requestor_id = u.user_id
    where u.dept = 4