UNION And UNION ALL SQL Operators

Introduction

In this article, we'll explore the UNION and UNION ALL operators. In addition, we'll also see the difference between the two operators. Furthermore, we'll give a performance tip about when to use these operators. Lastly, if you ever have been interviewed, UNION and UNION ALL are usually being questioned in an interview.

Ok, let's get started.

What's UNION?

• Union allows us, developers, to combine all rows in two union-compatible tables or two sets of rows into a single result set.
• In addition, the result set includes all the rows belonging to all the queries in the UNION.
• When using UNION, the order of tables doesn't matter because the resulting rows in the union will be the same. E.g., Table1 Ս Table2 is equivalent to Table2 Ս

Ensuring Union Compatibility For Both Tables

• The number of columns should be the same.
• Tables don't need to have the same column names. The names of the columns in the result set will usually be from one of the tables.
• The sequence should be in the same order.
• Data types must be compatible.

Syntax of UNION

Example

Let's see an example to appreciate what we have described so far about UNION. Furthermore, for us to enjoy this example, food data would be a good example, in my opinion.

Let's see the sample code below.

Output

What's UNION ALL?

• It is similar to the UNION operator, but it includes a duplicate row in the result set.
• Remember that you can use the UNION operator if you don't want duplicate records.

Syntax of UNION ALL

Example

Let's see an example about UNION ALL. Again, we'll continue the sample data as food and add a bit of SQL CASE statement to give remarks and show the duplicate record [Hoping you won't get confused!].

Output

For our first output, using this code,

And, of course, for the last example. Just don't judge my taste buds 😉.

Practical Performance Tip

UNION operator uses a distinct sort. It has an additional overhead of eliminating duplicate rows and sorting results.

Remember, it doesn't use a distinct sort when using UNION ALL. That's why when we know that all the records returned by our query are unique. It is recommended to use UNION ALL. Otherwise, use the UNION operator.

The Response of UNION and UNION ALL When Dealing with Text Data Type

When dealing with UNION and UNION ALL operators. It is essential to be mindful of text data type, and using the UNION operator will give an error response. This is because the text data type isn't comparable when used in any of these operators UNION, INTERSECT, or EXCEPT. However, the text data type will be accepted by UNION ALL operator.

Let's see the examples below.

Let's try to use the UNION operator.

Output

Let's try to use the UNION ALL operator.

Output

Summary

In this article, we have discussed the following:

• What's UNION?
• Ensuring UNION compatibility for both tables
• Syntax of UNION
• What's UNION ALL
• Syntax of UNION ALL
• Practical Performance Tip
• The Response of UNION and UNION ALL When Dealing with Text Data Type

Once again, I hope you have enjoyed reading  this article/tutorial as much as I have enjoyed writing it. This article was originally written and posted here.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers! And Thank you!