Subqueries são consultas realizadas com o apoio de outra consulta no banco de dados.
Sua estrutura pode ser dividida em duas: a query interior (inner) e a exterior (outer).
Quando utilizamos uma subquery, a primeira a ser executada é a mais interior e é ela quem entrega o resultado para a query externa, que possui a construção maior.
Elas podem ser autocontidas (self-contained) e correlatas (correlated) e dependendo, pode ditar o resultado ao final.
ÍNDICE:
- SUBQUERY AUTO CONTIDA (SELF-CONTAINED)
- SUBQUERY CORRELATA: IGUALANDO AS QUERIES INTERNAS E EXTERNAS
- EXTRA: COMPORTAMENTO DAS SUBQUERIES
- CONCLUSÃO
SUBQUERY AUTO CONTIDA (SELF-CONTAINED)
É uma subquery que se auto referencia na estrutura interna da consulta. É como pesquisar dentro da própria tabela que estamos consultando na query externa.
O interessante aqui é que ambas as queries podem ser executadas independente uma da outra.
As subqueries self-contained permitem consultas em tabelas de fora, mas estas devem retornar valores scalar (único), do contrário acusará erro.
Veja neste exemplo de consulta que estou realizando. Perceba que utilizo uma função de agregação para tornar o valor linear.
Como a subquery iguala uma com a outra, o banco espera retornar um valor único. Uma vez que isso não acontece, ocorre erro de execução.
Sempre que houver um operador lógico de comparação entre as duas queries, teremos uma subquery escalar, podendo ser self-contained ou não, como o caso acima.
Lembre-se que uma self-contained subquery consulta a própria tabela da query externa. É uma auto consulta na própria tabela.
Outro ponto importante que é necessário destacar em uma subquery é que ela não lida com valores desconhecidos (unknown) no resultado. Sempre que houver a chance de retornar NUll na consulta, ela retornará vazio.
A solução seria adicionar no WHERE o IS NULL. Veja.
MÚLTIPLOS RESULTADOS EM SELF-CONTAINED SUBQUERY: IN E EXISTS
Nem sempre as subqueries autocontidas retornarão valores lineares, quero dizer, elas não são obrigadas a trabalhar desse jeito.
As situações dos exemplos anteriores são muito aplicadas em consultas pontuais, mas podemos perfeitamente trabalhar com múltiplos resultados em uma self-contained subquery.
Como perceberam, na query que pesquisei os produtos que não possuíam uma cor registrada, ela retornou diversos resultados – isso foi ocasionado pela mudança do operador na cláusula WHERE.
Na subquery abaixo, eu quis saber quem da tabela Person é funcionário da empresa. Como eu espero mais de um valor como resultado, preciso utilizar o IN.
Se quisermos negar um conjunto de valores no resultado, poderíamos utilizar o NOT IN como operador na cláusula WHERE para tal.
Veja que nesse exemplo, utilizei uma self-contained para mostrar que não há limitação dos resultados.
Podemos dizer que o IN trabalha apenas considerando TRUE e FALSE em sua avaliação lógica. Assim sendo, NULLS são eliminados por serem desconhecidos do operador.
Então, caso desconfie que haja NULLS no resultado por desconhecer o banco de dados e as tabelas que está consultando, utilize o IS NULL.
Assim, estamos ‘forçando’ na query os resultados que seriam desconhecidos para o IN.
Outro operador que possui um comportamento similar é o EXISTS. Assim como o IN, ele também trabalha apenas com TRUE E FALSE.
O interessante aqui é que se o EXISTS encontrar apenas um valor dentro do que lhe foi passado, todo o resto será verdadeiro.
Veja nesse primeiro exemplo com uma query que possui o resultado limitado pelo WHERE.
Como não há um produto com o ID = 1000, não há resultado. Porém, quando passamos apenas um valor como verdadeiro, veja o que ocorre.
Não podemos dizer o mesmo quando utilizamos o AND na subquery. Se uma das avaliações for falsa, todo o resto será, como deve ser.
O mais interessante do predicado EXISTS é a sua otimização quanto ao uso do tão temido SELECT * FROM.
Por ele considerar apenas TRUE e FALSE em sua avaliação lógica, ao encontrar o primeiro valor verdadeiro, a query é finalizada e todo o resultado entregue.
Mesmo limitando com o WHERE, o filtro não atua como esperado. Então, mesmo que seja otimizado, apenas para melhor entendimento do resultado, sugiro que utilize em tabelas menores ou utilize o IN.
O problema do EXISTS é que quando um valor é TRUE, todos são. Daí a consulta acaba retornando todas as linhas da query externa, como podemos ver no resultado abaixo.
.
E comparando o resultado dos dois operadores, note a diferença e como fica mais fácil entender quando utilizamos o IN em relação ao EXISTS.
Ao avaliar que o produto com ID 1000 era falso, a query retorna apenas os encontrados.
E gostaria de chamar atenção para uma situação interessante que percebi testando o subqueries com IN. Ao utilizar os operadores lógicos OR e AND, veja o resultado e como o banco trata e retorna os dados para o usuário.
Ainda que o produto de ID 331 exista, na segunda query destacado em amarelo, quando “viramos” para o AND e comparamos com o ID 1000, o banco considera como FALSE; excluindo-o do resultado!
Lembra muito quando mudamos o sentido do OUTER JOIN na consulta. Veja mais aqui.
Tenha muita atenção nesta situação para não cometer erros e excluir valores existentes do resultado.
SUBQUERY CORRELATA: IGUALANDO AS QUERIES INTERNAS E EXTERNAS
Ao estressar o conceito de self-contained subquery, quis firmar as bases e o funcionamento para que ficasse claro quando entrasse nas subqueries correlatas.
Esse tipo de estrutura funciona de maneira similar aos JOINS, criando uma comparação entre as consultas.
A igualdade entre as consultas ocorre na cláusula WHERE e aqui, todo tipo de independência que vimos com as auto contidas, desaparece. Não é possível executar uma query interna de forma isolada.
As subqueries correlatas são ótimas para, por exemplo, retornar pedido anterior ao atual. Este exemplo é bom para mostrar que não necessariamente, a subquery deva vir após o WHERE.
EXTRA: COMPORTAMENTO DAS SUBQUERIES
Uma situação que estava percebendo enquanto estudava sobre as subqueries foi o fato delas apresentarem um comportamento atípico, não que seja errado, mas que pode passar despercebido.
Para ser honesto, esse é o comportamento esperado. Porém, achei interessante entender o que estava acontecendo e decidi documentar aqui.
A curiosidade começou com essa query e resultado:
Eu estranhei pois mesmo sendo colunas diferentes, houve retorno da query. Então, testei com a query abaixo e veja que não houve resultado.
Outro aspecto que chamo atenção é que as colunas utilizadas na igualdade na query interna são iguais e as tabelas envolvidas também são. Porém, percebam que no WHERE externo, as colunas divergem.
Analisando a query, entendi que mesmo igualando as colunas e utilizando a mesma tabela, se a coluna do WHERE externo foi diferente do SELECT da subquery, o resultado será vazio.
No exemplo que me chamou atenção, na comparação entre BusinessentityID e PersonID, o que muda ali é apenas o nome da coluna. Quando o SQL Server executa a busca em outra tabela, ele acaba encontrando os valores e retornando ao final.
Veja abaixo a correção da estrutura anterior utilizando uma Self-Contained.
Outro ponto que pode ocorrer é uma dupla negação, obtendo um resultado diferente do esperado.
Como a avaliação lógica da query é duplamente falsa, já que as colunas são diferentes e por ter sido construída com um NOT IN, o banco entende como verdadeiro e retorna a tabela SalesOrderHeader inteira.
E por fim, caso esteja trabalhando com tabelas que possuem resultados “consolidados” como a SalesOrderHeader, se desejar um resultado scalar, não utilize subquery correlata.
Compare as duas queries e entenda.
ERRO LÓGICO DAS SUBQUERY COM A LINGUAGEM SQL
Existe uma situação que pode causar estranheza e até confunde os usuários como sendo um bug do SQL Server, mas é um comportamento padrão da linguagem.
Irei criar uma tabela e inserir alguns valores, veja:
Agora, vamos executar um SELECT comum nesta tabela e consultar os dados.
Veja que ela retorna o resultado normalmente, o que poderia passar despercebido, porém, há algo errado com essa query. Veja o próximo exemplo:
Ao especificar qual tabela a subquery iria procurar a coluna que lhe foi passada, há um erro de execução, mas repare que é a mesma tabela, na real, é a mesma query do exemplo anterior.
Solucionando a questão:
Analisando passo-a-passo o que ocorreu nas subqueries, temos:
- Cria-se uma tabela chamada MYSHIPPER com a coluna SHIPPER_ID
- Criamos uma subquery utilizando essa tabela e a coluna SHIPPER_ID buscando na tabela orders – aqui que fica “esqusito”
- Se formos pensar, não era para a query retornar resultado já que são colunas diferentes
- Porém o sql server busca o resultado na query interna e por não encontrar, vai na query externa, onde acaba encontrando!!!
- O que em teoria era para ser uma self-contained subquery (auto-contida), se tornou correlated (correlacionada) e como o sql server encontrou um resultado, retornou.
CONCLUSÃO
No artigo sobre subquery, quis trazer todos os conceitos possíveis e como podemos aplicar da melhor forma esse recurso nas operações diárias.
Embora seja uma estrutura simples de construir, tome cuidado, é muito comum perder a direção e criar uma estrutura custosa para o banco de dados.
Lembre-se que a menos que os dados já estejam dispostos na memória, o que é difícil, cada subquery é um acesso ao disco – e isso custa caro. Disco é lento. Então, evite criar mais de dois subníveis na sua consulta, opte pelo JOIN.
Em comparação com os JOINS, as subqueries não perdem muita performance quando utilizadas corretamente, embora particularmente seja mais fã do uso de JOINS.
Cuidado com alguns comportamentos indesejados com a subquery. Em algumas situações pode parecer erro do banco, mas é comportamento padrão da linguagem SQL. Consulte o último tópico.
Como boa prática, atribua um nome a tabela sempre que for trabalhar com essa estrutura. Ainda que não vá criar uma correlação, torna o código mais legível e evita erros inesperados.
OLÁ!
Se chegou até e gostou do artigo, não esqueça de deixar o seu like e compartilhar. Me ajude a alcançar mais pessoas!
Se tem alguma dúvida ou sugestão, deixe nos comentários, terei o prazer de interagir e trocar experiência.
Se quiser ficar atualizado dos artigos aqui no blog, assine abaixo!
ManageBI 