SQL UNION ALL Operator
This page has all the information about UNION ALL operator, which is used to join all the values from the SELECT queries.
Similar records were not considered in the result in the UNION operator, but UNION ALL considered similar records in the output obtained after the UNION ALL query was executed.
The syntax for UNION ALL Operator is:
SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4, Column_Name_5 FROM Table_Name_1 UNION ALL SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4, Column_Name_5 FROM Table_Name_2;
Rules to be followed to operate using UNION ALL Operator are as follows:
- The number of Columns and Order of Columns must be the same.
- The data type must be Compatible.
Let's learn the concept of SQL UNION ALL operator with the help of an example
Consider the already existing table with the following data.
Table Name 1: Diploma_Student
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
Table Name 2: D_Students
Student_Id | Student_Name | First_Semester | Second_Semester | Third_Semester | Fourth_Semester | Fifth_Semester | Sixth_Semester | Total | Department_Id |
202111 | Vishaka Jain | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Aman Sharma | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Manan Vohra | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Pratik Rokade | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Ashwini Shewani | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Aman Gupta | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
Example 1: write a query to perform UNION ALL between the Diploma_Student and D_Students tables.
SELECT * FROM Diploma_Student UNION ALL SELECT * FROM D_Students;
We have used two SELECT statements. The First SELECT statement displays the records from the Diploma_Student table performing UNION ALL operation with the records displayed by the Second SELECT statement from the D_Students table in the above UNION ALL operator example.
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
202111 | Vishaka Jain | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202115 | Aman Sharma | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202117 | Manan Vohra | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202220 | Pratik Rokade | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202221 | Ashwini Shewani | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Aman Gupta | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |

Example 2: write a query to perform UNION ALL between the Diploma_Student and D_Students tables ORDER BY Department Id.
SELECT * FROM Diploma_Student UNION ALL SELECT * FROM D_Students ORDER BY Department_Id;
We have used two SELECT statements. The First SELECT statement displays the records from the Diploma_Student table performing UNION ALL operation with the records displayed by the Second SELECT statement from the D_Students table ORDER BY Department Id in the above UNION ALL operator example.
The output of the above query is as follows:
Student_Id | Student_Name | First_Sem | Second_Sem | Third_Sem | Fourth_Sem | Fifth_Sem | Sixth_Sem | Total | Department_Id |
202115 | Aman Sharma | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202115 | Axar Patel | 85 | 80 | 82 | 86 | 92 | 84 | 85 | 1 |
202223 | Anushka Sen | 70 | 75 | 71 | 74 | 80 | 78 | 75 | 1 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202112 | Vaibhav Lokhande | 85 | 90 | 92 | 80 | 85 | 82 | 86 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202118 | Sakshi Patil | 80 | 78 | 74 | 78 | 80 | 77 | 78 | 2 |
202119 | Sopan Bhore | 70 | 68 | 75 | 75 | 80 | 80 | 75 | 2 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202116 | Meena Mishra | 78 | 75 | 80 | 74 | 85 | 77 | 78 | 3 |
202113 | Yash Dhull | 90 | 88 | 94 | 87 | 85 | 90 | 89 | 3 |
202222 | Venkatesh Iyer | 90 | 89 | 87 | 90 | 92 | 91 | 90 | 3 |
202220 | Pratik Rokade | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202220 | Prajwal Lokhande | 80 | 85 | 85 | 75 | 78 | 80 | 81 | 4 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202114 | Sonali Patole | 95 | 90 | 92 | 88 | 92 | 90 | 91 | 4 |
202117 | Mahesh Kumbhar | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202117 | Manan Vohra | 75 | 80 | 75 | 78 | 80 | 76 | 77 | 5 |
202111 | Vishaka Jain | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202111 | Vaishnavi Patil | 94 | 91 | 88 | 85 | 95 | 92 | 91 | 6 |
202221 | Ashwini Shewani | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202225 | Aman Gupta | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
202221 | Anuja Wanare | 85 | 88 | 86 | 82 | 84 | 85 | 85 | 7 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
202225 | Akshay Agarwal | 85 | 80 | 78 | 88 | 90 | 82 | 84 | 7 |
202224 | Aakash Jain | 80 | 75 | 72 | 74 | 85 | 80 | 78 | 7 |
