UNION Clause

You've successfully mastered inner queries, and we've already attracted the interest of a client who wants us to write SQL queries for them.

But there's one requirement - we'll have to join many tables and work with them. Therefore, before diving into JOINs, let's familiarize ourselves with an important clause: UNION.

Let's briefly go over the key points when working with UNION:

Number and order of columns: All queries being combined with UNION must have the same number of columns, and these columns must be in the same order;
Data types: The data types of corresponding columns in each query must be compatible;
Unique rows: By default, UNION removes duplicate rows. To include duplicates, UNION ALL is used.

For a better understanding of how to correctly combine tables using the UNION clause, I introduced an additional table that contains information about contractors.

There's not much information here, just the first_name, last_name, and email.

Here's what this table looks like:

You can see that this table has similarities with the employees table. Using the UNION clause, we can combine these two tables to, for example, see a list of all names and surnames of employees and contractors involved in the company.

To do this, we'll use the UNION clause:


              12345678
            
(SELECT employee_id as id, first_name, last_name
FROM employees)

UNION 

(SELECT contractor_id as id, first_name, last_name
FROM contractors)
ORDER BY id

Let's briefly break down what's happening here:

We have 2 queries that return 3 columns with identical data types for each column.

That is, we want to see the Id, first_name, and last_name of all company participants. We also aliased the Id so that the columns in both SELECT queries have the same names.

Next, we use the UNION clause to combine the result of these two SELECT queries with duplicate removal (although we don't have any duplicates).

After that, we use the ORDER BY clause to sort by Id.

Note

We're sorting by the Id, which is a shared column for both tables.

For better understanding, you need to realize that after combining with the UNION clause, we get "one big query" that we can then work with using different clauses; in our case, it's the ORDER BY clause.

But we can't use clauses like WHERE or GROUP BY for tables combined like this using the UNION clause.

To do this, we'll need to write this combination in a subquery in the FROM section.

Here's how it will look:


              123456789
            
SELECT id, first_name, last_name
FROM (
  SELECT employee_id AS id, first_name, last_name
  FROM employees
  UNION 
  SELECT contractor_id AS id, first_name, last_name
  FROM contractors
  ) AS combined
WHERE first_name = 'Jane'

Thus, using a query like a subquery in the FROM section opens up a wide range of possibilities for us!

Note:

It may seem daunting at first, but believe me, with this knowledge and the ability to use such structures, it will be much easier for you to write complex queries and perform the tasks assigned to you.

1. What columns are required when using the `UNION` clause in SQL?

2. How does the `UNION` clause treat duplicate rows by default?

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 5

Intermediate SQL

UNION Clause