Combination of multiple MySQL table result sets

I believe everyone knows about MySQL table result sets. The following describes several methods for combining multiple MySQL table result sets. I hope you can have a better understanding of MySQL table result sets.

Select * from table2
Union all
Select * from table2

Select * from table2 union select * from table2

If the MS-SQL combines the results of two or more queries into a single result set that contains all rows of all queries in the Union query, the common method is as follows:

1. Use the union or union All statement
1. Difference between union and union all statements
Combine multiple tables or result sets) and return them as a single result set;
Ø union all contains ALL rows in the result, including duplicate rows.
That is to say, when UNION is used to combine two tables, duplicate records are deleted. When union all is used to combine two or more tables, the existence of duplicate records in the result set is not considered.
2. Note the following when using the UNION operator:
The columns and columns in all queries must be in the same order.
In a statement that uses the UNION operator combination, the number of all displayed lists must be the same. The list content can include the column name, arithmetic expression, and aggregate function );
Data Types must be compatible.
The corresponding columns in the result set using the UNION combination must have the same data type, or implicit data conversion exists between the two data types, or type conversion is provided. For example, the UNION operator cannot exist between columns of the datetime data type and columns of the binary data type, unless explicit conversion is provided, the UNION operator can exist between columns of the money data type and columns of the int data type, because they can be implicitly converted.
The result set columns in the statements combined with the UNION operator must appear in the same order, because the UNION operator compares the columns one by one according to the given order of each query.
3. When using the UNION statement with other Transact-SQL statements, note the following:
Ø if an INTO statement is used in a UNION statement, it can only be used in the final table or result set). If an INTO statement is used in a single table or result set, the MS-SQL will prompt an error;
Incorrect statement: Select AID, AName, ADemo Into From ATable Union All Select BID, BName, BDemo Into From Btable
The order by and COMPUTE clauses can be used only at the end of the UNION statement to define the ORDER of the final result or calculate the sum. These clauses cannot be used in separate queries of UNION statements.
Error statement:
Select AID, AName, ADemo From ATable order by AID
Union All
Select BID, BName, BDemo From BTable Order By BID
You can write as follows:
Select * From
(Select AID, AName, ADemo From ATable Union All Select BID, BName, BDemo From BTable)
Order By a. AID
You can also write as follows:
Select AID, AName, ADemo From ATable
Union All
Select BID, BName, BDemo From BTable
Order By AID
Sort by AID here because the first column in the final result table is named AID in the MS-SQL. Some DBMS systems do not name the columns queried By different column names. In this way, Order By column names cannot be used for sorting. However, the Order of columns in the result table can be referenced to sort columns. For example: order By 1, which is equivalent to Order By AID
The group by and HAVING clauses can only be used in separate queries. They cannot be used to affect the final result set.
The UNION operator can be used in an INSERT statement.
The for browse clause cannot be used in statements containing the UNION operator. FOR the usage and description of for browse, refer to the description of SELECT statements)
Note: Some DBMS systems do not allow the use of functions and expressions in the Select clause queried by UNION combinations.

Ii. Use the except T or INTERSECT Operator
Similar to UNION, the except T or INTERSECT operator can combine multiple tables or result sets to generate a new table or result set.
1. Random t operator
Similar to the UNION command, limit t processes the results of two SQL statements. The difference is that before distinct T is compared to the second table or result set, all duplicate information has been cleared from the result set of each table. That is, the result set obtained from the distinct T operation will never contain duplicate record rows ).
For example, we need to find unfinished student information in Table_A (student information table) and Table_ B (student Required Course Completion information table ).
Select * From Table_A
Except
Select * From Table_ B
Note: Many DBMS systems do not support the EXCEPT t operator, so you have to use the Left Outer Join operator. Minus in Oracle are similar to those in Oracle.
2. INTERSECT Operator
Similar to the UNION command, INTERSECT processes the results produced by two SQL statements. The difference is that UNION is the UNION set, while INTERSECT is the intersection. That is to say, the result set generated by INTERSECT is a record row that is common to multiple tables or result sets ).
Select AID, AName, ADemo From ATable
INTERSECT
Select BID, BName, BDemo From Btable

3. Use UNION Corresponding to combine rows from two or more tables incompatible with UNION
For example, there are two tables
Create Table Table_A (ID int, office int, address varchar (20), department char (5), pay money)
Create Table Table_ B (office int, ID int, address varchar (20), department char (5 ))
You can use UNION Corresponding to obtain the data in the columns with the same column name in the two tables:
Select * From table_A UNION Corresponding Select * From Table_ B
Note: not all DBMS support UNION Corresponding operations, including MS-SQL Server

Implementation of MySQL table sharding

MySQL authorization table usage example

Disadvantages of MySQL memory tables

Deletes the MySQL registry information.

Description of permission fields in the Mysql User table