One: The purpose of the Union directive is to combine the results of two SQL statements.
Let's say we have the following two tables:
Store_information table: Store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles jan-08-1999
Boston $700 jan-08-1999
Internet Sales table: Date Sales
jan-07-1999
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750
If we want to find out all the days that have turnover (sales). To achieve this, we use the following SQL statement:
SELECT Date from Store_information
UNION
SELECT Date from Internet_sales
Results:
Date
jan-05-1999
jan-07-1999
jan-08-1999
jan-10-1999
jan-11-1999
jan-12-1999
The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned.
The purpose of the UNION all directive is to merge the results of the two SQL statements together. The Union all and union differ in that union all lists each qualifying material, regardless of whether the data value is duplicated or not.
If we only use union ALL in the above example, the result will be:
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
Summary: Union combines data with the same results and removes all duplicate data.
Two: Join and Union differences
A join is a record set in which two tables are connected and the same part of the same condition is created.
The Union is the resulting two recordset (the field is the same) and together, becomes a new recordset.
The usage and efficiency of the SQL Union and Union all, and the difference from join