Cláusula UNION

Great job mastering inner queries! 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.

Repasemos brevemente los puntos clave al trabajar con UNION:

1. Número y orden de las columnas: Todas las consultas combinadas con UNION deben tener el mismo número de columnas, y estas columnas deben estar en el mismo orden;

2. Tipos de datos: Los tipos de datos de las columnas correspondientes en cada consulta deben ser compatibles;

3. Filas únicas: Por defecto, UNION elimina las filas duplicadas. Para incluir duplicados, se utiliza UNION ALL.

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.