Imagine ter uma ferramenta que possa detectar automaticamente problemas de desempenho de JPA e Hibernate. Otimizador de Hipersistência é essa ferramenta!
Introdução
Neste artigo, vamos ver como funciona o operador SQL EXISTS e quando o deve utilizar.
Embora o operador EXISTS esteja disponível desde SQL:86, a primeira edição do SQL Standard, descobri que ainda há muitos criadores de aplicações que não se apercebem de quão poderosas são as expressões de subconsultas SQL quando se trata de filtrar uma dada tabela com base numa condição avaliada numa tabela diferente.
Modelo de tabela de base de dados
Vamos assumir que temos as duas tabelas seguintes na nossa base de dados, que formam uma relação de uma para muitas tabelas. A tabela student é a tabela de base de dados, e a student_grade é a tabela infantil uma vez que tem uma student_id coluna Chave Estrangeira referenciando a id coluna Chave Primária na tabela student.
O student tabela contém os dois registos seguintes:
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
E, a tabela student_grade armazena as notas que os alunos receberam:
| id | class_name | grade | student_id ||----|------------|-------|------------|| 1 | Math | 10 | 1 || 2 | Math | 9.5 | 1 || 3 | Math | 9.75 | 1 || 4 | Science | 9.5 | 1 || 5 | Science | 9 | 1 || 6 | Science | 9.25 | 1 || 7 | Math | 8.5 | 2 || 8 | Math | 9.5 | 2 || 9 | Math | 9 | 2 || 10 | Science | 10 | 2 || 11 | Science | 9.4 | 2 |
SQL EXISTS
Vamos dizer que queremos obter todos os alunos que receberam uma nota de 10 na aula de Matemática.
Se só estamos interessados no identificador do aluno, então podemos executar uma consulta como esta:
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Mas, a aplicação está interessada em exibir o nome completo de um aluno, e não apenas o identificador, por isso precisamos de informações da tabela student também.
Para filtrar o student registos que tenham uma nota 10 em Matemática, podemos usar o operador EXISTS SQL, como este:
SELECT id, first_name, last_nameFROM studentWHERE EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade = 10 AND student_grade.class_name = 'Math')ORDER BY id
Ao executar a consulta acima, podemos ver que apenas a linha Alice é seleccionada:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
A consulta externa selecciona a linha student colunas da linha em que estamos interessados em regressar ao cliente. Contudo, a cláusula WHERE está a utilizar o operador EXISTS com uma subconsulta interna associada.
O operador EXISTS retorna true se a subconsulta retornar pelo menos um registo e false se nenhuma linha for seleccionada. O motor da base de dados não tem de executar inteiramente a subconsulta. Se um único registo for igualado, o operador EXISTS retorna true, e a outra linha de consulta associada é seleccionada.
A subconsulta interna está correlacionada porque a coluna student_id do student_grade tabela é comparada com a tabela id coluna do student tabela.
SQL NOT EXISTS
Consideremos que queremos seleccionar todos os alunos que não tenham nota inferior a 9. Para isso, podemos utilizar NOT EXISTS, o que nega a lógica do operador EXISTS.
Por isso, o operador NOT EXISTS retorna true se a subconsulta subjacente não retorna nenhum registo. Contudo, se um único registo for igualado pela subconsulta interna, o operador NOT EXISTS retorna false, e a execução da subconsulta pode ser interrompida.
Para combinar todos student registos que não tenham associado student_grade com um valor inferior a 9, podemos executar a seguinte consulta SQL:
SELECT id, first_name, last_nameFROM studentWHERE NOT EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade < 9)ORDER BY id
Ao executar a consulta acima, podemos ver que apenas o registo Alice é igualado:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Cool, certo?
Online Workshops
Se gostou deste artigo, aposto que vai adorar o meu próximo Workshop Online de 4 dias!
AltoPerformance Java Persistence Online Workshop (4 horas x 4 dias) entre 19 e 22 de Abril
Conclusion
A vantagem de utilizar os operadores SQL EXISTS e NÃO EXISTS é que a execução da subconsulta interna pode ser interrompida desde que seja encontrado um registo correspondente.
Se a subconsulta necessitar de digitalizar um grande volume de registos, a paragem da execução da subconsulta assim que um único registo for correspondido pode acelerar grandemente o tempo de resposta global da consulta.
/div>