SQL Union and UNION ALL operators
The union operator is used to combine the result set of two or more SELECT statements
Note that the SELECT statement inside the UNIOB 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
The syntax for SQL UNION is as follows
SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2
Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNIONall.
SQL UNION All syntax
SELECT column_name (s) from Table_name1union Allselect 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 following is an example of the original table used
Employees_china:
e_id |
E_name |
01 |
Zhang, Hua |
02 |
Wang, Wei |
03 |
Carter, Thomas. |
04 |
Yang, Ming |
Employees_usa:
e_id |
E_name |
01 |
Adams, John. |
02 |
Bush, George. |
03 |
Carter, Thomas. |
04 |
Gates, Bill. |
Using UNION
List all the different employee names in China and the United States
SELECT E_name from Employees_chinaunionselect e_name from Employees_usa
Results
E_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas. |
Yang, Ming |
Adams, John. |
Bush, George. |
Gates, Bill. |
Note: This command cannot list all employees in China and the United States. In the above example, we have two employees of the same name, and only one of them is listed. The UNION command will only pick a different value.
UNION All
The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values ( including duplicates )
SQL Statement 1UNION allsql Statement 2
Example: List of all employees in China and the United States
SELECT e_name from Employees_chinaunion allselect e_name from Employees_usa
Results
E_name |
Zhang, Hua |
Wang, Wei |
Carter, Thomas. |
Yang, Ming |
Adams, John. |
Bush, George. |
Carter, Thomas. |
Gates, Bill. |
SQL Advanced Application (Union, UNION ALL)