SQL Union All Operator Examples | How to use Union All in SQL?

In my previous article I have given the details of Union statement with real examples . In this article I would like to give the information about SQL Union all operator with real life industry examples. I would like to give the bullet-points of SQL Union all operator and examples as well. There are so many questions in interview about union as well as union all operator and other set operators. We will throw light on those interview questions of union all as well.

What you will find in this article?

1. What are multiple types of Set operators in SQL?
2. SQL Union all Operator with real industry examples.

What are multiple types of Set operators in SQL?

The set operators are used to perform multiple set operations on tables in database. There are following 4 basic set operators used in SQL :

1. Union
2. Union All
3. Intersect
4. Minus

The above set operators are used to perform multiple set operations on tables. You may get so many questions in interview about these set operators.

SQL Union all Operator with real industry examples

In this section we will focus on SQL union all operator with real industry examples in detail. We will have look in to multiple syntax of SQL Union all Operator with real life industry examples in detail.

1. SQL Union all Operator combines the result of two or more tables where the column names and datatypes of the multiple tables needs to be similar.
2. The Union all operator is used to fetch the common results which eliminates the duplicates from table.
3. Just make sure that Every select statement within union must have same number of columns.
4. The columns must have similar datatype and we require to follow order as well.

5.In above diagram if we see there are two sets : One set contains a,b,c and other set contains a,d,e as values. If we require to combine the dataset and used union all. It will not eliminate duplicate records and output will be a,b,c,a,d,e.

6.Union all operator does not eliminates duplicates and shows duplicate row as many times in the result.

7.The Union all operator is faster than union operator as it does not eliminate duplicates from the dataset.

8. Syntax of Union all Operator :

Syntax Of Union all :

Select column1…column n from table1;

union all

Select column1…column n from table2;

9.Real Life Industry Example :

If There are two tables Student and Student_1 and you require to find out the combined records from two tables and add it in third table. In this case we need to use the Union all Operator.

Student Table :

Roll_no	Student_name	Class	Marks
1	Rahul	10th	68
2	Rohan	10th	55
3	Rohit	9th	43

Second table

Table Name:Student_1

Roll_no	Student_name	Class	Marks
1	Rahul	10th	68
4	Mohit	10th	47
3	Rohit	9th	43

Query:

Select Roll_no,Student_name,Class,Marks from Student;

Union all

Select Roll_no,Student_name,Class,Marks from Student_1;

Output :

Roll_No	Student_name	Class	Marks
1	Rahul	10th	68
2	Rohan	10th	55
3	Rohit	9th	43
1	Rahul	10th	68
4	Mohit	10th	47
3	Rohit	9th	43

If you see carefully in the output table ; it will not eliminate the duplicate records. Just make sure that union all is faster than union operator. I hope you like this article. If you like this article or if you have any issues with the same kindly comment in comments section.