«
»

Did you know that in MS SQL and MySQL, a UNION query will automatically filter out duplicate records between the tables that are being unioned?

I ran into this on an email tool I was working on. The client wanted to email users that had recently signed up for either of two different services. So I did a UNION between the two tables to pull all the users into one query that I could loop through. But for some reason, my test user that was in both tables (but with the same name and email address) was only showing up once. I had expected to need to filter out duplicates myself, and I was puzzled as to why both users weren’t showing up. Here is the query:

SELECT fname, email
FROM WeeklyReport
WHERE DateDiff(day,createDate,GetDate()) <= 60
UNION
SELECT fname, email
FROM SeminarUsers
WHERE DateDiff(day,createDate,GetDate()) <= 60
(cfqueryparams were removed for readability)

I discovered that if you add the userids to the select statements, both records would show up. Aha! It must be automatically filtering out the dupes. A quick look into Books Online confirmed this, and led to the discovery of the ALL keyword:

SELECT fname, email
FROM WeeklyReport
WHERE DateDiff(day,createDate,GetDate()) <= 60
UNION ALL
SELECT fname, email
FROM SeminarUsers
WHERE DateDiff(day,createDate,GetDate()) <= 60

That will return all the records.

This entry was posted on 26 March 2007 at 1:19 pm and is filed under SQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

3 Comments

  1. dc says:

    MINUS and INTERSECT are pretty good tools to have in your box too, they are from the same family as UNION and UNION ALL. Worth reading up on. (Oracle has them and I think MSSQL from 2005).

    27 March 2007, 5:38 am

  2. Aaron Greenblatt says:

    Knowing this, how would I see only records that have a duplicate entry. I need to return only one column since I will be using the result set in a subquery so I will be using UNION ALL, and I want to see which records turn up two or more times.

    19 April 2007, 3:32 pm

  3. max4ever says:

    I had problems because i needed the duplicates, thanks!

    18 December 2012, 3:26 am