Union queries

Union queries offer a handy way of merging the results of two independent tables or queries. Here's the SQL - in this example, data from a staff table is being combined with data from a supervisors table to show all employees for a given region:

SELECT StaffId as IdNumber, Forename, Surname
FROM tblStaff
WHERE Region='South'
UNION SELECT SupervisorId, Forename, Surname
FROM tblSupervisors
WHERE Region='South'
ORDER BY IdNumber;

There are a couple of important things to remember when using UNION - these are:

• by default, no duplicate records are returned when you use a UNION operation; however, you can get round this by including the ALL predicate straight after the UNION keyword. This also makes the query run faster;
• all queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type;
• use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement (cf. IdNumber in the example above);
• an ORDER BY clause can be used at the end of the last query argument to display the returned data in a specified order;
• a GROUP BY or HAVING clause can be used in each query argument to group the returned data.