Skip to main content
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Jeff Coughlin
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Jeff Coughlin

SQL UNION ALL Does Not Check Duplicates

By
Published in

I am in the SQL optimization mood and just recalled a little fact that I learned recently at the New York ColdFusion User's Group (NYCFUG). Shlomy Gantz was giving a presentation on SQL 2005 when he put up a screen that had "UNION ALL" on it. I am a user of UNION and had never see "ALL" appended before.

They explained that the ALL clause prevented the SQL server from checking for duplicates. See, when you union different record sets, it goes through and checks to make sure you are not duplicating records as a result of the union (remember your Ven Diagrams).

If you UNION ALL, then it performs the union but does NOT go back and check for duplicates. If you are not expecting duplicates, this can be a big performance boost. For example, if you were going to create a "What's New" query by combining recent press release and news items, you would not expect duplicates as they are coming from completely different sets of data.

Another small but very cool piece of information.

Short link: https://bennadel.com/71

Reader Comments

Oh my chickens, this post is old!
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel