UNION

The UNION keyword is used to combine the results from multiple select queries into a single table. Each SELECT statement used in a UNION query must have the same number of columns and each column must require a similar data type. The column names from the first SELECT statement are used as the columns for the results returned.

For example, the Employee table:

For example, Contractor table:

The column names from the first SELECT statement are used as the columns for the results returned as shown in following example.

SELECT 'Employee' As Type, name, city, country FROM Employee
UNION
SELECT 'Contractor', name, city, country FROM Contractor;

The following example of UNION removes duplicate rows from the result; if we don't want to remove duplicate rows, the ALL keyword can be used:

SELECT country FROM Employee UNION SELECT country FROM Contractor;

This query returns two rows from the preceding table--Germany and Mexico. To fetch all rows from both these tables, we can use the ALL keyword as follows:

SELECT country FROM Employee UNION ALL SELECT country FROM Contractor;