Skip to content
Related Articles

Related Articles

Improve Article

How to Append Two Tables and Put the Result in a Table in SQL?

  • Last Updated : 14 Sep, 2021
Geek Week

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.

UNION:

Syntax:

SELECT column_one, column_two,..column_N INTO Table_name FROM table_name
UNION  SELECT column_one,column_two,column_three,.. column_N FROM table_name;

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_IdNameCount
1USB drive10
2pencil11
3candle01
4sharpie19
5model car12
6water bottle20

Command used to create the table

Query:

CREATE TABLE Shop1(Item_Id int,Name varchar(20),Count int)

Output:

Database: Shop2

Item_IdNameCount
1nail file11
2rubber band10
3candle01
4pencil10
5model car12
6water bottle12
7bread3
8shoes19
9face wash20

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_IdNameCount
1USB drive10
1nail file11
2pencil11
2rubber band10
3candle1
4pencil10
4sharpie19
5model car12
6water bottle12
6water bottle20
7bread3
8shoes19
9face wash20

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_IdNameCount
1USB drive10
1nail file11
2pencil11
2rubber band10
3candle1
3candle1
4sharpie19
4pencil10
5model car12
5model car12
6water bottle20
6water bottle12
7bread3
8shoes19
9face wash20

Here is the output when we execute the query.

Output:

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :