SQL: Union Operator

In our previous SQL posts we learned how to combine tables using the Join clause to produce results based on common columns among these tables. In this post, we will be talking about the UNION operator, which in simple words is used to combine the results of different SELECT statements into one results set. The following image shows it's basic syntax:

The UNION operator comes with some requirements that must be met in order to be able to implement it:

• All SELECT statements or queries must have the same number of columns.
• Said columns must appear in the same order in all queries.
• These columns' data types must be the same or compatible.

Let's consider the following tables to study this operator:

Now, let's use it to combine these tables into one:

The results might look like this:

Notice we mentioned that the results might look like the previous image, and that's because we presented these results sorted by bill, but without an ORDER BY clause, the order of these results could vary.

When we use the UNION clause, all duplicate records are removed. If we want to keep these records, we should use the UNION ALL statement, which includes them in the final results set.

Had we used UNION ALL in our previous example, the columns mortgage and cellphones would have appeared twice in the results set.

Learn about SQL Inner Join, here.
Learn about SQL Outer Joins, here.