A preliminary introduction to the set Operation _mysql in Mysql

What is a collection operation?

Generally, a join operation is considered a horizontal operation between tables because the operation generates a virtual table that contains columns from two tables. And I've summarized the set operations here, and generally consider these operations as vertical operations. The MySQL database supports two kinds of collection operations: Union distinct and UNION all.

As with join operations, the collection operation also operates on two inputs and generates a virtual table. In the join operation, the input table is generally referred to as left input and right input. The two inputs to the set operation must have the same number of columns, and the MySQL database is automatically implicitly converted if the data type is different. Also, the name of the result column is determined by the left input.
Pre-Preparation

Prepare test table table1 and table2:

CREATE TABLE table1 
      (aid int NOT NULL auto_increment, 
      title varchar (), 
      tag varchar (), 
      primary KEY ( AID)) 
      engine=innodb default Charset=utf8;

CREATE TABLE Table2 
      (bid int NOT null auto_increment, 
      title varchar (), 
      tag varchar (), 
      Primary Key (BID)) 
      engine=innodb default Charset=utf8;

Insert the following test data:

INSERT INTO table1 (aid, title, tag) values (1, ' article1 ', ' MySQL ');
INSERT INTO table1 (aid, title, tag) values (2, ' article2 ', ' PHP ');
INSERT INTO table1 (aid, title, tag) VALUES (3, ' article3 ', ' CPP ');

INSERT into table2 (bid, title, tag) values (1, ' article1 ', ' MySQL ');
INSERT into table2 (bid, title, tag) values (2, ' article2 ', ' CPP ');
INSERT into table2 (bid, title, tag) VALUES (3, ' article3 ', ' C ');

UNION DISTINCT

Union distinct combine two inputs, and apply distinct filter duplicates, you can generally omit the DISTINCT keyword directly, use union.

The Union's syntax is as follows:

SELECT column,... From table1 
UNION [all]
SELECT column,... From table2 ...

In multiple SELECT statements, the corresponding column should have the same field properties, and the field name used in the first SELECT statement is also used for the field name of the result.

Now I run the following SQL statement:

(SELECT * FROM table1) union (SELECT * from table2);

The following results will be obtained:

+-----+----------+-------+
| aid | title  |
+-----+----------+-------+
|  1 | Article1 | MySQL |
|  2 | Article2 | PHP  |
|  3 | Article3 | CPP  |
|  2 | Article2 | CPP  |
|  3 | Article3 | C   |
+-----+----------+-------+

We found that the table table1 and the duplicate data items in the table table2:

|  1 | Article1 | MySQL |

It only happens once, and that's the effect of union.

The MySQL database currently implements the Union distinct in the following ways:

• Create a temporary table, which is a virtual table;
• Add a unique index to the column of this temporary table;
• Inserts the entered data into a temporary table;
• Returns the virtual table.

Because a unique index is added, duplicate data items in the collection can be filtered out. Repetition here means that the selected field of select is exactly the same, and is counted as duplicates.

UNION All

The UNION all means that you don't rule out duplicate data items, like I run the following SQL statement:

(SELECT * FROM table1) UNION ALL (SELECT * from table2);

You will get the following results:

+-----+----------+-------+
| aid | title  |
+-----+----------+-------+
|  1 | Article1 | MySQL |
|  2 | Article2 | PHP  |
|  3 | Article3 | CPP  |
|  1 | Article1 | MySQL |
|  2 | Article2 | CPP  |
|  3 | Article3 | C   |
+-----+----------+-------+

Duplicate data is found and will not be filtered out.

When using union distinct, the speed of insertion is obviously affected by the addition of a unique index to a temporary table. If you confirm that there are no duplicate options in the two sets of the Union operation, the most efficient approach would be to use union all.