Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.
UNION Clause | Nested Subqueries
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
2. Nested Subqueries
3. Joining Tables
4. DDL and DML in SQL

bookUNION 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:

  1. 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;
  2. Data types: The data types of corresponding columns in each query must be compatible;
  3. 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
copy

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'
copy

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?
What columns are required when using the `UNION` clause in SQL?

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

Select the correct answer

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

How does the UNION clause treat duplicate rows by default?

Select the correct answer

Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 2. Chapter 5
some-alt