Operadores de Conjuntos
As operações de conjuntos quando vistas são lembradas na matéria de matemática, quando nossos professores da matéria nos falavam em que poderíamos formar conjuntos de números unindo-os, diferenciando-os e levando em consideração o que ambos possuem em comum. No Oracle existem funções que levam em consideração este conceito, e com isso é efetuado uma montagem dos dados formando uma espécie de “Conjuntos de Dados”, ou seja, funções que montam conjuntos de dados com os mesmos princípios básicos das operações de conjuntos vistas lá na matéria de matemática. Estas funções são: UNION, UNION ALL, INTERSECT e MINUS.
Os operadores de conjunto combinam os resultados de duas consultas de componentes em um único resultado. As consultas contem operadores estabelecidos que são chamados de consultas compostas.
Com estes operadores, é possível combinar várias consultas usando as operações de conjunto, ou seja, todos os operadores estabelecidos têm a mesma precedência. Se uma instrução SQL contém vários operadores de conjunto, em seguida, o Oracle Database avalia-os a partir da esquerda para a direita, a menos que sejam especificados expressamente em outra ordem por parênteses.
As expressões correspondentes nas listas de seleção das consultas compostas onde devem corresponder em um número e deve estar no grupo de tipo de dados (sejam eles numéricos ou caracteres).
Se ambas as consultas retornar valores do tipo de texto de comprimento igual, então os valores devolvidos têm tipo de dados texto para a extensão, porém se as consultas retornarem valores do tipo de texto e com diferentes comprimentos, em seguida, o valor de retorno é texto variado e com o comprimento do maior valor. Também há a regra para que se um valor retornado possuí retorne o valor para o tipo de dados em texto variado, os valores devolvidos têm tipo de dados texto variado.
Para as consultas que selecionam o tipo de dados numéricos, o seu retorno é determinado pela precedência numérica, isto é, se uma consulta seleciona apenas valores do tipo binário duplo, o seu retorno será de binário duplo. Também há a regra para se caso nenhuma consulta dos valores retornar o tipo de dados de binários duplos, mas qualquer outra consulta retornar valores do tipo binários flutuantes, o resulta será de valores devolvidos em binários flutuantes. Porém se todas as consultas selecionar valores do tipo numérico, o resultado será do tipo numérico.
Nas consultas usando operadores de conjunto, o Oracle não realiza a conversão implícita entre grupos de tipos de dados. Com isso as expressões correspondentes de consultas de componentes resolvem ambos os dados de caracteres e dados numéricos, retornando um erro:
“ORA-01790: A EXPRESÃO DEVE TER O MESMO TIPO DE DADOS DA EXPRESSÃO CORRESPONDENTE”Comando errado que foi executado:
SELECT 1, SYSDATE FROM DUAL
UNION
SELECT '1', SYSDATE FROM DUAL;Comando correto que deve ser executado:
SELECT 1, SYSDATE FROM DUAL
UNION
SELECT 1, SYSDATE FROM DUAL;Além da situação em que o Oracle retornará um erro em caso que os tipos de dados não sejam iguais, também irá gerar um erro, caso as consultas não possuam a mesma quantidade de colunas, o erro retornado será o seguinte:
“ORA-01789: O BLOCO DE CONSULTA POSSUÍ UM NÚMERO INCORRETO DE COLUNAS DE RESULTADO”Comando errado que foi executado:
SELECT 1 FROM DUAL
UNION
SELECT 1, SYSDATE FROM DUAL;Comando correto que deve ser executado:
SELECT 1, SYSDATE FROM DUAL
UNION
SELECT 1, SYSDATE FROM DUAL;A ordenação dos registros quando utilizados os operadores de conjuntos, devem sempre ser escrito no final da consulta do último operador de conjunto, não há a necessidade de escrever em cada consulta do banco de dados efetuada, caso isso ocorra, o seguinte erro surgirá:
“ORA-00933: COMANDO SQL NÇÃO ENCERRADO ADEQUADAMENTE”Comando errado que foi executado:
SELECT 1, SYSDATE FROM DUAL ORDER BY 1
UNION
SELECT 1, SYSDATE FROM DUAL ORDER BY 1;Comando correto à ser executado:
SELECT 1, SYSDATE FROM DUAL
UNION
SELECT 1, SYSDATE FROM DUAL ORDER BY 1;UNION
A expressão de união “UNION “ combina os resultados de duas linhas, eliminando as linhas duplicadas, ou seja, todas as linhas que aparecerão no resultado serão distintas.
Abaixo há um exemplo onde temos a tabela de funcionários e a tabela de departamentos para o usuário padrão do Oracle HR (Human Resources), e com isso queremos unir ambas as tabelas, efetuando um filtro para o departamento 90 – Executivo, cujo o gerente deste departamento é Steven King. No resultado mais abaixo (Figura 1-1), nota-se que a consulta agrupou os quatro funcionários em dois registros, um com os funcionários 100 – Steven King, 101 – Neena Kochhar, 102 – Lex De Haan e criou outro registro para o funcionário 178 – Kimberely Grant, pois ele é subordinado ao funcionário 149 – Eleni Zlotkey (não aparece neste resultado, pois ela pertence ao departamento 80 – Vendas).
Exemplo:
SELECT DEPARTMENT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
UNION
SELECT DEPARTMENT_ID, MANAGER_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 90
ORDER BY 2;Figura 1-1
UNION ALL
Ao contrário da união que retorna somente as linhas distintas que aparecem em qualquer resultado, a operação unir tudo “UNION ALL” não elimina linhas duplicadas selecionadas, porém da mesma forma que a união simples, este comando deve possuir os mesmos tipos de dados para as colunas em suas devidas posições e também a mesma quantidade de colunas. (LORENTZ, 2005)
No exemplo abaixo (Figura 1-2), declaramos que a função deve mostrar todos os resultados selecionados, independentemente se os mesmos se repetem, ou seja, não são resultados distintos, igual ao UNION. Com a mesma consulta executada anteriormente, apenas deve-se acrescentar a palavra ALL ao lado da palavra UNION, e que o resultado será complemente diferente. Também se deve notar que o resultado mostra um registro à mais, isto por que é efetuado uma consulta na tabela de funcionários para os mesmos que trabalham no departamento de executivos, e devemos lembrar que também é efetuado uma consulta na tabela de departamentos, como não é feito distinção alguma para os resultados, a consulta da tabela de departamentos também mostrará seus resultados.
Exemplo:
SELECT DEPARTMENT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
UNION ALL
SELECT DEPARTMENT_ID, MANAGER_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 90
ORDER BY 2;Figura 1-2
INTERSECT
A operação de interseção “INTERSECT” combina os resultados e retorna somente os linhas resultantes por todas as consultas. No entanto ele só retorna as linhas de conjuntos de dados caso, um registro exista em uma consulta e nas demais também.
Quando utilizado a função de interseção em nosso exemplo, este irá combinar todos os registros que sejam iguais em todas as consultas, no caso, o departamento de executivos com o gerente 100 – Steven King, será a única linha retornada em nosso resultado, isto devido que todos os funcionários do departamento de executivos são subordinados ao Steven King, porém há um funcionário que não listou no resultado (figura 1-3), que seria o código 178 – Kimberely Grant com o gestor do departamento 149 – Eleni Zlotkey, por que ela não está como gestor do departamento de executivos e sim o Steven King.
Exemplo:
SELECT DEPARTMENT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
INTERSECT
SELECT DEPARTMENT_ID, MANAGER_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 90
ORDER BY 2;Figura 1-3
MINUS
O operador de conjuntos, menos “MINUS”, é utilizado para retornar todas as linhas na primeira instrução de consulta e que não são resultados pelas demais instruções de consultas. Cada instrução de consulta irá definir um conjunto de dados, e o operador “MINUS” irá recuperar todos os registros à partir do primeiro conjunto de dados e em seguida remover os resultados de todos as demais consultas de dados.
Em nosso exemplo o operador menos, irá retornar apenas o contrário da interseção, ou seja, apenas o valor da funcionária 178 – Kimberely Grant do departamento de executivos, onde está como gerente do departamento no cadastro de funcionários 149 – Eleni Zlotke, conclui-se que o operador de menos buscou todos os funcionários com o código do departamento de executivos (90) e encontrou em todos os seus resultados o gerente Steven King à partir da segunda consulta, porém não encontrou a funcionária 178 – Kimberely Grant, conforme a figura 1-4.
Exemplo:
SELECT DEPARTMENT_ID, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
MINUS
SELECT DEPARTMENT_ID, MANAGER_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 90
ORDER BY 2;Figura 1-4
Restrições referentes aos comandos de operadores de conjunto do Oracle:
- Os operadores de conjuntos não são válidos em colunas do tipo BLOB, CLOB, BFILE, VARRAY ou TABELAS ANINHADAS;
- A União, Interseção e Menos, não são válidos em colunas do tipo LONG;
- Se a lista de seleção que precede o operador de conjunto contém uma expressão, então deve-se fornecer um nome para a colunam para que possa se referir na clausula de ordenação;
- Não é possível especificar também um operador de conjunto em uma clausula para atualização (FOR_UPDATE_CLAUSE);
- Também não é possível especificar a clausula de ordenação na subconsulta quando utilizado este operador;
- Não é permitido usar esses operadores em consultas declaradas contendo tabelas de expressões de coleções.
Referências
LORENTZ, Diana. ORACLE® DATABASE: SQL REFERENCE 10g RELEASE 2 (10.2) – B14200-02. 2005.
Agradecimentos
FABIO PELICER
ALEXANDRE PIETROBELLI
Abraços