The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.
But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error
“The text data type cannot be selected as DISTINCT because it is not comparable.”
As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.