Sqlserver Union and SQL Union All

SQL UNION operator

The UNION operator is used to merge the result sets of two or more SELECT statements.

Note that the SELECT statement inside the UNION must haveSame quantity. Columns must also haveSimilar data types. In additionSequenceMust be the same.

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

Note: by default, the UNION operator selects different values, that isUNION is de-duplicated. IfRepeated values are allowed. Use UNION ALL.

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

In addition, the column name in the UNION result set is always the same as the column name in the first SELECT statement in the UNION.

UNIONThe purpose of the command is to combine the results of two SQL statements. From this perspective,UNIONThis is somewhat similar to JOIN, because both commands can retrieve data from multiple tables. Union only concatenates two results and displays them together. It does not join two tables ............UNIONSyntax:

 [SQL statement 1]
UNION
[SQL statement 2]Suppose we have two tables,

Store_InformationTable 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 SalesTable Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-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"Then we will get the same result.

SQL Union All
UNION ALLThe purpose of this command is to combine the results of two SQL statements.UNION ALLAndUNIONThe difference is thatUNION ALLEach qualified item is listed, regardless of whether the item value is repeated.UNION ALLSyntax:[SQL statement 1]
UNION ALL
[SQL statement 2]We use the same example on the previous page to showUNION ALLAndUNION. Let's assume that we have the following two tables,

Store_InformationTable 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 SalesTable Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-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

Eg:
Copy codeThe Code is as follows:
SELECT id, Name, DocPinYin FROM doctor
UNION
SELECT 0 AS id, 'no doctor name' AS Name, ''AS DocPinYin


Result set is

SELECT id, Name, DocPinYin FROM doctor

Result set is

Therefore, union aggregates two results.