Resumo : neste tutorial você aprenderá como utilizar o EXISTS
operador MySQL e quando utilizá-lo para melhorar o desempenho das consultas.
Introdução ao operador MySQL EXISTS
O EXISTS
operador é um operador booleano que retorna verdadeiro ou falso. O EXISTS
operador é frequentemente usado para testar a existência de linhas retornadas pela subconsulta .
O seguinte ilustra a sintaxe básica do EXISTS
operador:
SELECT
select_list
FROM
a_table
WHERE
[NOT] EXISTS(subquery);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Se a subconsulta retornar pelo menos uma linha, o EXISTS
operador retornará verdadeiro, caso contrário, retornará falso.
Além disso, o EXISTS
operador encerra imediatamente o processamento adicional assim que encontra uma linha correspondente, o que pode ajudar a melhorar o desempenho da consulta.
O NOT
operador nega o EXISTS
operador. Em outras palavras, retorna NOT EXISTS
verdadeiro se a subconsulta não retornar nenhuma linha, caso contrário, retorna falso.
Observe que você pode usar SELECT *
, SELECT column
, SELECT a_constant
ou qualquer coisa na subconsulta. Os resultados são os mesmos porque o MySQL ignora a lista de seleção que apareceu na SELECT
cláusula.
Exemplos de operadores MySQL EXISTS
Vejamos alguns exemplos de uso do EXISTS
operador para entender como ele funciona.
Exemplos de MySQL SELECT EXISTS
Considere o seguinte customers
e orders
as tabelas no banco de dados de amostra .
A instrução a seguir usa o EXISTS
operador para localizar o cliente que possui pelo menos um pedido:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber
= customers.customernumber);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo, para cada linha da customers
tabela, a consulta verifica o valor customerNumber
na orders
tabela. Se o customerNumber
, que aparece na customers
tabela, existir na orders
tabela, a subconsulta retornará a primeira linha correspondente.
Como resultado, o EXISTS
operador retorna verdadeiro e para de examinar a orders
tabela. Caso contrário, a subconsulta não retornará nenhuma linha e o EXISTS
operador retornará falso.
O exemplo a seguir usa a NOT EXISTS
operadora para localizar clientes que não possuem pedidos:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
NOT EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber = customers.customernumber
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Exemplos de UPDATE EXISTS do MySQL
Suponha que você precise atualizar os ramais telefônicos dos funcionários que trabalham no escritório em São Francisco.
A declaração a seguir encontra funcionários que trabalham no escritório em San Franciso
:
SELECT
employeenumber,
firstname,
lastname,
extension
FROM
employees
WHERE
EXISTS(
SELECT
1
FROM
offices
WHERE
city = 'San Francisco' AND
offices.officeCode = employees.officeCode);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Este exemplo adiciona o número 1 ao ramal telefônico dos funcionários que trabalham no escritório em São Francisco:
UPDATE employees
SET
extension = CONCAT(extension, '1')
WHERE
EXISTS(
SELECT
1
FROM
offices
WHERE
city = 'San Francisco'
AND offices.officeCode = employees.officeCode);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como funciona.
- Primeiro, a
EXISTS
operadora daWHERE
cláusula recebe apenas funcionários que trabalham no escritório em São Francisco. - Em segundo lugar, a
CONCAT()
função concatena o ramal telefônico com o número 1.
Exemplo de INSERT EXISTS do MySQL
Suponha que você queira arquivar clientes que não possuem pedidos de vendas em uma tabela separada. Para fazer isso, você usa estas etapas:
Primeiro, crie uma nova tabela para arquivar os clientes copiando a estrutura da customers
tabela:
CREATE TABLE customers_archive
LIKE customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, insira os clientes que não possuem pedidos de vendas na customers_archive
tabela usando a INSERT
instrução a seguir.
INSERT INTO customers_archive
SELECT *
FROM customers
WHERE NOT EXISTS(
SELECT 1
FROM
orders
WHERE
orders.customernumber = customers.customernumber
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, consulte os dados da customers_archive
tabela para verificar a operação de inserção.
SELECT * FROM customers_archive;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Exemplo de DELETE EXISTS do MySQL
Uma tarefa final no arquivamento dos dados do cliente é excluir da customers_archive
tabela os clientes que existem na customers
tabela.
Para fazer isso, você usa o EXISTS
operador na WHERE
cláusula da DELETE
instrução da seguinte maneira:
DELETE FROM customers
WHERE EXISTS(
SELECT
1
FROM
customers_archive a
WHERE
a.customernumber = customers.customerNumber);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Operador MySQL EXISTS vs. operador IN
Para encontrar o cliente que fez pelo menos um pedido, você pode usar a IN
operadora conforme mostrado na consulta a seguir:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
customerNumber IN (
SELECT
customerNumber
FROM
orders);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Vamos comparar a consulta que usa o IN
operador com aquela que usa o EXISTS
operador usando a EXPLAIN
instrução.
EXPLAIN SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber = customers.customernumber);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Agora, verifique o desempenho da consulta que utiliza o IN
operador.
SELECT
customerNumber, customerName
FROM
customers
WHERE
customerNumber IN (SELECT
customerNumber
FROM
orders);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A consulta que utiliza o EXISTS
operador é muito mais rápida que aquela que utiliza o IN
operador.
A razão é que o EXISTS
operador trabalha com base no princípio “pelo menos encontrado”. O EXISTS
para de verificar a tabela quando uma linha correspondente é encontrada.
Por outro lado, quando o IN
operador é combinado com uma subconsulta, o MySQL deve processar a subconsulta primeiro e depois usar o resultado da subconsulta para processar toda a consulta.
A regra geral é que se a subconsulta contiver um grande volume de dados, o EXISTS
operador fornecerá melhor desempenho.
No entanto, a consulta que utiliza o IN
operador terá um desempenho mais rápido se o conjunto de resultados retornado da subconsulta for muito pequeno.
Por exemplo, a instrução a seguir utiliza o IN
operador para selecionar todos os funcionários que trabalham no escritório em São Francisco.
SELECT
employeenumber,
firstname,
lastname
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
offices.city = 'San Francisco');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Vamos verificar o desempenho da consulta.
É um pouco mais rápido que a consulta que utiliza o EXISTS
operador que mencionamos no primeiro exemplo. Veja o desempenho da consulta que utiliza o EXIST
operador abaixo:
Neste tutorial, você aprendeu como usar o EXISTS
operador MySQL para testar a existência de linhas retornadas por uma subconsulta.