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
.
UNION
in SQL is used to combine the results of two or more SELECT
queries into one resulting set. It allows you to merge rows from multiple queries into a single result set of data.
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:
contractor_id | first_name | last_name | |
---|---|---|---|
1 | Alice | Johnson | alice.johnson@contractor.com |
2 | Bob | Brown | bob.brown@contractor.com |
3 | Charlie | Davis | charlie.davis@contractor.com |
4 | David | Evans | david.evans@contractor.com |
... | ... | ... | ... |
50 | Yvonne | Zimmer | yvonne.zimmer@contractor.com |
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:
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:
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.
Tudo estava claro?
Conteúdo do Curso
Intermediate SQL
4. DDL and DML in 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
.
UNION
in SQL is used to combine the results of two or more SELECT
queries into one resulting set. It allows you to merge rows from multiple queries into a single result set of data.
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:
contractor_id | first_name | last_name | |
---|---|---|---|
1 | Alice | Johnson | alice.johnson@contractor.com |
2 | Bob | Brown | bob.brown@contractor.com |
3 | Charlie | Davis | charlie.davis@contractor.com |
4 | David | Evans | david.evans@contractor.com |
... | ... | ... | ... |
50 | Yvonne | Zimmer | yvonne.zimmer@contractor.com |
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:
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:
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.
Tudo estava claro?