Union All
Syntax:
SELECT column_one, column_two,column_three,.. column_N INTO Table_name FROM table_name UNION SELECT column_one, column_two, column_three,..column_N FROM table_name;
The difference between Union and Union All is UNION doesn’t include duplicates, but UNION ALL includes duplicates too. Both are used with similar syntax.
Consider the following tables of two departmental stores data
Database: Shop1
Item_Id | Name | Count |
---|---|---|
1 | USB drive | 10 |
2 | pencil | 11 |
3 | candle | 01 |
4 | sharpie | 19 |
5 | model car | 12 |
6 | water bottle | 20 |
Command used to create the table
Query:
CREATE TABLE Shop1(Item_Id int,Name varchar(20),Count int)
Output:
Database: Shop2
Item_Id | Name | Count |
---|---|---|
1 | nail file | 11 |
2 | rubber band | 10 |
3 | candle | 01 |
4 | pencil | 10 |
5 | model car | 12 |
6 | water bottle | 12 |
7 | bread | 3 |
8 | shoes | 19 |
9 | face wash | 20 |
Command used to create the table
Query:
CREATE TABLE Shop2(Item_Id int,Name varchar(20),Count int)
Output:
Method 1: Using UNION Keyword
In order to join the two tables i.e. Shop1 and Shop2 we run the following command:
Query:
SELECT * INTO joined FROM Shop1 UNION SELECT * FROM Shop2;
The above command joins Shop1 and Shop2 into a new table joined which is as follows and can be viewed by the following command:
Query:
SELECT * FROM joined;
Item_Id | Name | Count |
---|---|---|
1 | USB drive | 10 |
1 | nail file | 11 |
2 | pencil | 11 |
2 | rubber band | 10 |
3 | candle | 1 |
4 | pencil | 10 |
4 | sharpie | 19 |
5 | model car | 12 |
6 | water bottle | 12 |
6 | water bottle | 20 |
7 | bread | 3 |
8 | shoes | 19 |
9 | face wash | 20 |
So the joined table doesn’t include duplicates as we have used UNION Keyword
Here is the output when we execute the query
Output:
Method 2: Using UNION ALL Keyword
Query:
SELECT * INTO joined2 FROM Shop1 UNION ALL SELECT * FROM Shop2;
The above command creates a new table names joined2 which includes all the values of Shop1 and Shop2.
We run the following command for viewing the table
Query:
SELECT * FROM joined2;
Item_Id | Name | Count |
---|---|---|
1 | USB drive | 10 |
1 | nail file | 11 |
2 | pencil | 11 |
2 | rubber band | 10 |
3 | candle | 1 |
3 | candle | 1 |
4 | sharpie | 19 |
4 | pencil | 10 |
5 | model car | 12 |
5 | model car | 12 |
6 | water bottle | 20 |
6 | water bottle | 12 |
7 | bread | 3 |
8 | shoes | 19 |
9 | face wash | 20 |
Here is the output when we execute the query.
Output:
How to Append Two Tables and Put the Result in a Table in SQL?
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. In this article, we will learn how to append two tables and store the result into a new table using UNION, UNION ALL.