Union and Union All in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning

Overview

Union and Union All commands in SQL are used to combine two or more data sets. The Union command combines two or more data sets by removing all duplicate records. The Union All command combines two or more data sets by keeping duplicate records.

What is Union in SQL?

The Union in SQL combines two or more result sets by removing all the duplicate records. The Union operator first sorts the result set and removes all duplicate records before returning the combined result set. Each SELECT statement in UNION must have the same number of fields in the result sets with similar data types. The result of the SELECT statement can also be restricted with WHERE clause conditions.

Union

In the above diagram, A is present in both the result sets. The final result after the union of the above two sets is {A, B, C, D, F}. There are a total of two A in both the sets. But the final result has only one A as the Union operation removed the duplicate A from the result.

What is Union All in SQL?

The Union All in SQL combines two or more result sets, and it doesn't remove any duplicate records from the combined result set. The result of the union all operation contains all rows from both the result sets. Each SELECT statement in UNION ALL must have the same number of fields in the result sets with similar data types. The result of the SELECT statement can also be restricted with WHERE clause conditions.

Union All

In the above diagram, A is present in both the result sets, and the final result set has two A's as the Union All operator doesn't remove duplicate values.

Syntax of Union in SQL

The UNION operator expects two or more result sets with the same number of fields with similar data types. The syntax of Union in SQL is

The UNION keyword is added between two select statements to combine their results. We can add an optional WHERE condition to filter the result of the SELECT statements.

Syntax of Union All in SQL

The UNION ALL operator expects two or more result sets with the same number of fields with similar data types. The syntax of Union All in SQL is

The UNION ALL keyword is added between two select statements to combine their results. We can add an optional WHERE condition to filter the result of the SELECT statements.

Example of Union in SQL

Let's understand Union in SQL by creating two tables, physics_coaching and maths_coaching, containing the list of students attending physics and maths coaching classes.

First, we create the tables physics_coaching and maths_coaching.

We insert records into the physics_coaching and maths_coaching tables.

The tables after inserting data are: PHYSICS_COACHING

student_idstudent_name
1Marleen Suggate
2Vicki Nelm
3Alexine McGregor
4Sigvard Jeste
5Ardelis Clatworthy

MATHS_COACHING

student_idstudent_name
5Ardelis Clatworthy
6Wilton Dermott
7Dorena Gravells
8Morgen Furze
9Alana Amey

We will use the below Union query to find the list of students attending either physics or maths coaching classes.

Output
The output of the above Union query is

student_idstudent_name
1Marleen Suggate
2Vicki Nelm
3Alexine McGregor
4Sigvard Jeste
5Ardelis Clatworthy
6Wilton Dermott
7Dorena Gravells
8Morgen Furze
9Alana Amey

Explanation

The Union query combines the results of the tables physics_coaching and maths_coaching. Even though the student Ardelis Clatworthy is part of both tables, the result of Union contains only one entry. This is because the Union operator removes the duplicate records.

Example of Union All in SQL

We use the same physics_coaching and maths_coaching tables to understand the Union All operator.

Output
The output of the above query is:

student_idstudent_name
1Marleen Suggate
2Vicki Nelm
3Alexine McGregor
4Sigvard Jeste
5Ardelis Clatworthy
5Ardelis Clatworthy
6Wilton Dermott
7Dorena Gravells
8Morgen Furze
9Alana Amey

Explanation:

We can see that the student Ardelis Clatworthy record is present twice in the result set. This is because the Union All operator doesn't remove duplicate records.

Union or Union All: Which Should You Choose?

The decision to use Union or Union All depends on the use case we want.

  • If our goal is to combine two or more result sets and we want only distinct records, we can go with Union operator.
  • On the other hand, if we don't have a problem with duplicate records, we can go with Union All.
  • If we want to run the query faster, we can go with Union All as it is faster than Union because Union takes some extra time to remove duplicate records.

Difference between Union and Union All in SQL

UnionUnion All
The Union operator is used to combine two or more result sets by removing duplicate recordsThe Union All operator is used to combine two or more result sets by retaining duplicate records
The Union operator returns distinct recordsThe Union All operator returns all records
The syntax of Union is
SELECT columns FROM table1 UNION SELECT columns from table2
The syntax of Union All is
SELECT columns FROM table1 UNION ALL SELECT columns from table2
A query using the Union operator runs slowly because it takes some time to find and remove duplicate recordsA query using Union All operator runs fast because it doesn't remove duplicate records

Conclusion

  • The Union and Union All operators in SQL are used to combine two or more result sets.
  • The result set of the Union query doesn't have duplicate records, whereas the result set of the Union All query contains duplicate records.
  • The Union All operator is faster than the Union operator as the Union operator takes some extra time to find and remove duplicate records.
  • We can choose Union All over Union if we want the query to run fast, and the result set can contain duplicate records.