Union Clause in SQL

The Union Clause is used to combine two separate select statements and produce the result set as a union of both the select statements.

NOTE:

1. The fields to be used in both the selet statements must be in same order, same number and same data type.
2. The Union clause produces distinct values in the result set, to fetch the duplicate values too UNION ALL must be used instead of just UNION.

Basic Syntax:

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

Resultant set consists of distinct values.

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

Resultant set consists of duplicate values too.

Queries

• To fetch distinct ROLL_NO from Student and Student_Details table.

·         SELECT ROLL_NO FROM Student UNION SELECT ROLL_NO FROM Student_Details;

Output:

ROLL_NO

1

2

3

4

• To fetch ROLL_NO from Student and Student_Details table including duplicate values.

·         SELECT ROLL_NO FROM Student UNION ALL SELECT ROLL_NO FROM Student_Details;

Output:

ROLL_NO

1

2

3

4

3

2

• To fetch ROLL_NO , NAME from Student table WHERE ROLL_NO is greater than 3 and ROLL_NO , Branch from Student_Details table WHERE ROLL_NO is less than 3 , including duplicate values and finally sorting the data by ROLL_NO.

·         SELECT ROLL_NO,NAME FROM Student WHERE ROLL_NO>3

·         UNION ALL

·         SELECT ROLL_NO,Branch FROM Student_Details WHERE ROLL_NO<3 span="">

·         ORDER BY 1;

·          

·         Note:The column names in both the select statements can be different but the

·          data type must be same.And in the result set the name of column used in the first

·          select statement will appear.

Output:

ROLL_NO	NAME
1	Information Technology
2	Computer Science
4	SURESH