“Union of ideas begets a surprising confidence” – J. Madison

Today’s post will deal with an SQL command many of us may be familiar with but may forget exists, or haven’t thought about other uses for it.


In simplest terms the UNION command allows you to join separate SQL queries into one result set. The UNION command has one optional option “ALL”

The difference between UNION and UNION ALL is that the UNION command will remove duplicates rows. While the UNION ALL command will keep all rows from each SQL query. You can combine 2 or more SQL queries, and the queries can be from the same table or different tables. You can nest UNION commands. One of the limitations is that each of the SQL queries must have the same number of columns in its results set. You can use a blank or NULL value to pad the queries in your UNION query.

UNION Example



In the example above you can see that I put the UNION command between the two queries. And I pad the second query by putting the word ‘blank’ in the second query. Another thing to note is that the column names will be the values in the first query of the UNION query.
There are dozens of uses for using the UNION command, but one of my more common uses is when I want to avoid rerunning sub-queries and create a temporary table using the WITH command.

The WITH command allows you to store the results of a query temporarily in memory or in TSQL’s temp database depending on the size of the results. By storing the results temporarily and not having to rerun them multiple times during the query processing, it will reduce the query execution time. Additionally if the results are small and can be stored in memory it can significantly speed up the query time.