SQL Union Operator Examples |How to use SQL Union operator?

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

What you will find in this article?

1. What are multiple types of Set operators in SQL?
2. SQL Union Operator with real industry examples
3. Some Important Interview Questions related to SQL Union Operator

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 Operator with real industry examples

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

1. SQL Union 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 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 1,2 and other set contains 1,2,3,4 as values. If we require to combine the dataset and used union. It will eliminate duplicate records and output will be 1,2,3,4.

6.Union operator eliminates duplicates and shows duplicate row once in the result.

7.If you consider union operator it is not good option to use for performance tuning of SQL as it removes duplicates.

8. Syntax of Union Operator :

Syntax Of Union:

Select column1…column n from table1;

union

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 duplicate records from two tables and add it in third table. In this case we need to use the Union 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

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
4	Mohit	10th	47

You can use insert statement to insert data from above table to complete this.

Some Important Interview Questions related to SQL Union Operator

In this section I would like to throw light on some important interview questions related to SQL union Operator.

Question 1 : What is difference between Union and Union all?

Answer :

Union	Union ALL
1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records	1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records
2.Syntax:Select col1,col2…from table1;UnionSelect col1,col2…from table2;	2.Syntax:Select col1,col2…from table1;Union allSelect col1,col2…from table2;
3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records	3.Union all is preferable operator in Performance tuning.

Credits : https://www.complexsql.com/sql-set-operatorsunionunion/

Question 2 :How to Show the Max marks and min marks together from student table?

Answer:

Select max (marks) from Student

Union

Select min (marks) from Student;

Question 3 ; What is fast operation in SQL – Union or Joins:

Answer :

The Union operator is slower than inner join as it eliminates duplicates.User needs to use inner join rather than outer join or union like statements to improve the performance of SQL Query.Outer Joins and Union statements will always do the full scanning of either table so it is not recommended to use it.

If you guys want to contribute kindly provide me union interview questions. I will feature you on this website. I hope this article is informative and gave you required information about SQL Union Operator in detail. If you like this article or if you have any issues with the same kindly comment in comments section.