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

Table Name 2: D_Students

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:

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: