Why use a UNION query

[plmesser]

I don't understand the reason for using a UNION query. Can someone help me on this one?
Pat-City of Reno, NV

 

[boblarson]

Union queries allow you to pull together information that would otherwise not be available given the needs for certain joins, etc. You can basically also pull together information that is unrelated if necessary, but more importantly you can pull together what would, in SQL Server terms, be called a FULL JOIN, which Access does not support. In otherwords, I want all records from this table and all records from that table, not just the ones that have matching data (based on a joined key).

I'm sure that there's a better technical explanation, but that's my attempt

 

[boblarson]

And by the way:

 

[plmesser]

Thanks Bob. Can you give me an example and why you would use UNION? I do understand the FULL JOIN idea but seldom need it. I'm doing an online T-SQL class and UNION concept is clear as 'mud'!
Pat
This forum looks better than any in US I've seen in a while. Couple of old ones but not getting updates.

 

[Banana]

A common case is where we may have a archive table containing old data and a current data that only contains the 'active' data. Every so often, the data is moved from active to archive table to keep the indices and row count in a manageable sizes and keep everything zippy.

But suppose I wanted to report on full history of a certain kind of data (e.g. a product or a client) since the first transaction?

I would then UNION the archive table with the active table and I could then collect all data I need from both table.

Note: To clarify for everybody's benefit- it is not always the best idea to have two identical tables, one for archive and one for active, and for most cases, performance can be had with a simple flag indicating it's active or not. This really applies when we are in realms of multi-million rows consuming gigabytes.

Another possible example is when you want to report on all events that happened in the operations, including signing up a new customer, buying, ordering, and selling products. Normally, customers would be in their own table, their order in another table, and your inventory control would be in their set of table. Assuming that all tables has timestamp to indicate when they were added into the database, you could have a UNION query to get all dates from all tables and thus build a full chronological order of events for all different aspects of your operations.

Did that help?

 

[boblarson]

Thanks Bob. Can you give me an example and why you would use UNION? I do understand the FULL JOIN idea but seldom need it. I'm doing an online T-SQL class and UNION concept is clear as 'mud'!
Pat
This forum looks better than any in US I've seen in a while. Couple of old ones but not getting updates.

A Union query allows you to bring together data you require. For example, in one case that I had with a client, we had a set of queries that calculated totals over groups. We then used a Union query in order to bring them all together to be able to export the results to Excel and have the totals for the appropriate groups.

Basically you can combine anything as long as the number of columns match. And if any records match exactly (same data for the same columns) it will not show that data more than once. But, you can have it show it all more than once if you wanted by using UNION ALL which then brings back all records, even exactly matching, from all tables/queries that are included in the union.

 

[plmesser]

Thanks Banana and Bob again,
Little more clear now. I understand the concept much better now. I like the example of an archive table and current table using UNION to find the differences. An outer join would do the same I think. Thanks again folks. I am going to like this forum.
Pat

 

[Banana]

Wait, wait, to clear something up.

Outer join wouldn't work, especially if the data isn't duplicated in either table.

Put it other way, if at every dump, we flushed the current table all clear and moved all data into the archive table, we now have nothing to relate upon and thus Outer Join would fail.

One way to think of it is it's like getting one table, copying it in the Excel spreadsheet, then getting another table and copying and pasting it on the same worksheet right under the same table, so it's now one table with so many more rows.

So in table1 we have the data:

ID TextValue
1 "foo"
2 "bar"

and in table2

ID Numbervalue
1 1
2 2

If we then wrote a query:

SELECT TextValue FROM Table1
UNION
SELECT NumberValue FROM Table2

this is we get:

UNION query:

TextValue
foo
bar
1
2


Did that help?

 

[gemma-the-husky]

banana you go to too many foo bars

stick with wine bars

very clear explanations tho!

 

[plmesser]

Yes, banana, I do understand but still not quite sure " the why" to use it. And I do understand the Outer Join would not do it now, thanks.
Pat

 

[Rabbie]

