Why You Should Consider Adding Union Queries to Your SQL Capabilities
In database management, there will be times when you will need to combine two or more results into a one set of data. This often occurs when you need to filter out a large data set to display specific records with differing selection criteria. While this result can be achieved with one Select statement with a complex Where clause, it is more efficient to use a use a Union query.
What is a Union query?
A Union query displays the result sets of two or more Select statements one after the other. See this SQL article for more details. The main requirements for a Union query are that the number and order of columns in each Select statement must be the same and the data types must be compatible. This will allow you to break down what would have been a complex, WHERE statement into easily manageable chunks of separate SELECT statements – an important consideration if the query will be used and modified often.
Union queries also allow you to bring the results of Select queries from different data sets together. The data sets that are being combined by the Union query do not need to have a relational field/data – unlike a Join query.
As an example:
SELECT column1, column2 FROM table1 UNION SELECT columnA, columnB FROM table2;
The column header for the result will be based on the first SELECT statement. If the contents of column1 are names, the contents of columnA on table2 should also be names or something similar – even if the column headers are different. Otherwise the result will not make sense even if the data types are compatible.
Combine data from unrelated sets of data
As previously mentioned, Union queries can be used to combine different data sets together. See this article for more details. This is especially useful in businesses like banks and retail companies, where data from many different sources need to be collated into a sensible data set.
For example, you have a data set for Customers and another data set for Employees. While these two tables do not have a field or data column relating them to each other, you can run a Union query to get their combined data. You can, for instance, collate people’s names, addresses, and contact details if you are making a mailing list.
SELECT EmployeeFirstName, EmployeeLastName, HomeAddress, email FROM EmployeeList UNION SELECT FirstName, LastName, MailingAddress, Email FROM Customers;
In the above example, the number and order of column headers are the same even though they have different column headers. The contents of each column should also be compatible both in context and data type.
Another example where a business may find the need the combine data is when it acquires a business ormerges with another one. The data from both businesses will have to be combined as quickly and as efficiently as possible.
Optimise statements with WHERE and OR clauses
If you use the WHERE clause with an OR condition to query a large data set, you’ll find that the query may run slow. OR queries will usually trigger a table/index scan. This is because the selectivity requirement of the whole operation – comparing two or more criteria to select a record – is better executed with a table/index scan. But a table/index scan operation will touch the whole data set and cause the query to run slow.
You can tweak the query by changing each OR condition into several SELECT statements with the UNION (or UNION ALL) clause/operator. Since each SELECT statement is considered one operation, the selectivity requirement for each operation is simpler and thus can be fulfilled with a seek (using the index). Seek operations are faster than scans since they only touch the required records. And while the whole UNION query may be made up of several operations, it is still faster than a table scan.
For example, the query:
SELECT column_name(s) FROM table1 WHERE ColumnName2 = 100 OR ColumnName2 = 110;
Can be rewritten as:
SELECT column_name(s) FROM table1 WHERE ColumnName2 = 100 UNION SELECT column_name(s) FROM table1 WHERE ColumnName2 = 110;
Note that if you expect duplicate rows and require those duplicates to be displayed, use the UNION ALL clause instead of UNION.
A useful tool in business operations
Union queries are useful to combine data into a sensible result – a requirement in business that occurs more often than one would expect. Union queries are also effective in simplifying complex statements that use the Where and Or clauses – also a common occurrence in many database management applications. Once you become proficient with using Union queries, you would have added a versatile tool to your database management capabilities.
Here’s a few resourceful link related to UNION sql queries