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:

1. The number of Columns and Order of Columns must be the same.
2. 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