Difference between UNION and UNION ALL in SQL.

In Today's article, we shall be talking about the UNION operator, and the UNION_ALL operator, how to use them, the difference between the Union operator and the Union all Operator, and Lastly which one is better to use.

What is the UNION  operator and how does it work? 

Union operator allows us to combine two or more results from multiple SELECT queries into a single unified result set. It has a default feature to remove the duplicate rows from the tables. By using the UNION operator it means you are selecting a particular column of two different tables. So it returns the result.

 

This operator syntax always uses the column's name in the first SELECT statement to be the column names of the output. so, in a nutshell, the union operator does not take duplicates. It removes the duplicate and returns the multiple combined table(s) as a singular table.

How it works: 

The number and order of the columns should be the same in all queries, and the corresponding column position of each select query must have a compatible data type.
The column name selected in the different SELECT queries must be in the same order. And also, The column name of the first SELECT query will be the column name of the output.

Now, we shall move to UNION ALL operator.

What is UNION ALL  operator and how does it work? 

The UNION ALL operator combines two or more results from multiple SELECT queries and returns all records into a single result set. It does not remove the duplicate rows from the output of the SELECT statements. What I am saying is that, in UNION ALL, it takes two or more different tables and returns it as a singular table, but does not remove the duplicates. it returns it all like that. Let us say we have a table of Students, which has id, name, gender, and age as columns. and we have another table called school which has id, level, and department :

UNDERGRADUATE_STUDENTS table

       id          name               gender     department

1.    001       Williams cole    male        Medicine   

2.    002        Alfred  Toni      female     Law

3.    003        Grace Rolli       female     Education Psychology

4.    004        Cyndi Lo          female     Business Administration   

5.    005         John Smith       male       Economics

6.    006         Chukwu Dan    male        Building  

POSTGRADUATE_STUDENTS table

       id          name       department

1.    001        Year 1     Business Administration

2.    002        Year 3     Software Engineering

3.    003        Year 5     Medicine

4.    004       Year 2       Theatre art

5.    005      Year 3       Building

6.    006        Year 4      Education  psychology

 

Fig 1.0 Union of a table  

HOW TO USE THE UNION OPERATOR.

Query Example for UNION operator.

1. SELECT department FROM undergraduate_student
2. UNION
3. SELECT department FROM postgraduate_student ;

 This query shows that there is a Student table and a School table, so we are saying that it should select the name column from the undergraduate student table and merge it with the department column in the School table, then it returns it as a singular table. Remember, if there are any duplicate rows, it removes them automatically. Now, let us see the result

department

Building

Business Administration

Economics

Education Psychology

Law

Medicine

Software Engineering  

Theatre art

HOW TO USE THE UNION ALL OPERATOR

Query Example for UNION ALL operator.

1. SELECT department FROM undergraduate_student
2. UNION ALL
3. SELECT department FROM postgraduate_student;

The above query for UNION ALL does exactly as UNION too. The only difference is that it returns the table with duplicate values if there were in the first place. Let us see the result.

department

Building

Building

Business Administration

Business Administration

Economics

Education Psychology

Educational Psychology

Law

Medicine

Medicine

Software Engineering  

Theatre art

DIFFERENCE BETWEEN THE UNION AND UNION ALL OPERATOR

Now, I want to explain and differentiate the difference between UNION and UNION ALL in SQL. Both are SQL operators used to combine results from multiple tables. These operators allow us to use multiple SELECT queries, retrieve the desired results, and then combine them into a final output. But the difference is that UNION gives you the row that you have specified in your query while UNION gives you all the rows including the duplicates.

CONCLUSION

In this article, you learned how to use the "union" and the "union all" operators, the differences, and When to use them. But, you may be wondering which one is better?  let me just answer by saying, it depends on your use case. However, UNION ALL has proven faster than UNION.