SQL union all usage union all efficiency

SQL union all usage and example tutorial, and SQL union all efficiency
The UNION operator is used to merge the result sets of two or more SELECT statements.

UNION combines multiple tables (or result sets) and returns 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, duplicate records, including duplicate rows, are not considered.

SQL UNION ALL syntax
SELECT column_name (s) FROM table_name1
UNION ALL
SELECT column_name (s) FROM table_name2

SQL UNION syntax
SELECT column_name (s) FROM table_name1
UNION
SELECT column_name (s) FROM table_name2
Simple instance
Store_Information table store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
 
Internet Sales table Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 12-1999 $750
 
We need to find out all the days with a turnover. To achieve this goal, we use the following SQL statement: SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales result: Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
It is worth noting that if we use "select distinct Date" in any SQL statement (or both sentences), we will get the same result.

SQL Union All
The purpose of the union all command is to combine the results of two SQL statements. The difference between union all and UNION is that union all lists each qualified data, regardless of whether the data value is repeated. The syntax of union all is as follows: [SQL statement 1]
UNION ALL
[SQL statement 2] we use the same example on the previous page to show the differences between UNION ALL and UNION. Assume that we have two tables: Store_Information table store_name Sales Date.
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
 
Internet Sales table Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 12-1999 $750
 
However, we need to find out the days when there are store turnover and network turnover. To achieve this goal, we use the following SQL statement: SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales result: Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

Note:

SQL query notes

Note:
(1) If an INTO statement is used in a UNION statement, it can only be used in the final table (or result set) using the INTO statement in, 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
(2) The order by and COMPUTE clauses can be used only at the end of the UNION statement to define the sequence of final results or calculate the sum. These clauses cannot be used in separate queries of UNION statements.
View union instances

Error statement:
Select AID, AName, ADemo From ATable order by AID
Union All
Select BID, BName, BDemo From BTable Order By BID
The correct solution can be written as follows:
Select * From
(Select AID, AName, ADemo From ATable Union All Select BID, BName, BDemo From BTable)
Order By a. AID
The correct solution can also be written as follows:
Select AID, AName, ADemo From ATable
Union All
Select BID, BName, BDemo From BTable
Order By AID

The SELECT statement within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the column sequence in each SELECT statement must be the same. Note: by default, the UNION operator selects different values. If repeated values are allowed, use union all. The column names in the UNION result set are always equal to the column names in the first SELECT statement in UNION.

Note the following when using the UNION operator:
(1) 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 column names, arithmetic expressions, Aggregate functions, and so on );
(2) 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.
(3) 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.

SQL union usage and SQL union all usage, SQL union Efficiency

In terms of efficiency, union all is much faster than UNION. Therefore, if we can confirm that the two results of the merge do not contain duplicate data, we use union all.