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.
- The fields to be used in both the select statements must be in same order, same number and same data type.
- 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.
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.
- To fetch distinct ROLL_NO from Student and Student_Details table.
SELECT ROLL_NO FROM Student UNION SELECT ROLL_NO FROM Student_Details;
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;
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.
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 firstname.lastname@example.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.