The UNION operation and the UNION ALL operation perform almost the same operation. They are both used to combine two result sets in to one result set. The main difference between the two operations is that the UNION operation will return the unique records in the final result set. The UNION ALL operation will return any duplicates in the final result set.

In the examples below, we will assume we have two tables with the following data. Notice that the values Cat and Dog exist in both tables.

Table1 | Table2 |
---|---|

Alligator | Cat |

Beaver | Dog |

Cat | Eagle |

Dog | Frog |

**The UNION Operation**

1 2 3 4 5 6 7 |
SELECT * FROM Table1 UNION SELECT * FROM Table2 |

Result |
---|

Alligator |

Beaver |

Cat |

Dog |

Eagle |

Frog |

Notice that the duplicate Cat and Dog values were removed from the result.

**The UNION ALL Operation**

1 2 3 4 5 6 7 |
SELECT * FROM Table1 UNION ALL SELECT * FROM Table2 |

Result |
---|

Alligator |

Beaver |

Cat |

Dog |

Cat |

Dog |

Eagle |

Frog |

Notice that the duplicate Cat and Dog values are left in the result.

**Speed Considerations**

One final difference between UNION and UNION ALL is the speed difference. Because the UNION operation needs to return a distinct set of values, this will take extra processing time. A general rule is that if you know that the combined data is going to be unique already… just use the plain UNION ALL operation. Only use the UNION operation if there are duplicates that you would like to have remove.

NOTE: The UNION and UNION ALL operations are actually one operation. The ALL keyword is just an attribute. Because they are so different, I find it is easier to think of them both as separate operations.

Reference: http://msdn.microsoft.com/en-us/library/ms180026.aspx