Normally, querying a normalized database necessitates joining tables together on one or more common fields. Otherwise, you risk generating a cartesian product. That is a result set whose number of rows equals those in the first table multiplied by the number of rows in the second table. So, if the input contains 1000 persons and 1000 phone numbers, the result consists of 1,000,000 pairs! Not good. Having said that, if you wanted to aggregate data from similar tables that are not directly related, you can do that using the UNION operator. In today's blog, we'll learn some of the finer points on using UNION, along with its close cousin, UNION ALL.
UNION versus UNION ALL
Some people think that Union and Union All are interchangeable. They are not. They differ in that Union removes duplicate rows or records, whereas Union All does not; instead, it just selects all the rows from the tables which meet the conditions of your queries' WHERE criteria and combines them into the results.
Combining Results with UNION
Here's a query that combines the results of two SELECT queries using the Sakila Sample Database:
Let's say that you wanted to find the names of all the actors and customers whose first name is the same as the first name of the actor with ID 8, but without returning the actor 8's details. Although there is more than one way to achieve this, one solution is to employ UNION ALL (UNION would work as well, since there are no duplicates between each result set). Here is the query that does the job, along with the results, in the Navicat Premium database development and admin client:
In the above query, the top SELECT fetches customers whose first name matches that of the actor with the actor_id of 8, while the bottom query fetches actors with the same first name as their fellow actor with ID 8.
If you are a Navicat user, you are already aware that it's editor is one of the best. It provides syntax highlighting, reusable code snippets, as well as auto-complete. When you start to type a word, a popup list appears with suggestions for everything from schemas, tables/views, columns, as well as stored procedures and functions. Here is the UNION operator:
A few final words about UNION and UNION ALL. It is crucial that all queries return the same number of columns or you'll get an error similar to the following:
That being said, the column types do not have to match across all SELECT statements.
And finally, keep in mind when employing UNION or UNION ALL that the column names for the result set are determined by the first SELECT.
Conclusion
The UNION and UNION ALL operators are the perfect tool for aggregating data from similar tables that are not directly related.
If you'd like to give Navicat Premium a try, you can test drive it for 14 days completely free of charge for evaluation purposes!
