How to use SQL union query statements

The UNION operator can combine the query result sets of two or more SELECT statements into one result set for display, that is, the UNION query is executed. The syntax format of UNION is

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

During a joint query, the column title of the query result is the column title of the first query statement. Therefore, to define a column title, it must be defined in the first query statement. To sort the Union query results, you must also use the column name, column title, or column number in the first query statement.

When using the UNION operator, ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selection expression should have the same data type, or they can be automatically converted to the same data type. During automatic conversion, the system converts low-precision data types to high-precision data types.

 

Instance

Mysql tutorial>
Mysql>
Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> 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', '000000', '000000', 19960725, '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', '000000', '000000', 19760321, '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', '000000', '000000', 19781212, '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, 'cela', 'Rice ', '000000', '000000', 19821024, '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', '000000', '000000', 19840115, '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', '000000', '000000', 19870730, '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', '123', '123', 19901231, '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', '000000', '000000', 19960917, '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 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 4322.78 | New York | Tester |
| 7 | David | Larry | 7897.78 | New York | Manager |
| 8 | James | Cat | 1232.78 | Vancouver | Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql>
Mysql>
Mysql> SELECT first_name
-> FROM employee
-> WHERE (first_name LIKE 'J % ')
-> UNION
-> SELECT first_name
-> FROM employee
-> WHERE (first_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)