SQL Union and SQL UNION ALL usage

SQL Union and SQL UNION ALL usage

SQL UNION operator

The UNION operator is used to combine the result set of two or more SELECT statements.

Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type . Also, the order of the columns in each SELECT statement must 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 chooses a different value, that is, the Union is going to be heavy. If duplicate 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 equal to the column name in the first SELECT statement in the Union.

The purpose of the UNION directive is to combine the results of two SQL statements. From this point of view, UNION is somewhat similar to join, because these two instructions can be retrieved from multiple tables. Union only shows the two results together, not the two tables ........ The syntax of UNION is as follows:

[SQL statement 1]
UNION
[SQL Statement 2] Let's say 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 $ 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

And 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

One thing to note is that if we use "Select DISTINCT Date" in any SQL statement (or both), we will get exactly the same result.

SQL Union All
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. 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 All and union . Also assume that 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

And we need to find out the days of store turnover and network turnover. To achieve this, we use the following SQL statement: SELECT Date from Store_information
UNION All
SELECT Date from Internet_sales results:

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

SQL Union and SQL UNION ALL usage