SQL UNION ALL Does Not Check Duplicates

Posted June 5, 2006 at 1:55 PM 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.



Reader Comments

There are no comments posted for this web log entry.

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Kévin
May 21, 2012 at 1:58 AM
Updated: Converting A ColdFusion Query To CSV Using QueryToCSV()
Hi Ben, why do you need to have so many double quotes when adding the field and field name to the row data? ----------------------------------------- <cfset LOCAL.RowData[ LOCAL.ColumnIndex ] = ... read »
AXL
May 21, 2012 at 1:24 AM
URL Rewriting And ColdFusion's WriteToBrowser Image Functionality (CFFileServlet)
@Mounir, Open your lower case URL Rewrite rule and add the following condition. Condition input: {REQUEST_URI} Check if input string: Does Not Match the Pattern Pattern: ^/CFFileServlet/_cf_ca ... read »
John
May 20, 2012 at 4:28 AM
Understanding The Complex And Circular Relationships Between Objects In JavaScript
@Will Vaughn I tried your javascript example but got this error:- foo.print is not a function ... read »
Perl hacker
May 19, 2012 at 5:37 AM
A Graphical Explanation Of Javascript Closures In A jQuery Context
Thanks for this article, but I fear you missed an important point. If variables in the outer context change, these changes affect the inner anonymous functions as well. That means: if you change the ... read »
Alexander
May 18, 2012 at 3:39 PM
Parsing CSV Data With An Input Stream And A Finite State Machine
Can you use file upload button with this? and read live? or does the file have to already be on the server saved? ... read »
horoskop
May 18, 2012 at 1:06 AM
VIRGO (Aug. 23-Sept. 22): Dead On The Money!
A friend of mine and I were arguing about astrology and she told me that he believes in astrology. She hasn't provided me with any evidence that the belief makes any sense to me. She she been telling ... read »
Manuel
May 17, 2012 at 11:32 PM
Using ColdFusion to Handle 404 Errors (Page Not Found) On Development Server
Very easy the configuration. I read a lot pages and I can't find the solution. I open the administrator and change this Administrator/server settings/Error Handlers/Missing Template Handler and p ... read »
Curt Self
May 17, 2012 at 3:13 PM
LOCAL Variables Scope Conflicts With ColdFusion Query of Queries
I never cease to be amazed that almost EVERY random CF issue I come across lands me on your site. Thank you for documenting your findings for the world. ... read »