SQL UNION Operator

sql union

SQL UNION Operator

SQL UNION Operator

     

The UNION operator combine the result-set of two or more SELECT statements. The UNION works with Select statement, when both the table have same number of columns. The columns used in both table must have similar data types and same order.

Understand with Example

The Tutorial helps you to understand SQL UNION Operator .In this Tutorial, we create a two different tables name 'Stu_Class_10' and 'Stu_Class_12' respectively using create table statement. The insert into statement add the records or rows to the tables respectively. The select statement returns you the records from the respectively tables specified.

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 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

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 Syntax used for SQL UNION is used to selects only distinct values by default. The Syntax used for SQL UNION is given below:.

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

Use UNION in SQL Query

In this example, we make use of UNION in SQL Query, The UNION query return you the set of distinct records from both the tables. The UNION operator only works with select statement, when both the table have same field name and data type. The given below Query return you the distinct value from both the tables.

SELECT * FROM Stu_Class_10
UNION
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 12
2 Ajay 12
3 Rakesh 12
4 Bhanu 12
5 Santosh 12