Razões para OUTER JOIN
A propósito, você se lembra quando juntamos nossas planilhas e nossas tarefas de limpeza do escritório sumiram porque ainda não havia faxineira?
Se você executar uma consulta como esta:
SELECT * FROM task
Então obtemos este resultado:
eu ia | id_funcionário | nome | prazo final |
---|---|---|---|
1 | 1 | Corrigir um bug no front-end | 01/06/2022 |
2 | 2 | Corrigir um bug no back-end | 15/06/2022 |
3 | 5 | comprar café | 01/07/2022 |
4 | 5 | comprar café | 01/08/2022 |
5 | 5 | vai comprar café | 01/09/2022 |
6 | (NULO) | Limpe o escritório | (NULO) |
7 | 4 | Aproveite a vida | (NULO) |
8 | 6 | Aproveite a vida | (NULO) |
A tarefa “Limpar escritório” desaparece se tentarmos unir a tabela de tarefas com a tabela de funcionários por employee_id.
Para resolver esse problema, vários modificadores foram adicionados ao operador JOIN que permitem que essas linhas órfãs sejam armazenadas sem um par em outra tabela.
Deixe-me lembrá-lo da forma clássica do operador JOIN:
table 1 JOIN table 2 ON
condition
Podemos dizer ao SQL Server para garantir que todos os dados da tabela esquerda (tabela1) estejam presentes na tabela unida. Mesmo que não haja par para eles na mesa certa. Para fazer isso, você só precisa escrever:
table 1 LEFT JOIN table 2 ON condition
Se você deseja que a tabela unida tenha todas as linhas da tabela correta , então você precisa escrever:
table 1 RIGHT JOIN table 2 ON
condition
Vamos escrever uma consulta que combine todas as tarefas e funcionários para que as tarefas sem um executor não sejam perdidas. Para fazer isso, escreva uma consulta:
SELECT * FROM employee e RIGHT JOIN task t ON e.id = t.emploee_id
E o resultado desta consulta:
eu ia | nome | ocupação | salário | idade | data de afiliação | eu ia | id_funcionário | nome |
---|---|---|---|---|---|---|---|---|
1 | Ivanov Ivan | Programador | 100000 | 25 | 30/06/2012 | 1 | 1 | Corrigir um bug no front-end |
2 | Petrov Petr | Programador | 80000 | 23 | 12/08/2013 | 2 | 2 | Corrigir um bug no back-end |
4 | Rabinovich Moisha | Diretor | 200000 | 35 | 2015-05-12 | 7 | 4 | Aproveite a vida |
5 | Kirienko Anastácia | Gerente | 40000 | 25 | 2015-10-10 | 3 | 5 | comprar café |
5 | Kirienko Anastácia | Gerente | 40000 | 25 | 2015-10-10 | 4 | 5 | comprar café |
5 | Kirienko Anastácia | Gerente | 40000 | 25 | 2015-10-10 | 5 | 5 | comprar café |
6 | Vaska | gato | 1000 | 3 | 2018-11-11 | 8 | 6 | Aproveite a vida |
(NULO) | (NULO) | (NULO) | (NULO) | (NULO) | (NULO) | 6 | (NULO) | Limpe o escritório |
Outra linha foi adicionada à nossa tabela e, curiosamente, há muitos valores NULL nela. Todos os dados que foram retirados da tabela de funcionários são exibidos como NULL, pois não havia nenhum executor da tabela de funcionários para a tarefa “Limpar escritório”.
Tipos de JOIN
Existem 4 tipos de JOINs no total. Eles são apresentados na tabela abaixo:
Breve entrada | entrada longa | Explicação | |
---|---|---|---|
1 | JUNTAR | JUNÇÃO INTERNA | Somente os registros que estão nas tabelas A e B |
2 | ASSOCIAÇÃO À ESQUERDA | ESQUERDA OUTER JOIN | Todas as linhas sem um par da tabela A devem ser |
3 | JUNTAR À DIREITA | JUNÇÃO EXTERNA DIREITA | Todas as linhas sem um par da tabela B devem ser |
4 | OUTER JOIN | JUNÇÃO EXTERNA COMPLETA | Todas as linhas de pares de bases das tabelas A e B devem ser |
Para simplificar, se representarmos tabelas como conjuntos, então JOIN pode ser exibido como uma imagem:
![](https://cdn.codegym.cc/images/article/66aba568-a02a-45e0-9939-6beebbb190ed/512.jpeg)
Definir interseção significa que para uma tabela existe um registro correspondente de outra tabela à qual ela se refere.
Pergunta da entrevista
Às vezes, programadores iniciantes são bombardeados com uma pergunta muito simples em uma entrevista. Dadas as nossas tabelas, pode ser formulado da seguinte forma:
“Escreva uma consulta que exiba uma lista de todos os funcionários para os quais não há tarefas .” Primeiro, vamos tentar reformular um pouco a pergunta: "Escreva uma consulta que exiba uma lista de todos os funcionários da tabela de funcionários para os quais não há tarefas na tabela de tarefas ." Precisamos obter este conjunto:![](https://cdn.codegym.cc/images/article/d3cc4f4a-b340-4b3a-9ae0-3e38eca36bb2/512.jpeg)
Existem várias maneiras de resolver esse problema, mas começarei com a mais simples: primeiro, você pode unir nossas tabelas com um LEFT JOIN e, em seguida, usar WHERE para excluir todas as linhas para as quais os dados ausentes foram preenchidos com NULLs.
SELECT * FROM employee e LEFT JOIN task t ON e.id = t.emploee_id
WHERE t.id IS NULL
E o resultado desta consulta:
eu ia | nome | ocupação | salário | idade | data de afiliação | eu ia | id_funcionário | nome |
---|---|---|---|---|---|---|---|---|
3 | Ivanov Sergei | Testador | 40000 | trinta | 2014-01-01 | (NULO) | (NULO) | (NULO) |
A única desvantagem dessa solução é que aqui as linhas da tabela contêm NULL e, por condição, precisamos exibir uma lista de funcionários.
Para fazer isso, você precisa listar as colunas necessárias da tabela de funcionários em SELECT ou, se precisar exibir todas, pode escrever a seguinte construção:
SELECT e.* FROM employee e, task t
A solicitação completa ficará assim:
SELECT e.*
FROM employee e RIGHT JOIN task t ON e.id = t.emploee_id
WHERE t.id IS NULL
o resultado desta consulta:
eu ia | nome | ocupação | salário | idade | data de afiliação |
---|---|---|---|---|---|
3 | Ivanov Sergei | Testador | 40000 | trinta | 2014-01-01 |
O resto dos métodos são deixados para você como lição de casa. Não quero privá-lo do prazer de encontrá-los você mesmo.