Join is a multi-table join with left, right, and horizontal addition.
Union is the result of merging multiple tables into one table, increasing vertically.
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.UnionOne restriction is that the columns generated by two SQL statements must be of the same data type. In addition, when we useUnionIn this command, we only see different data values (similar to select distinct ). 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 |
|