SQL UNION ALL Operator

The SQL UNION ALL Operator is used to list all records from two or more select statements.

SQL UNION ALL Operator

SQL UNION ALL Operator

     

The SQL UNION ALL Operator is used to list all records from  two or more select statements. The SQL Union ALL  Operator is used to combine two table using select statement when both the  table have the same number of columns. Each columns in a table must have the same data types.The UNION ALL select all the records from both table must be in the same order.

 

Understand with Example

The Tutorial illustrates an example from SQL UNION  ALL Operator. In this Tutorial, we create a tables name 'Stu_Class_10'  and 'Stu_Class_12' using create statement. The insert into  is used to  add the records or rows in the respective tables. The select statement is used to retrieve the records or rows from the respective tables.

Create Table Stu_Class_10

create table Stu_Class_10(Stu_Id integer(2), Stu_Name varchar(15), Stu_Class  varchar(10))

Create Table Stu_Class_12

create table Stu_Class_12(Stu_Id integer(2), Stu_Name varchar(15), Stu_Class  varchar(10))

Insert data into Stu_Class_10

insert into Stu_Class_10 values(1,'Komal',10)
insert into Stu_Class_10 values(2,'Ajay',10)
insert into Stu_Class_10 values(3,'Rakesh',10)
insert into Stu_Class_10 values(4,'Bhanu',10)
insert into Stu_Class_10 values(5,'Santosh',10)
insert into Stu_Class_10 values(1,'Komal',10)

Insert data into Stu_Class_12

insert into Stu_Class_12 values(1,'Komal',12)
insert into Stu_Class_12 values(1,'Komal',12)
insert into Stu_Class_12 values(2,'Ajay',12)
insert into Stu_Class_12 values(3,'Rakesh',12)
insert into Stu_Class_12 values(4,'Bhanu',12)
insert into Stu_Class_12 values(5,'Santosh',12)

Stu_Class_10

Stu_Id Stu_Name Stu_Class
1 Komal 10
2 Ajay 10
3 Rakesh 10
4 Bhanu 10
5 Santosh 10
1 Komal 10

Stu_Class_12

Stu_Id Stu_Name Stu_Class
1 Komal 12
1 Komal 12
2 Ajay 12
3 Rakesh 12
4 Bhanu 12
5 Santosh 12

SQL UNION Syntax

The SQL UNION Syntax used for  union columns from two tables is given below:

SELECT column_name(s) FROM table_name1
UNION All
SELECT column_name(s) FROM table_name2

Use UNION in SQL Query

In this example, we union columns from two different tables. The UNION ALL combine two table using select statement when both the table have the same name field and its data type. The select return you all duplicate records from both tables. The UNION ALL command select all records from a tables.

.SELECT * FROM Stu_Class_10
UNION ALL
SELECT * FROM Stu_Class_12

Result

Stu_Id Stu_Name Stu_Class
1 Komal 10
2 Ajay 10
3 Rakesh 10
4 Bhanu 10
5 Santosh 10
1 Komal 10
1 Komal 12
1 Komal 12
2 Ajay 12
3 Rakesh 12
4 Bhanu 12
5 Santosh 12