SQL Union SQL UNION ALL Use Union UNION ALL efficiency

SQL Union SQL UNION All usage and instance tutorial and SQL UNION ALL efficiency
The UNION operator is used to merge the result sets of two or more SELECT statements

The UNION combines multiple tables (or result sets) and returns them as a single result set;
UNION all contains all the rows in the result, including duplicate rows.
That is, when you combine two tables with a union, you delete duplicate records, and when you combine more than two tables with union all, regardless of whether duplicate records exist in the result set, including duplicate rows.

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
Jane Single Instance
store_information table Store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles $300 jan-08-1999
Boston $700 jan-08-1999
 
Internet sales Form Date Sales
jan-07-1999 $ The
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750
 
and we're going to find all the sales days. To do this, we use the following SQL statement: Select date from Store_information the
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 are in any one of the SQL Statements (or both) with "SELECT DISTINCT Date", we get exactly the same result.

SQL Union All
UNION all the purpose of this instruction is to merge the results of two SQL statements together. The union all and the Union differ in that the Union all lists each qualifying data, regardless of whether the data value is duplicated or not. The syntax for UNION all is as follows: [SQL statement 1]
UNION All
[SQL Statement 2] We use the same example as the previous page to show the difference between Union and union. Also assume that we have the following two tables, store_information form store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles $300 jan-08-1999
Boston $700 jan-08-1999

Internet Sales Form Date Sales
jan-07-1999 $
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750

And we need to find out where the store turnover and network turnover date. To achieve this, 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 attention

Attention should be paid to:
(1) If you use an into statement in a statement that has a union, you can only use it in the final table (or result set), and if you want to use the into statement in a single table (or result set), Ms-sql will prompt for an error;
Error statement: Select Aid,aname,ademo into to ATable Union all select Bid,bname,bdemo to from Btable
(2) Only the end of the UNION statement allows the ORDER BY and COMPUTE clauses to be used to define the sequence of the final result or the calculated rollup value. You cannot use these clauses in a separate query that builds a UNION statement.
See union example

Error statement:
Select Aid,aname,ademo from ATable order by AID
Union All
Select Bid,bname,bdemo from Btable order by BID
Positive solutions can be written like this:
Select * FROM
(select Aid,aname,ademo from ATable Union all Select Bid,bname,bdemo from Btable) a
ORDER BY A.aid
Positive solutions can also be written like this:
Select Aid,aname,ademo from ATable
Union All
Select Bid,bname,bdemo from Btable
ORDER BY AID

The SELECT statement within the UNION must have the same number of columns. The column must also have a similar data type. Also, the columns in each SELECT statement must have the same order, note: By default, the UNION operator picks different values. If duplicate values are allowed, use the union all,union the column names in the result set are always equal to the column names in the first SELECT statement in the Union

When using the UNION operator, be aware that:
(1) The number of columns in all queries must be the same as the order of the columns.
In statements that combine with the UNION operator, the number of display lists must be the same (list contents can include: column names, arithmetic expressions, aggregate functions, and so on);
(2) The data type must be compatible.
The corresponding columns in the result set using UNION combination must have the same data type, or there is an implicit data conversion between the two data types, or a type conversion is provided. For example, there is no possibility of a union operator between columns of datetime data types and columns of binary data types, unless explicit conversions are provided, and 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 corresponding result set columns in each statement combined with the Union operator must appear in the same order, because the union operator compares the columns individually, in the order given by each query.

SQL union Usage and SQL Union All Usage, SQL Union efficiency

In terms of efficiency, union All is much faster than union, so if you can confirm that the merged two result sets do not contain duplicate data, then use UNION ALL