Another place where a UNION query can be useful is where you can merge data for a Client from a Payments table with data from the Invoice table to calculate the total balance outstanding.

 

[gemma-the-husky]

Yes, banana, I do understand but still not quite sure " the why" to use it. And I do understand the Outer Join would not do it now, thanks.
Pat

the why?

occasionally you need a set of results based on merging data from two different sources, that cannot otherwise be combined.

 

[wazz]

another famous example:
- a combobox is used to filter a form
- select, say, a city to get a short-list of everyone who lives there

how do you get everyone back?

obviously you have to remove the filter. how do you do that?
you could use a button, or something similar, or add
<All>
to the list of cities. select <All> to get people from all of the cities.

how do you add <All> to the list of cities?

<All> is not a city, so it is not in the city table. you have to combine <All> with the city names and you do it with a UNION query.

the why?

occasionally you need a set of results based on merging data from two different sources, that cannot otherwise be combined.

 

[plmesser]

Thanks to all this has been super helpful.
PatM-Reno

 

[Simon_MT]

When I started with a database the Original and copies with Editioned works of art where in the same file. The only time they need to be joined is an Exhibition. So the Union query first collects the Originals and then the Editions and orders them according to the Exhibition numbering. One report is produced for the entire Exhibition exactly as the works of art are seen on the wall.

This I might add is infrequent as most Exhibition solely contain Originals or solely contain Editions.

This is where the Union Query comes into play as the information for both is essentially the same and I wanted to create an Exhibition List with all the works of art.

Simon

 

[neileg]

My two penn'orth.

I find that in about 80% of cases, the use of a union query indicates that the data is not normalised since it implies that you have two or more datasets with similar structures. Of course, the structure may be dictated by external factors which you cannot change and not simply poor design on your part.

 

[Simon_MT]

I would argue that when a business has too very different functions it is more important to delineate these functions and they require specialisation. Splitting the data to reflect this requirement and them bring them back together on the odd occasion makes UNION joins invaluable. Yes, you could denote each function by flagging the data but each function has to sift through each others data and it more efficient to have distinct files.

Simon

Simon

 

[dallr]

I just wanted to add 3 points

1. I don't want you to leave with the impression that a union query is used to bring two distinct data sources (tables) together since most of the examples I saw was of this nature.
You can also use a union to return results from the same table by "unioning" it to itself. And example of this is if you want to return a result of the top 5 sales persons a well as the worst 5 sales persons.

SELECT TOP 5 AmtSold, FName FROM SALES ORDER BY AmtSold DESC
UNION 
SELECT TOP AmtSold, FName FROM SALES ORDER BY AmtSold ASC

As the example shows it is the exact same table

2. There is a misconception that UNION queries must have the same amount of fields/columns. This is not ideally true. You can union two tables together although the physically fields do not match. To handle this scenario all you have to do is include NULL for the table columns you don't have a physically column for. e.g

SELECT FName, Sex, DOB, Religion FROM Employee 
UNION 
SELECT FName, Sex, NULL, NULL FROM EmployeeArchive

In this example we have an employee archive table where the organization never use to collect data around the date of Birth (DOB) and also the Religion. Then they changed to start to capture this information in their active employee table. As such you have to include NULL as a field so the "So called" fields match when you want to view everything.

3. On a side note. Union queries are not updatable so you would not be able to add data directly to them as a "simple" SELECT query can.


Dallr

 

[gemma-the-husky]

My two penn'orth.

I find that in about 80% of cases, the use of a union query indicates that the data is not normalised since it implies that you have two or more datasets with similar structures. Of course, the structure may be dictated by external factors which you cannot change and not simply poor design on your part.

I think this is a fair point - often you find yourself doing a union of a live file/table and an archive file.table, in a system that doesnt/didnt really need the archive file.

hiowever Rabbie's example about the combo box with <ALL> is a god one, and I think DOES need a union query

 

[wazz]

I just wanted to add 3 points ... Dallr

excellent points.