Course Content
Intermediate SQL
Intermediate SQL
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:
(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:
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.
Everything was clear?