MySQL Federated Query Union and UNION ALL usage Introduction

MySQL Federated Query Union and UNION ALL usage Introduction

The function and syntax of union and UNION all

The UNION is used to combine the result set of two or more SELECT statements and eliminate any duplicate rows in the table.
The SELECT statement inside the UNION must have the same number of columns, and the column must have a similar data type.
Also, the order of the columns in each SELECT statement must be the same.
SQL UNION Syntax:

Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.
When all is used with the Union (that is, union ALL), duplicate rows are not eliminated
SQL UNION All syntax

Note: In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.
Note: 1, the column name in the UNION result set is always equal to the column name in the first SELECT statement
2. The SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same

Ii. use of Union and matters needing attention

Union: The union means to merge two or more query results together.
Requirements: Two queries must have the same number of columns
Recommendation: The type of column can be different, but the recommended query for each column, want the corresponding type to be the same
Data that can come from multiple tables: The column names taken out by multiple SQL statements can be inconsistent, with the column name of the first SQL statement as a standard.
If the rows taken in different statements are identical (in this case, the values for each column are the same), then union merges the same rows and eventually retains only one row. It is also possible to understand that union removes duplicate rows.
If you do not want to remove duplicate rows, you can use union ALL.
If there is an order by,limit in the clause, enclose it in parentheses (). After all clauses are recommended, the result of the final merge is sorted or filtered.

(SELECT * from a ORDER by ID) union (SELECT * from B order ID);

SELECT ' DJ ' as ' type ', djje as JE, djje_paytime as time
From Ecs_order_info
UNION SELECT ' zq ' as ' type ', zqdje as JE, zqdje_paytime as time
From Ecs_order_info
UNION SELECT ' wk ' as ' type ', Wkje as JE, wkje_paytime as time
From Ecs_order_info
ORDER by ' time ' DESC
LIMIT 0, 30

A constant can be set in a statement in a union to differentiate: "First row" as username

$sql = "SELECT * from" (select Order_sn, ddly, user_id, brand_id, consignee, address, order_id,  ' deposit ' as  TYPE, Djje _way as Pay_way,  djje as JE, djje_status as ostatus, djje_paytime as time from Ecs_order_info UNION SELECT Order_sn, D dly, user_id, brand_id, consignee, address, order_id,  ' Medium ' as TYPE, Zqdje_way as Pay_way, zqdje_status as Ostatus, ZQ Dje as JE, zqdje_paytime as time from Ecs_order_info  UNION SELECT order_sn, ddly, user_id, brand_id, consignee, addres S, order_id,  ' tail ' as TYPE, Wkje_way as Pay_way, wkje_status as Ostatus, Wkje as JE, wkje_paytime as time from Ecs_ord Er_info) A ";
$sql. = "WHERE a.ostatus =1 ORDER by A. ' Time ' DESC  LIMIT 100";

MySQL Federated Query Union and UNION ALL usage Introduction