UNION Clause

We've caught the attention of a client who needs SQL queries. Before we dive into JOINs, let's learn about the UNION clause, which is essential for combining multiple tables.

Here are some important things to know about using UNION:

1. Columns Count and Order: All queries combined with UNION must have the same number of columns in the same order;

2. Data Types: The columns in each query must have compatible data types;

3. Unique Rows: By default, UNION removes duplicate rows. Use UNION ALL if you want to keep duplicates.

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.

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 break down what's happening:

We have two queries that each return three columns with the same data types. We want to see the Id, first_name, and last_name of everyone in the company. We also renamed the Id column so both queries have the same column names.

Then, we use UNION to combine the results of these queries, removing duplicates (though there are none here).

Finally, we sort the results by Id using ORDER BY.

Note

We're sorting by Id, which is a common column in both tables.

After using UNION, we get a "single large query" that we can further manipulate with clauses like ORDER BY.

We can't directly use WHERE or GROUP BY with tables combined using UNION. To apply these clauses, we need to use a subquery in the FROM section. Here's an example of how to do it:

Using a subquery in the FROM section gives us more flexibility! It might seem tricky at first, but mastering this will make writing complex queries much easier.