How to use SQL Union query statement

The Union operator can combine a collection of query results from two or more SELECT statements into a single result set, that is, to execute a federated query. The syntax format for Union is

SELECT <fields>
From <table>
WHERE <condition>
UNION
SELECT <fields>
From <table>
WHERE <condition>

In a federated query, the column headings of the query results are the column headings of the first query statement. Therefore, to define a column heading must be defined in the first query statement. To sort federated query results, you must also use the column name, column heading, or column ordinal in the first query statement.

When using the union operator, you should ensure that there are the same number of expressions in the select list for each federated query statement, and that each query selection expression should have the same data type, or you can automatically convert them to the same data type. In the case of automatic conversion, the system converts a low precision data type to a high precision data type for numeric types.

Instance

MySQL tutorial
mysql>
mysql> CREATE TABLE Employee (
   ->      id            int,
   ->      first_name    VARCHAR,
   ->      last_name     VARCHAR,
   ->     Start_ date    DATE,
   ->     end_date       DATE,
   ->     salary        FLOAT (8,2),
   ->     city           VARCHAR,
   ->     description   VARCHAR (15)
   ->);
Query OK, 0 rows affected (0.02 sec)

Mysql>
Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ', 1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ', 6661.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ', 6544.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (4, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ', 2344.78, ' Vancouver ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> values (5, ' Robert ', ' black ', ' 19840115 ', ' 19980808 ', 2334.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ', 4322.78, ' New York ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ', 7897.78, ' New York ', ' Manager ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, end_date, salary, city, Description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ', 1232.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City | Description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 9 2 David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql>
mysql> SELECT first_name
  & nbsp -> from employee
   -> WHERE (first_name like ' j% ')
   -> UNION
  ;  -> SELECT first_name
   -> from employee
   -> WHERE (firs T_name like ' r% ');
+------------+
| first_name |
+------------+
| jason      |
| james      |
| robert     |
+------------+
3 rows in Set (0.02 sec)

Mysql>
Mysql>
Which is identical as using two where conditions.
Mysql>
mysql> SELECT First_ Name
   -> from employee
   -> WHERE (first_name like ' j% ') | | (First_Name like ' r% '));
+------------+
| first_name |
+------------+
| jason      |
| james      |
| robert     |
| james      |
+------------+
4 rows in Set (0.00 sec)