SQL UNION and UNION ALL syntax and example

The SQL UNION operator is used to merge the result sets of two or more SELECT statements. Note that the SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.

SQL union and union all syntax and example
/*
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 have the same number of columns. Columns must also have similar data types. In addition, the columns in each select statement must be in the same order.

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. If repeated 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.

SQL union and union all Application Instances

Union only Concatenates the two results and displays them together. It does not join two tables.
The syntax of union is as follows: [SQL statement 1]
Union
[SQL statement 2] Suppose we have the following two tables,
Store_information table 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 sales table date sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 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" in any SQL statement (or both sentences), we will get the same result.
SQL union all
The purpose of the union all command is to combine the results of two SQL statements. The difference between union all and union is that union all lists each qualified data, regardless of whether the data value is repeated. The syntax of union all is as follows: [SQL statement 1]
Union all
[SQL statement 2] we use the same example on the previous page to show the differences between union all and union. Let's assume that we have two tables:
Store_information table 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 sales table date sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 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