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:

Columns Count and Order: All queries combined with UNION must have the same number of columns in the same order;
Data Types: The columns in each query must have compatible data types;
Unique Rows: By default, UNION removes duplicate rows. Use UNION ALL if you want to keep duplicates.

Vamos rapidamente rever os pontos chave ao trabalhar com UNION:

Número e ordem das colunas: Todas as consultas combinadas com UNION devem ter o mesmo número de colunas, e estas colunas devem estar na mesma ordem;
Tipos de dados: Os tipos de dados das colunas correspondentes em cada consulta devem ser compatíveis;
Linhas únicas: Por padrão, UNION remove linhas duplicadas. Para incluir duplicatas, utiliza-se 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:


              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

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:


              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'

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.

1. Quais colunas são necessárias ao usar a cláusula `UNION` em SQL?

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

Tudo estava claro?

Obrigado pelo seu feedback!

Seção 2. Capítulo 5

SQL Intermediário

Cláusula UNION

1. Quais colunas são necessárias ao usar a cláusula UNION em SQL?

2. How does the UNION clause treat duplicate rows by default?

1. Quais colunas são necessárias ao usar a cláusula `UNION` em SQL?

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