MySQL Subconsulta com exemplos

O que é uma subconsulta em SQL?

A subconsulta É uma consulta SELECT contida dentro de outra consulta. A consulta SELECT interna geralmente é usada para determinar os resultados da consulta SELECT externa; portanto, o banco de dados avalia primeiro a instrução interna e, em seguida, passa sua saída para a consulta superior.

A consulta interna é chamada de consulta interna ou consulta aninhada, e a instrução que a contém é chamada de consulta externaVamos analisar a sintaxe da subconsulta.

O diagrama acima mostra o formato geral da instrução: o SELECT externo fornece as colunas que você deseja visualizar, e o SELECT interno entre colchetes fornece o valor ou a lista de valores que a cláusula WHERE utiliza para comparação.

Por que usar uma subconsulta?

Antes de analisarmos os diferentes tipos, é útil saber quando uma subconsulta justifica sua utilização em uma instrução.

Uma subconsulta responde a uma pergunta cujo valor de filtro não é conhecido antecipadamente. Ele precisa ser calculado a partir dos próprios dados. Uma reclamação comum dos clientes da MyFlix Video Library é o baixo número de títulos de filmes, e a gerência quer comprar filmes para a categoria que tem o menor número de títulos. Ninguém sabe qual é essa categoria até que o banco de dados seja consultado, então o valor precisa ser calculado primeiro e depois usado como filtro.

As subconsultas estão emtracativo por três razões práticas:

• Legibilidade: Cada parte da lógica está em seu próprio bloco entre colchetes, de modo que a declaração se lê como uma sequência de pequenas perguntas em vez de uma expressão complexa.
• Isolamento: Uma consulta interna pode ser executada isoladamente para confirmar se retorna o valor esperado, o que facilita muito os testes e a depuração.
• Flexibilidade: O mesmo padrão funciona nas cláusulas WHERE, HAVING, SELECT e FROM, bem como dentro das instruções INSERT, UPDATE e DELETE.

A contrapartida é a velocidade, que será analisada na comparação JOIN mais adiante neste artigo.

Tipos de subconsultas em MySQL

MySQL Suporta três tipos de subconsultas, e o tipo é determinado pelo formato do resultado retornado pela consulta interna. Cada tipo é explicado abaixo com um exemplo prático usando o banco de dados myflixdb.

1) Subconsulta escalar

A subconsulta escalar Retorna exatamente uma linha e uma coluna, o que significa que retorna um único valor. Como o resultado é um único valor, ele pode ser usado em qualquer lugar onde um valor literal seja permitido. Voltando ao problema do MyFlix acima, você pode usar uma consulta como esta:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Isso produz o seguinte resultado:

Vejamos como essa consulta funciona.

Como mostra o diagrama de execução, MySQL primeiras corridas SELECT MIN(category_id) FROM movies, recebe um valor e somente então executa a consulta externa com esse valor. Como apenas um valor é retornado, os operadores permitidos são o conjunto de comparação padrão: =, <> (ou !=), >, >=, < e <=.

💡 Dica: Se uma subconsulta for colocada depois = retorna mais de uma linha, MySQL gera o erro 1242, A subconsulta retorna mais de uma linha.. Mude a operadora para INou então, restrinja a cláusula WHERE interna.

2) Subconsulta de linha

A subconsulta de linha também retorna uma única linha, mas essa linha pode conter mais de uma coluna. Portanto, a consulta externa compara uma linha de valores com um construtor de linha, em vez de com um único valor.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Os operadores permitidos são os mesmos operadores de comparação listados acima, aplicados à linha inteira de uma só vez.

3) Subconsulta de tabela

A subconsulta de tabela retorna várias linhas e, frequentemente, várias colunas, portanto, a consulta externa deve usar um operador de conjunto, como IN, NOT IN, ANY, ALL, ou EXISTS.

Suponha que você queira os nomes e números de telefone dos membros que alugaram um filme e ainda não o devolveram, para que você possa ligar para eles e lembrá-los. Você pode usar uma consulta como esta:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Vejamos como essa consulta funciona.

Neste caso, a consulta interna retorna mais de um resultado, portanto a lista de números de sócio é passada para o IN O operador e todos os membros correspondentes são retornados.

Aninhamento de subconsultas em vários níveis de profundidade

Até agora, você viu dois níveis. Uma subconsulta também pode conter outra subconsulta, o que produz uma instrução aninhada tripla.

Suponha que a gerência queira premiar o membro que paga a maior quantia. Podemos executar uma consulta como esta:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

A consulta mais interna encontra o maior pagamento, a consulta do meio converte esse valor em um número de membro e a consulta externa converte o número de membro em um nome. A consulta acima produz o seguinte resultado:

Como usar subconsultas com INSERT, UPDATE e DELETE

As subconsultas não se limitam a instruções SELECT. O mesmo padrão entre colchetes funciona dentro de instruções de modificação de dados, o que permite que um conjunto inteiro de linhas seja alterado em uma única passagem, sem a necessidade de criar uma tabela temporária.

INSERIR com uma subconsulta. Uma subconsulta pode fornecer as linhas que serão inseridas, copiando dados de uma tabela para outra. A lista de colunas da instrução SELECT deve corresponder à lista de colunas da instrução INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

ATUALIZAÇÃO com uma subconsulta. Aqui, a consulta interna decide quais linhas serão alteradas. O exemplo abaixo sinaliza todos os membros cujo aluguel ainda está em atraso.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

EXCLUIR com uma subconsulta. A mesma ideia remove as linhas que satisfazem uma condição presente em uma segunda tabela.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Aviso: MySQL Não é permitido que uma instrução modifique uma tabela e selecione da mesma tabela dentro de uma subconsulta na cláusula FROM. Se o erro 1093 aparecer, envolva a consulta interna em uma tabela derivada, por exemplo. SELECT * FROM (SELECT ...) AS t, De modo que MySQL materializa o resultado antes que a alteração seja aplicada. Também é aconselhável executar primeiro o SELECT interno isoladamente e confirmar a contagem de linhas antes de executar um ATUALIZAÇÃO EXCLUIR em produção.

Subconsultas vs. junções

Tanto uma subconsulta quanto um JOIN podem combinar informações de mais de uma tabela, então a pergunta natural é qual delas usar.

Em comparação com junções, as subconsultas são simples de usar e fáceis de ler. Elas não são tão complicadas quanto... Juntae, portanto, são frequentemente usados ​​por SQL iniciantes.

No entanto, as subconsultas apresentam problemas de desempenho. Usar um JOIN em vez de uma subconsulta pode, por vezes, proporcionar um aumento de desempenho de até 500 vezes, porque o otimizador consegue resolver um JOIN numa única passagem, em vez de avaliar repetidamente a instrução interna.

Se puder escolher, é recomendado usar um JOIN em uma subconsulta. As subconsultas devem ser usadas apenas como solução alternativa quando não for possível usar uma operação JOIN para atingir o objetivo descrito acima.

As subconsultas também são fáceis de decompor em componentes lógicos individuais, o que é muito útil quando ensaio e depurando as consultas.

Perguntas Frequentes