Master Union Queries & Select Statements – [SQL and Access]
Contents
Normally when bringing two sets of data together in a relational database the intention is for data in the two sources to be matched in some way.
For example: Customers to Invoices, Suppliers to Parts, Staff to Absence.
Occasionally, but more often than you may think, there can be a requirement to “combine” rather than “join” sets of data.
For more on joins, read this article all about SQL Joins. To become an expert in SQL, attend one of our SQL Courses.
For example to combine both Customers and Suppliers into one mailing list to announce a significant change to, or a move for, the business.
What Is A Union Query?
A Union Query allows you to pull data from as many tables as you like. Think of it as a Select Statement that is selecting data from as many sources as you want into one big list.
When relating sets of data in the standard way the two sets of data must have a piece of information, a field, in common. In the case of Customers to Invoices it would be the Customer’s Account Number and then this common fact/field is used to “pair” or “relate/join” records from the two tables (Fig1 below).
In a Union Query/Select Statement there does not need to be this relating data/field.
|
In this relationship the number of resulting rows/records will not exceed the total number of records in the original Invoices table. |
A Union Query/Select Statement will essentially place the contents of the two sets of data “on top” of one another listing for example the names and emails of both a Customer set and a Supplier set of data together into one long list.
For more on Access read our article on Using IF Queries as Search Criteria.
In this relationship the number of resulting rows/records will normally be the total number of records from both tables added together.
| In this relationship the number of resulting rows/records will normally be the total number of records from both tables added together. |
|
What are their requirements and restrictions?
In order to create Union Queries/Select Statements the two sources of data being combined must have structural similarities: same number of selected fields in the same order and with similar data types.
In the scenario given here both the Customer and Supplier table will have a Name, Address and Contact fields in common and it is those fields we wish to combine in order to run a mailmerge.
The two sets of data must be combined using the same number of fields in the same order but not necessarily of the same name and with similar data types. If for example the Customer table has more address lines than Suppliers, then a dummy address line will have to be created for the Supplier table.
Alternatively, if one table has a single Contact field and the other First and Lastname, then the fields will have to be combined/concatenated to create a single equivalent to Contact.
An important feature of Union Queries/Select Statements is that duplicated records are automatically excluded from the final list of records.
For example, if a Customer is also a Supplier they will only appear once in the final Union Query/Select Statement. If the duplicates are required for any reason, then the UNION ALL operator can be used instead of the pure UNION.
How to create a Union Query in Access
To create a Union Query in Access is quite a simple task as it can be achieved by creating two comparable standard Select Queries and then combine the SQL from the two queries into one.
Prepare a Customer query to include the necessary fields for the mailmerge and with any Sorting or Filtering you require.
Note that Sorting can only be applied to the fields contained in the first query but as the fields in the second query have to be in the same order the final result of this Union will be all Companies in alphabetical order.
Leaving this query open create a second query for Suppliers.
Notice in the example below, because the tblCustomer has only one Contact field but tblSuppliers has two, a formula has been used to concatenate the First and Last Name fields together to match the single Contact field in tblCustomers. This is because the two sources must contain the same number of fields.
Also note that the field names do not have to match as in the first query for Customers two address lines are Address1 and Address2. The corresponding lines in Suppliers are Add1 and Add2.
Switch this second query into SQL View using the View buttons in the bottom right hand corner of the screen
to display the following which needs to be highlighted in full and copied.
Return to Query1 the Customers and again switch to SQL View. Click at the end of the SQL displayed and press Enter to create a new line.
To learn more about temporary tables read here.
At the start of the new line type the word UNION followed by a space and paste the contents of Query2 the Suppliers to arrive at the following:
SELECT tblCustomers.Company, tblCustomers.Contact, tblCustomers.Address1, tblCustomers.Address2, tblCustomers.Town, tblCustomers.County, tblCustomers.PostCode, tblCustomers.EMail
FROM tblCustomers
ORDER BY tblCustomers.Company;
UNION SELECT tblSuppliers.Company, [Firstname] & ” ” & [LastName] AS Contact, tblSuppliers.Add1, tblSuppliers.Add2, tblSuppliers.Town, tblSuppliers.County, tblSuppliers.PostCode, tblSuppliers.EMail
FROM tblSuppliers;
View the full Query in Datasheet View and 5 records will be displayed as one Customer is also a Supplier in the example given and duplicates are automatically excluded. Should you require the duplicated Customer and Supplier to be shown separately add the word ALL between UNION SELECT: UNION ALL SELECT tblSuppliers.Company, [Firstname] & ” ” & [LastName] AS Contact
The query will also display the five records in alphabetical order by Company/Supplier name.
In Access the resulting query will be displayed with an icon of two overlapping circles to distinguish it from standard Select or Action queries.
See here for details of our Microsoft Access training courses in London, Guildford and across the South of the UK.
How To Create A Union Query In SQL
Union Queries/Select Statements can be created in any product that supports SQL. The requirements (What are their requirements and restrictions) remain the same in all instances and some environments require that the command words be in UPPER CASE exactly as below.
SELECT tblTable1.Field1, tblTable1.Field2, tblTable1.Field3, tblTable1.Field4, tblTable1.Field5
FROM tblTable1
ORDER BY tblTable1.Field1;
UNION SELECT tblTable2.Field1, tblTable2.Field2, tblTable2.Field3, tblTable2.Field4, tblTable2.Field5
FROM tblTable2;
Or to display all records from both tables regardless of any duplication of data:
SELECT tblTable1.Field1, tblTable1.Field2, tblTable1.Field3, tblTable1.Field4, tblTable1.Field5
FROM tblTable1
ORDER BY tblTable1.Field1;
UNION ALL SELECT tblTable2.Field1, tblTable2.Field2, tblTable2.Field3, tblTable2.Field4, tblTable2.Field5
FROM tblTable2;
For more reading on SQL Backup types go here. If you would like to practise your SQL skills while on the go, try the SQL training apps available for iOS and Android. You can learn the theory and complete practical exercises.
Related Articles
Using Union Queries and Select Statements
Backup Strategies For SQL Databases
