Consultas aninhadas em SQL
A linguagem SQL permite aninhar uma consulta dentro de outra consulta. Isso torna possível escrever uma consulta muito grande que fará algo grande e complexo, embora a legibilidade do código seja bastante reduzida.
Dependendo de quantos valores são retornados pelas subconsultas, a área onde eles podem ser aplicados muda. São três opções no total:
- A subconsulta retorna um único valor (uma coluna e uma linha).
- A subconsulta retorna uma lista de valores (uma tabela com uma coluna).
- A subconsulta retorna uma tabela (muitas colunas, qualquer número de linhas).
Vejamos um exemplo para cada caso.
Subconsulta com resultado escalar
Vamos encontrar uma lista de todos os nossos funcionários da tabela de funcionários cujo salário é superior à média da empresa. Como podemos fazer isso?
Podemos filtrar facilmente os funcionários comparando seus salários com a média, se soubermos disso com antecedência. Ao mesmo tempo, já escrevemos uma consulta que nos permite calcular o salário médio dos funcionários da empresa. Vamos lembrá-lo:
SELECT AVG(salary) FROM employee
Então o MySQL nos retornou o valor: 76833.3333 .
Como agora encontrar uma lista de todos os funcionários cujo salário está acima da média? Também é muito simples:
SELECT * FROM employee
WHERE salary > 76833.3333
O resultado desta consulta será:
eu ia | nome | ocupação | salário |
---|---|---|---|
1 | Ivanov Ivan | Programador | 100000 |
2 | Petrov Petr | Programador | 80000 |
4 | Rabinovich Moisha | Diretor | 200000 |
E agora apenas combinamos as duas solicitações substituindo a primeira solicitação em vez do valor 76833:
SELECT * FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee)
O resultado desta consulta será o mesmo:
eu ia | nome | ocupação | salário |
---|---|---|---|
1 | Ivanov Ivan | Programador | 100000 |
2 | Petrov Petr | Programador | 80000 |
4 | Rabinovich Moisha | Diretor | 200000 |
Subconsulta com lista de valores
Você se lembra de uma vez que tínhamos uma tarefa - encontrar todos os registros de uma tabela para os quais não havia registros correspondentes de outra?
Havia também esta foto:
Se não me engano, a tarefa é a seguinte: exibir uma lista de todos os funcionários da tabela de funcionários para os quais não há tarefas na tabela de tarefas .
Vamos também encontrar uma solução em duas etapas.
Primeiro, vamos escrever uma consulta que retornará o id de todos os funcionários que possuem tarefas na tabela de tarefas. Apenas lembre-se de duas coisas:
- remover duplicatas - use a palavra-chave DISTINCT.
- remova valores NULL do resultado.
SELECT DISTINCT employee_id FROM task
WHERE employee_id IS NOT NULL
E aqui temos um belo resultado de tal pedido:
ID do Empregado |
---|
1 |
2 |
5 |
4 |
6 |
Vamos escrevê-lo temporariamente por conveniência como uma sequência: 1,2,5,4,6. Agora vamos escrever uma segunda consulta - para a tabela de funcionários, que retornará uma lista de funcionários cujo id não está na primeira lista:
SELECT * FROM employee
WHERE id NOT IN (1,2,5,4,6)
E 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 |
E agora, como no exemplo anterior, você pode combinar as duas requisições simplesmente substituindo o corpo da primeira requisição ao invés da lista de id.
SELECT * FROM employee
WHERE id NOT IN (
SELECT DISTINCT employee_id FROM task
WHERE employee_id IS NOT NULL
)