Uso de los operadores de conjuntos

Los operadores Set se utilizan para unir los resultados de dos (o más) sentencias SELECT. Los operadores SET disponibles en Oracle 11g son UNION, UNION ALL, INTERSECT y MINUS.

El operador de conjunto UNION devuelve los resultados combinados de las dos sentencias SELECT. Esencialmente, elimina los duplicados de los resultados, es decir, solo se listará una fila para cada resultado duplicado. Para contrarrestar este comportamiento, utilice el operador de conjunto UNION ALL que retiene los duplicados en el resultado final.INTERSECT enumera solo los registros que son comunes a ambas consultas SELECT; el operador de conjunto MINUS elimina los resultados de la segunda consulta de la salida si también se encuentran en los resultados de la primera consulta. Las operaciones de conjuntos INTERSECT y MINUS producen resultados no duplicados.

Todos los operadores SET comparten el mismo grado de precedencia entre ellos. En cambio, durante la ejecución de la consulta, Oracle comienza la evaluación de izquierda a derecha o de arriba a abajo. Si se usan explícitamente paréntesis, entonces el orden puede diferir ya que los paréntesis tendrían prioridad sobre operadores colgantes.

Puntos para recordar -

  • Todas las sentencias SELECT participantes deben seleccionar el mismo número de columnas. Los nombres de columna utilizados en la pantalla se toman de la primera consulta.

  • Los tipos de datos de la lista de columnas deben ser compatibles / implícitamente convertibles por Oracle. Oracle no realizará una conversión de tipo implícita si las columnas correspondientes en las consultas de componentes pertenecen a diferentes grupos de tipos de datos. Por ejemplo, si una columna en la consulta del primer componente es del tipo de datos DATE y la columna correspondiente en la consulta del segundo componente es de datos. escriba CHAR, Oracle no realizará una conversión implícita, pero generará el error ORA-01790.

  • Se debe usar el orden posicional para ordenar el conjunto de resultados. No se permite la ordenación de conjuntos de resultados individuales con los operadores de conjuntos. ORDER BY puede aparecer una vez al final de la consulta. Por ejemplo,

  • Los operadores UNION e INTERSECT son conmutativos, es decir, el orden de las consultas no es importante; no cambia el resultado final.

  • En cuanto al rendimiento, UNION ALL muestra un mejor rendimiento en comparación con UNION porque no se desperdician recursos en filtrar duplicados y ordenar el conjunto de resultados.

  • Los operadores de conjuntos pueden ser parte de subconsultas.

  • Los operadores de conjunto no se pueden usar en sentencias SELECT que contienen expresiones de colección TABLE.

  • Las tablas LONG, BLOB, CLOB, BFILE, VARRAY o anidadas no están permitidas para su uso en operadores Set. La cláusula For update no está permitida con los operadores set.

UNIÓN

Cuando se unen varias consultas SELECT mediante el operador UNION, Oracle muestra el resultado combinado de todas las consultas SELECT compuestas, después de eliminar todos los duplicados y en orden (ascendente de forma predeterminada), sin ignorar los valores NULL.

Considere las siguientes cinco consultas unidas mediante el operador UNION. El conjunto de resultados combinado final contiene el valor de todos los SQL. Tenga en cuenta la eliminación de duplicados y la clasificación de datos.

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

Cabe señalar que las columnas seleccionadas en las consultas SELECT deben ser de un tipo de datos compatible. Oracle lanza un mensaje de error cuando se viola la regla.

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

UNIÓN TODOS

UNION y UNION ALL son similares en su funcionamiento con una ligera diferencia. Pero UNION ALL proporciona el conjunto de resultados sin eliminar la duplicación y ordenar los datos. Por ejemplo, en la consulta anterior, UNION se reemplaza por UNION ALL para ver el efecto.

Considere la consulta demostrada en la sección UNION. Tenga en cuenta la diferencia en la salida que se genera sin clasificación ni deduplicación.

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

INTERSECARSE

Utilizando el operador INTERSECT, Oracle muestra las filas comunes de ambas declaraciones SELECT, sin duplicados y los datos organizados en orden (ascendente de forma predeterminada).

Por ejemplo, la consulta SELECT a continuación recupera el salario que es común en los departamentos 10 y 20. Según los estándares ISO SQL, INTERSECT está por encima de otros en la precedencia de la evaluación de operadores de conjuntos, pero esto aún no está incorporado por Oracle. 

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

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

MENOS

El operador menos muestra las filas que están presentes en la primera consulta pero ausentes en la segunda consulta, sin duplicados y los datos organizados en orden ascendente de forma predeterminada. 

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

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

Coincidencia de la instrucción SELECT

Puede haber escenarios en los que las declaraciones SELECT compuestas pueden tener diferentes recuentos y tipos de datos de las columnas seleccionadas. Por lo tanto, para hacer coincidir la lista de columnas explícitamente, se insertan columnas NULL en las posiciones que faltan para que coincidan con el recuento y el tipo de datos de las columnas seleccionadas en cada instrucción SELECT. Para las columnas numéricas, también se puede sustituir el cero para que coincida con el tipo de columnas seleccionadas en la consulta.

En la siguiente consulta, el tipo de datos del nombre del empleado (varchar2) y la identificación de la ubicación (número) no coinciden. Por lo tanto, la ejecución de la consulta siguiente generaría un error debido a problemas de compatibilidad.

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

Explícitamente, las columnas se pueden hacer coincidir sustituyendo NULL por ID de ubicación y nombre de empleado.

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

Usando la cláusula ORDER BY en operaciones SET

La cláusula ORDER BY solo puede aparecer una vez al final de la consulta que contiene sentencias SELECT compuestas, lo que implica que las sentencias SELECT individuales no pueden tener cláusula ORDER BY. Además, la clasificación se puede basar en las columnas que aparecen en la primera consulta SELECT solamente. Por este motivo, se recomienda ordenar la consulta compuesta utilizando posiciones de columna.

La consulta compuesta a continuación unifica los resultados de dos departamentos y los ordena por la columna SALARIO.

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;