Usando os Operadores de Conjunto

Os operadores de conjunto são usados para unir os resultados de duas (ou mais) instruções SELECT. Os operadores SET disponíveis no Oracle 11g são UNION, UNION ALL, INTERSECT e MINUS.

O operador de conjunto UNION retorna os resultados combinados das duas instruções SELECT. Essencialmente, ele remove duplicatas dos resultados, ou seja, apenas uma linha será listada para cada resultado duplicado. Para contrariar este comportamento, use o operador de conjunto UNION ALL que retém as duplicatas em o resultado final.INTERSECT lista apenas os registros que são comuns a ambas as consultas SELECT; o operador de conjunto MINUS remove os resultados da segunda consulta da saída se eles também forem encontrados nos resultados da primeira consulta. As operações de conjunto INTERSECT e MINUS produzem resultados não duplicados.

Todos os operadores SET compartilham o mesmo grau de precedência entre eles. Em vez disso, durante a execução da consulta, o Oracle inicia a avaliação da esquerda para a direita ou de cima para baixo. Se forem usados parênteses explicitamente, a ordem pode ser diferente, pois os parênteses teriam prioridade sobre operadores pendurados.

Pontos para lembrar -

O mesmo número de colunas deve ser selecionado por todas as instruções SELECT participantes. Os nomes das colunas usados na exibição são retirados da primeira consulta.
Os tipos de dados da lista de colunas devem ser compatíveis / implicitamente conversíveis pelo oracle. O Oracle não realizará a conversão de tipo implícita se as colunas correspondentes nas consultas de componente pertencerem a grupos de tipos de dados diferentes. Por exemplo, se uma coluna na primeira consulta de componente for do tipo DATE e a coluna correspondente na segunda consulta de componente for de dados digite CHAR, o Oracle não executará a conversão implícita, mas gerará o erro ORA-01790.
A ordem posicional deve ser usada para classificar o conjunto de resultados. A ordenação de conjuntos de resultados individuais não é permitida com operadores Set. ORDER BY pode aparecer uma vez no final da consulta. Por exemplo,
Os operadores UNION e INTERSECT são comutativos, ou seja, a ordem das consultas não é importante; não altera o resultado final.
Em termos de desempenho, UNION ALL mostra melhor desempenho em comparação com UNION porque os recursos não são desperdiçados na filtragem de duplicatas e na classificação do conjunto de resultados.
Os operadores de conjunto podem fazer parte das subconsultas.
Operadores de conjunto não podem ser usados em instruções SELECT contendo expressões de coleção TABLE.
As tabelas LONG, BLOB, CLOB, BFILE, VARRAY ou aninhada não são permitidas para uso em operadores de conjunto. A cláusula de atualização não é permitida com os operadores de conjunto.

UNIÃO

Quando várias consultas SELECT são unidas usando o operador UNION, o Oracle exibe o resultado combinado de todas as consultas SELECT compostas, depois de remover todas as duplicatas e em ordem de classificação (crescente por padrão), sem ignorar os valores NULL.

Considere as cinco consultas abaixo unidas usando o operador UNION. O conjunto de resultados combinados final contém o valor de todos os SQLs. Observe a remoção da duplicação e a classificação dos dados.

SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL 
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6

Para ser observado, as colunas selecionadas nas consultas SELECT devem ser de tipo de dados compatível. O Oracle lança uma mensagem de erro quando a regra é violada.

SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;

SELECT TO_DATE('12-OCT-03') FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

UNION ALL

UNION e UNION ALL são semelhantes em seu funcionamento, com uma ligeira diferença. Mas UNION ALL fornece o conjunto de resultados sem remover a duplicação e classificar os dados. Por exemplo, na consulta acima, UNION é substituído por UNION ALL para ver o efeito.

Considere a consulta demonstrada na seção UNION. Observe a diferença na saída que é gerada sem classificação e desduplicação.

SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL 
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

INTERSECT

Usando o operador INTERSECT, o Oracle exibe as linhas comuns de ambas as instruções SELECT, sem duplicatas e dados organizados em ordem de classificação (crescente por padrão).

Por exemplo, a consulta SELECT abaixo recupera o salário que é comum no departamento 10 e 20. De acordo com os padrões ISO SQL, INTERSECT está acima de outros na precedência de avaliação de operadores de conjunto, mas isso ainda não é incorporado pelo Oracle.

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY 
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MENOS

O operador menos exibe as linhas que estão presentes na primeira consulta, mas ausentes na segunda consulta, sem duplicatas e os dados organizados em ordem crescente por padrão.

SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------        
HR
FIN
ADMIN

Combinando a instrução SELECT

Pode haver cenários em que as instruções SELECT compostas podem ter contagens e tipos de dados diferentes das colunas selecionadas. Portanto, para corresponder explicitamente à lista de colunas, colunas NULL são inseridas nas posições ausentes, de modo a corresponder à contagem e ao tipo de dados das colunas selecionadas em cada instrução SELECT. Para colunas de número, zero também pode ser substituído para corresponder ao tipo das colunas selecionadas na consulta.

Na consulta abaixo, o tipo de dados do nome do funcionário (varchar2) e a identificação do local (número) não correspondem. Portanto, a execução da consulta abaixo geraria um erro devido ao problema de compatibilidade.

SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

Explicitamente, as colunas podem ser correspondidas substituindo-se NULL pelo id do local e pelo nome do funcionário.

SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;

Usando a cláusula ORDER BY em operações SET

A cláusula ORDER BY pode aparecer apenas uma vez no final da consulta que contém instruções SELECT compostas. Isso implica que instruções SELECT individuais não podem ter a cláusula ORDER BY. Além disso, a classificação pode ser baseada nas colunas que aparecem apenas na primeira consulta SELECT. Por esse motivo, é recomendável classificar a consulta composta usando as posições das colunas.

A consulta composta abaixo unifica os resultados de dois departamentos e classifica pela coluna SALÁRIO.

SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=10
UNION
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=20
ORDER BY 3;