How to Code a UNION
- A UNION combines the results of two or more queries into a single result set
- Each result set must have the same number of columns
- The corresponding data types for each column must be compatible. However, the column names may be different from each result set
- A UNION removes duplicate rows by default
- You may interfile the results using an ORDERY BY clause if there is a column with a common name.
Code Example:
1 USE world;
2 SELECT name, population
3 FROM city WHERE CountryCode = 'AUS'
4 UNION
5 SELECT name, population
6 FROM country
7 WHERE continent = 'Oceania'
8 ORDER BY name;
Results:
SELECT name, population
FROM city
WHERE CountryCode = 'AUS'
- The first query returns the name and population from the city table.
- The filter (WHERE CLAUSE) of the query limits the country code to Australia.
UNION
- The ‘UNION’ clause will combine this query with the results of the subsequent query.
SELECT name, population
FROM country
WHERE continent = 'Oceania'
- The second query returns the name and population from the country table.
- The filter (WHERE CLAUSE) of the query limits the continent code to Oceania.
ORDER BY name;
- It is possible to sort (ORDER BY CLAUSE) and interfile the results of both queries because each query shares a column with the same name. Otherwise, the ORDER BY clause would generate an error.