SQL UNION

The following post illustrates how to use the SQL UNION and SQL UNION ALL Operators.

From time to time, I need to bring content from two separate tables together in a query.  Using the SQL UNION or SQL UNION ALL operators, I can combine this information using two SELECT statements, bringing the results together.

SQL UNION

Copy the following SQL code into your query window and EXECUTE:

Results

SQL UNION

Now, let’s break it down further.

The first two result sets simply display the content of our two temporary tables, #billing and #shipping.

The next SQL statement shows how to use the SQL UNION operator:

SQL UNION

Take note of the following:

  • The SQL UNION operator combines the results of the two SELECT statements, brining together unique/distinct records
  • The columns used in a SQL UNION SELECT statements must be in the same order and data type
  • The SQL UNION SELECT statements must have the same number of columns

In the results above, only six results are returned:

  • The CustomerName values “Amy Allen” and “Andrew Castillo” have identical BillingAddress/City/ZipCode & ShippingAddress/City/ZipCode values, so only one record is returned for each since the SQL UNION brings together unique/distinct result sets
  • The CustomerName values “Mike Morales” and “Sandy Smith” each have two records in the results
    • “Mike Morales” (Green box above) has a different Billing and Shipping address, as well as different ZipCode
    • “Sandy Smith” (Yellow box above) also has a different Billing and Shipping address, as well as different ZipCode

 SQL UNION ALL

Copy the following SQL code into your query window and EXECUTE:

Results

SQL UNION ALL

Now, let’s break it down further.

The first two result sets simply display the content of our two temporary tables, #billing and #shipping.

The next SQL statement shows how to use the SQL UNION ALL operator:

SQL UNION ALL

Take note of the following:

  • The SQL UNION ALL operator combines the results of the two SELECT statements, brining together ALL records, including duplicates
  • The columns used in a SQL UNION SELECT statements must be in the same order and data type
  • The SQL UNION ALL SELECT statements must have the same number of columns

In the results above, there are now eight records returned:

  • The CustomerName values “Amy Allen” and “Andrew Castillo” now show up two times, even though they have identical BillingAddress/City/ZipCode & ShippingAddress/City/ZipCode values, since the SQL UNION ALL brings together ALL records, including duplicates
  • The CustomerName values “Mike Morales” and “Sandy Smith” each have two records in the results
    • “Mike Morales” (Green box above) has a different Billing and Shipping address, as well as different ZipCode
    • “Sandy Smith” (Yellow box above) also has a different Billing and Shipping address, as well as different ZipCode

 

Feel free to modify this code to meet your needs.  Enjoy!

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment