SQL | Union Clause

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

This article is contributed by Pratik Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

My Personal Notes arrow_drop_up

Save