Trabalho com junções¶

Junção interna¶

Uma junção interna emparelha cada linha de uma tabela com as linhas correspondentes da outra tabela.

O exemplo a seguir mostra uma junção interna:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_id = p.project_id
  ORDER BY p.project_ID, e.employee_ID;

+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |       1000 |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |       1000 |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |       1001 |
+------------+------------------+-------------+-----------------+------------+

Neste exemplo, a saída contém duas colunas denominadas PROJECT_ID. Uma coluna PROJECT_ID é da tabela projects e a outra é da tabela employees. Para cada linha na saída, os valores nas duas colunas PROJECT_ID correspondem porque a consulta especificou e.project_id = p.project_id.

A saída inclui apenas pares válidos; ou seja, linhas que correspondem à condição de junção. Neste exemplo, não há nenhuma linha para o projeto denominado NewProject, que ainda não tem funcionários atribuídos, ou para o funcionário denominado NewEmployee, que ainda não foi atribuído a nenhum projeto.

Junção externa¶

Uma junção externa lista todas as linhas da tabela especificada, mesmo que essas linhas não tenham correspondência na outra tabela. Por exemplo, uma junção externa à esquerda entre projetos e funcionários lista todos os projetos, incluindo os que ainda não têm nenhum funcionário atribuído.

SELECT p.project_name, e.employee_name
  FROM projects AS p LEFT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

O projeto denominado NewProject é incluído nesta saída, mesmo que não haja nenhuma linha correspondente na tabela employees. Como não há nomes de funcionários correspondentes para o projeto denominado NewProject, o nome do funcionário é NULL.

Uma junção externa direita lista todos os funcionários (independentemente do projeto).

SELECT p.project_name, e.employee_name
  FROM projects AS p RIGHT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

Uma junção externa completa lista todos os projetos e todos os funcionários.

SELECT p.project_name, e.employee_name
  FROM projects AS p FULL OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Junção cruzada¶

Uma junção cruzada combina cada linha da primeira tabela com cada linha da segunda, criando todas as combinações possíveis de linhas, o que é chamado de produto cartesiano. Como a maioria das linhas de resultados contém partes de linhas que não estão realmente relacionadas, uma junção cruzada raramente é útil por si só. Na verdade, as junções cruzadas são geralmente o resultado de se omitir acidentalmente a condição de junção.

O resultado de uma junção cruzada pode ser muito grande e caro. Se a primeira tabela tiver N linhas e a segunda tabela tiver M linhas, então o resultado são N x M linhas. Por exemplo, se a primeira tabela tiver 100 linhas e a segunda tabela tiver 1000 linhas, então o conjunto de resultados contém 100.000 linhas.

A consulta a seguir mostra uma junção cruzada:

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

Você pode tornar a saída de uma junção cruzada mais útil aplicando um filtro na cláusula WHERE:

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  WHERE e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

O resultado desta junção cruzada e filtro é o mesmo que o resultado da seguinte junção interna:

SELECT p.project_name, e.employee_name
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Junção natural¶

Uma junção natural une duas tabelas em colunas que têm os mesmos nomes e tipos de dados compatíveis. Tanto a tabela employees quanto a projects criadas anteriormente têm uma coluna denominada project_ID. Uma junção natural constrói implicitamente a cláusula ON: ON projects.project_ID = employees.project_ID.

Se duas tabelas tiverem várias colunas em comum, uma junção natural usará todas as colunas comuns na cláusula ON construída. Por exemplo, se duas tabelas tiverem colunas denominadas city e province, uma junção natural construirá a seguinte cláusula ON:

ON table2.city = table1.city AND table2.province = table1.province

A saída de uma junção natural inclui apenas uma cópia de cada uma das colunas compartilhadas. Por exemplo, a consulta a seguir produz uma junção natural que contém todas as colunas nas duas tabelas, exceto que omite todas as cópias, exceto uma, das colunas redundantes project_id:

SELECT *
  FROM projects NATURAL JOIN employees
  ORDER BY employee_id;

+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

Você pode combinar uma junção natural com uma junção externa.

Você não pode combinar uma cláusula ON de junção natural porque a condição de junção já está implícita. Entretanto, você pode usar uma cláusula WHERE para filtrar os resultados de uma junção natural.