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 :
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
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.
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
HOW TO USE THE UNION ALL OPERATOR
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.
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.
CONCLUSIONIn 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.