Resumo : neste tutorial, você aprenderá como usar o EXCEPT
operador MySQL para encontrar a diferença entre dois conjuntos de dados.
Observe que o EXCEPT
operador é suportado no MySQL a partir da versão 8.0.31. Se você usar uma versão inferior, poderá emular o operador EXCEPT (ou MINUS) .
Introdução ao operador MySQL EXCEPT
O operador MySQL EXCEPT
permite recuperar linhas de uma consulta que não aparecem em outra consulta.
Aqui está a sintaxe básica do EXCEPT
operador MySQL:
query1
EXCEPT [ALL | DISTINCT]
query2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Nesta sintaxe, EXCEPT
comparará o resultado de query1
com o conjunto de resultados de query2
e retornará as linhas do conjunto de resultados de query1
que não aparecem no conjunto de resultados de query2
.
Por padrão, o EXCEPT
operador usa a DISTINCT
opção se você a omitir. Remove EXCEPT DISTINCT
linhas duplicadas no conjunto de resultados.
Se quiser reter as linhas duplicadas, você precisará especificar a ALL
opção explicitamente.
Para usar o EXCEPT
operador, o query1
e query2
precisa seguir estas regras:
- A ordem e o número de colunas na lista de seleção das consultas devem ser iguais.
- Os tipos de dados das colunas correspondentes devem ser compatíveis.
O EXCEPT
operador retorna um conjunto de consultas com nomes de colunas derivados dos nomes de colunas da primeira consulta ( query1
).
Exemplos de operadores MySQL EXCEPT
Vejamos alguns exemplos de uso do EXCEPT
operador MySQL.
1) Exemplo simples do operador MySQL EXCEPT
Primeiro, crie duas tabelas t1
e t2
:
CREATE TABLE t1 (
id INT PRIMARY KEY
);
CREATE TABLE t2 (
id INT PRIMARY KEY
);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Segundo, insira linhas nas tabelas t1
e t2
:
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Terceiro, use o EXCEPT
operador para encontrar linhas que aparecem na tabela t1
, mas não aparecem na tabela t2
:
SELECT id FROM t1
EXCEPT
SELECT id FROM t2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----+
| id |
+----+
| 1 |
+----+
Linguagem de código: texto simples ( texto simples )
Neste exemplo, a primeira consulta retorna um conjunto de resultados (1,2,3) e a segunda consulta produz um conjunto de resultados (2,3,4).
O EXCEPT
operador retorna a linha 1 que aparece no primeiro conjunto de resultados, mas não aparece no segundo conjunto de resultados.
O seguinte diagrama de Venn ilustra a EXCEPT
operação:
2) Exemplo prático do operador MySQL EXCEPT
Usaremos as tabelas funcionários e clientes do banco de dados de exemplo para ilustrar o EXCEPT
operador:
A consulta a seguir usa o EXCEPT
operador para localizar os primeiros nomes que aparecem na customers
tabela, mas não aparecem na employees
tabela:
SELECT firstName
FROM employees
EXCEPT
SELECT contactFirstName
FROM customers;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-----------+
| firstName |
+-----------+
| Diane |
| Gerard |
| Anthony |
| Foon Yue |
| George |
| Loui |
| Pamela |
| Larry |
| Barry |
| Andy |
| Tom |
| Mami |
| Yoshimi |
+-----------+
Linguagem de código: texto simples ( texto simples )
Neste exemplo, o conjunto de resultados utiliza a firstName
coluna da primeira consulta para sua coluna.
3) Usando o operador EXCEPT com o exemplo da cláusula ORDER BY
Para classificar o conjunto de resultados retornado pelo EXCEPT
operador, você usa a ORDER BY
cláusula.
Por exemplo, a consulta a seguir usa o EXCEPT
operador para obter os primeiros nomes que aparecem na employees
tabela, mas não aparecem na customers
tabela e classificar os primeiros nomes em ordem alfabética:
SELECT firstName
FROM employees
EXCEPT
SELECT contactFirstName
FROM customers
ORDER BY firstName;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-----------+
| firstName |
+-----------+
| Andy |
| Anthony |
| Barry |
| Diane |
| Foon Yue |
| George |
| Gerard |
| Larry |
| Loui |
| Mami |
| Pamela |
| Tom |
| Yoshimi |
+-----------+
Linguagem de código: texto simples ( texto simples )
4) Usando o operador EXCEPT com a opção ALL
O exemplo a seguir usa o EXCEPT
operador com a ALL
opção de reter nomes duplicados no conjunto de resultados:
SELECT firstName
FROM employees
EXCEPT ALL
SELECT contactFirstName
FROM customers
ORDER BY firstName;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-----------+
| firstName |
+-----------+
| Andy |
| Anthony |
| Barry |
| Diane |
| Foon Yue |
| George |
| Gerard |
| Gerard |
| Larry |
| Loui |
| Mami |
| Pamela |
| Tom |
| Yoshimi |
+-----------+
Linguagem de código: texto simples ( texto simples )
No conjunto de resultados, o primeiro nome Gerard aparece duas vezes.
Resumo
- Use o operador MySQL
EXCEPT
para recuperar linhas de um conjunto de resultados que não aparecem em outro conjunto de resultados. EXCEPT DISTINCT
remove duplicatas enquantoEXCEPT ALL
retém as duplicatas.- O
EXCEPT
operador usa aDISTINCT
opção por padrão.