Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (Brussels) with:

SQL UNION ALL Does Not Check Duplicates

Posted by Ben Nadel
Tags: SQL

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.

Tweet This Deep thoughts by @BenNadel - SQL UNION ALL Does Not Check Duplicates Thanks my man — you rock the party that rocks the body!



Reader Comments

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.