remove duplicates from sql union

问题:

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

回答1:

Union will remove duplicates. Union All does not.

回答2:

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

回答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

回答4:

If you are using T-SQL then it appears from previous posts that UNION removes duplicates. But if you are not, you could use distinct. This doesn't quite feel right to me either but it could get you the result you are looking for

SELECT DISTINCT *
FROM
(
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
)a

回答5:

Since you are still getting duplicate using only UNION I would check that:

• That they are exact duplicates. I mean, if you make a

  SELECT DISTINCT * FROM (<your query>) AS subquery

  you do get fewer files?

• That you don't have already the duplicates in the first part of the query (maybe generated by the left join). As I understand it UNION it will not add to the result set rows that are already on it, but it won't remove duplicates already present in the first data set.