MySQL SELECT Federated Query

Here we share the usage of federated queries.

MySQL uses Union and Union All to implement a federated query of the data.

Suppose you have the following two tables:

 mysql> SELECT * from the staff;  +----+----------+-------+| ID | name |  Slary |+----+----------+-------+| 1 |  guoding |  3200 | | 2 |  Dingtao |  2800 | | 3 |  Haofugui |  3500 | | 4 |  guoming |  4000 | | 5 |  Haotian |  2900 | | 6 |  Fengfei |  3200 | | 7 |  guoting | 2600 |+----+----------+-------+7 rows in Set (0.00 sec) mysql> select * from staff_1;
     +----+----------+-------+| ID | name |  Slary |+----+----------+-------+| 1 |  guoding |  3200 | | 2 |  liding |  2700 | | 3 |  Haofugui |  3500 | | 4 |  Xiaoli |  3600 | | 5 |  Yazhi |  3200 | | 6 |  Yuanfei |  3200 | | 7 |  guoting | 3500 |+----+----------+-------+7 rows in Set (0.00 sec)  
 
 

Mysql> SELECT * from Staff_2;
+----+-------+----------+
| ID | Slary | name |
+----+-------+----------+
| 1 | 3200 | guoding |
| 2 | 2700 | liding |
| 3 | 3500 | Haofugui |
| 4 | 3600 | Xiaoli |
| 5 | 3200 | Yazhi |
| 6 | 3200 | Yuanfei |
| 7 | 3500 | guoting |
+----+-------+----------+
7 Rows in Set (0.00 sec)

Mysql> SELECT * from Staff_3;
+----------+-------+
| name | Slary |
+----------+-------+
| guoding | 3200 |
| liding | 2700 |
| Haofugui | 3500 |
| Xiaoli | 3600 |
+----------+-------+
4 rows in Set (0.00 sec)

1. UNION

SELECT column_name  FROM Statement table1  : UNION  SELECT column_name  FROMtable2

Description: 1) is used to merge the result set of two or more SELECT statements and eliminate any duplicate rows in the table;
2) The SELECT statement inside the UNION must have the same number of columns, and the column must have similar data types;
3) At the same time, the order of the columns in each SELECT statement must be the same;

4) If there is an order by in the clause , limit, and so on, are wrapped in brackets () and are recommended to be placed after all clauses, that is, to sort or filter the results of the final merge.

Example:

Mysql>SELECT * From the Staff Union SELECT * from Staff_1;+----+----------+-------+| ID | name |  Slary |+----+----------+-------+| 1 |  guoding |  3200 | | 2 |  Dingtao |  2800 | | 3 |  Haofugui |  3500 | | 4 |  guoming |  4000 | | 5 |  Haotian |  2900 | | 6 |  Fengfei |  3200 | | 7 |  guoting |  2600 | | 2 |  liding |  2700 | | 4 |  Xiaoli |  3600 | | 5 |  Yazhi |  3200 | | 6 |  Yuanfei |  3200 | | 7 |  guoting | 3500 |+----+----------+-------+rows in Set (0.00 sec)Mysql>select * from the Staff Union SELECT * from Staff_2;  +----+----------------------+----------------------+| ID | name |  Slary |+----+----------------------+----------------------+| 1 | guoding |  3200 | | 2 | Dingtao |  2800 | | 3 | Haofugui |  3500 | | 4 | guoming |  4000 | | 5 | Haotian |  2900 | | 6 | Fengfei |  3200 | | 7 | guoting |  2600 | | 1 | 3200 |  guoding | | 2 | 2700 |  liding | | 3 | 3500 |  Haofugui | | 4 | 3600 |  Xiaoli | | 5 | 3200 |  Yazhi | | 6 | 3200 |  Yuanfei | | 7 | 3500 | Guoting |+----+----------------------+----------------------+rows in Set (0.00 sec)Mysql>SELECT * From the Staff union select name from Staff_1;ERROR 1222 (21000): The used SELECT statements has a different number of columnsMysql>SELECT * From the Staff Union SELECT * from Staff_3;ERROR 1222 (21000): The used SELECT statements has a different number of columns

Mysql> (SELECT * from the staff ORDER by ID ASC) union (SELECT * FROM Staff_1 ORDER by slary desc); +----+----------+-------+| ID | Name     | slary |+----+----------+-------+|  1 | Guoding  |  3200 | |  2 | Dingtao  |  2800 | |  3 | Haofugui |  3500 | |  4 | Guoming  |  4000 | |  5 | Haotian  |  2900 | |  6 | Fengfei  |  3200 | |  7 | Guoting  |  2600 | |  2 | Liding   |  2700 | |  4 | Xiaoli   |  3600 | |  5 | Yazhi    |  3200 | |  6 | Yuanfei  |  3200 | |  7 | Guoting  |  3500 |+----+----------+-------+12 rows in Set (0.00 sec)

2. UNION All

The function and rules are the same as the Union, except that the Union all does not eliminate duplicate rows

Example:

Mysql>SELECT * From the Staff Union SELECT * from Staff_1;+----+----------+-------+| ID | name |  Slary |+----+----------+-------+| 1 |  guoding |  3200 | | 2 |  Dingtao |  2800 | | 3 |  Haofugui |  3500 | | 4 |  guoming |  4000 | | 5 |  Haotian |  2900 | | 6 |  Fengfei |  3200 | | 7 |  guoting |  2600 | | 2 |  liding |  2700 | | 4 |  Xiaoli |  3600 | | 5 |  Yazhi |  3200 | | 6 |  Yuanfei |  3200 | | 7 |  guoting | 3500 |+----+----------+-------+rows in Set (0.00 sec)Mysql>SELECT * from the Staff union ALL SELECT * from Staff_1;+----+----------+-------+| ID | name |  Slary |+----+----------+-------+| 1 |  guoding |  3200 | | 2 |  Dingtao |  2800 | | 3 |  Haofugui |  3500 | | 4 |  guoming |  4000 | | 5 |  Haotian |  2900 | | 6 |  Fengfei |  3200 | | 7 |  guoting |  2600 | | 1 |  guoding |  3200 | | 2 |  liding |  2700 | | 3 |  Haofugui |  3500 | | 4 |  Xiaoli |  3600 | | 5 |  Yazhi |  3200 | | 6 |  Yuanfei |  3200 | | 7 |  guoting | 3500 |+----+----------+-------+rows in Set (0.00 sec)

MySQL SELECT Federated Query