Operadores SET

Objetivos:

•Describir operadores SET.
•Utilizar un operador SET para combinar varias consultas en una sola consulta.
•Controlar el orden de las filas devueltas.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________
Operadores SET.

Los operadores SET combinan los resultados de consultas de dos o más componentes en un resultado. Las consultas que contienen operadores SET se denominan consultas compuestas.

Operador	Devuelve
UNION	Todas las filas distintas seleccionadas por cualquiera de las dos consultas
UNION ALL	Todas las filas seleccionadas por cualquiera de las dos consultas, incluidos todos los duplicados
INTERSECT	Todas las filas distintas seleccionadas por ambas consultas
MINUS	Todas las filas seleccionadas por la primera sentencia SELECT y no seleccionadas en la segunda sentencia SELECT

Todos los operadores SET tienen la misma prioridad. Si una sentencia SQL contiene varios operadores SET, Oracle Server las evaluará de izquierda (arriba) a derecha (abajo) si no hay paréntesis que especifiquen explícitamente otro orden. Debe utilizar paréntesis para especificar explícitamente el orden de evaluación en consultas que utilicen el operador INTERSECT con otros operadores SET.

Las tablas utilizadas en esta lección son:

•EMPLOYEES: Proporciona detalles relativosa todos los empleados actuales.

•JOB_HISTORY: Registra los detalles de las fechas de inicio y de finalización del primer puesto, así como el número de identificación del puesto y el departamento cuando un empleado cambia de puesto.

_____________________________________________________________________________________

Operador UNION.

El operador UNION devuelve todas las filas seleccionadas por cualquiera de las dos consultas. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas. 

•El número de columnas y los tipos de datos de las columnas que se están seleccionando deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos.

•UNION opera sobre todas las columnas que se están seleccionando.

•Los valores NULL no se ignoran durante la comprobación de duplicados.

•El operador IN tiene una prioridad más alta que el operador UNION.

•Por defecto, la salida se clasifica por orden ascendente de la primera columna de la cláusula SELECT.

Ejemplo:
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

/*En el Query anterior notamos algunos códigos de empleados repetidos, pero con el puesto de trabajo diferente, dichos registros pertenecen a los empleados que han cambia de trabajo en algún momento; Los registros idénticos en ambas tablas no muestran solo una vez.*/

---

SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;
/*Este Query es similar al anterior a el, con la pequeña diferencia de que este incluye la columna department_id. Este genera mas registros debido a que hay empleados que aparte de haber cambiado de puesto, también han cambiado de departamento .*/

_____________________________________________________________________________________

Operador UNION ALL.
Utilice el operador UNION ALL para devolver todas las filas de varias consultas.

Las instrucciones para UNION y UNION ALL son las mismas, con estas dos excepciones relacionadas con UNION ALL:
•A diferencia de lo que sucede con UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto.
•No se puede utilizar la palabra clave DISTINCT.(Nota: esto solo aplica en versiones ORACLE anteriores a 11g)

Ejemplo:
SELECT employee_id, job_id
FROM employees
UNION ALL
SELECT employee_id, job_id
FROM job_history;
/*En este ejemplo vemos como UNION ALL muestra filas repetidas ya que no elimina los duplicados, para ver la diferencia correr el mismo Query solo con UNION.*/

_____________________________________________________________________________________

Operador INTERSECT.

Utilice el operador INTERSECT para devolver las filas que son comunes a varias consultas. 

•El número de columnas y los tipos de datos de las columnas que se están seleccionando mediante las sentencias SELECT deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos. 

•Invertir el orden de las tablas interseccionadadas no altera el resultado. 

•INTERSECT no ignora los valores NULL.

Ejemplo:
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;

/*Este Query muestra los empleados que han cambia de puesto en algún momento pero han vuelto a su misma posición anterior, por dicha razón los registros para cada tabla coinciden y  por ello se muestran.*/

---
SELECT employee_id, job_id, department_id
FROM employees
INTERSECT
SELECT employee_id, job_id, department_id
FROM job_history;
/*Al agregar el columna department_id notamos que la cantidad de registros disminuye, esto se debe a que este campo es diferente en cada tabla.*/

_____________________________________________________________________________________

Operador MINUS.
Utilice el operador MINUS para devolver las filas devueltas por la primera consulta que no estén presentes en la segunda (la primera sentencia SELECT menos (MINUS) la segunda sentencia SELECT).

•El número de columnas y los tipos de datos de las columnas que se están seleccionando mediante las sentencias SELECT deben ser idénticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos.
•Para que funcione el operador MINUS, todas las columnas de la cláusula WHERE deben estar en la cláusula SELECT.

Ejemplo:

SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history;
/*En este ejemplo, los identificadores de empleado y de puesto de la tabla job_history se restan de los de la tabla employees. El juego de resultados muestra los empleados que quedan después de la resta; se representan mediante filas que existen en la tabla employees pero no en la tabla job_history. Son los registros de los empleados que no han cambiado de puesto ninguna vez.*/
---
SELECT employee_id, job_id
FROM job_history
MINUS
SELECT employee_id, job_id
FROM employees;
/*Noten que si se invierten las tablas: job_history-employees, los resultados serian distintos; Este Query muestra empleados que han cambiado de puesto por lo menos dos veces, osea cambiaron de trabajo una vez, luego lo hicieron por segunda vez y por dicha razón hay dos registros en la tabla job_history, uno para la posición actual y otro para la anterior.*/

_____________________________________________________________________________________

Directrices Operadores SET.

•Las expresiones de las listas SELECT deben corresponder en número y en tipo de datos. Las consultas que utilizan operadores UNION, UNION ALL, INTERSECT y MINUS en su cláusula WHERE deben tener el mismo número y el mismo tipo de columnas de su lista SELECT. Por ejemplo:

SELECT employee_id, department_id

FROM employees

WHERE (employee_id, department_id)  

        IN (SELECT employee_id, department_id

            FROM employees  

            UNION

            SELECT employee_id, department_id

            FROM job_history);

•La cláusula ORDER BY:

-Sólo puede aparecer al final de la sentencia.

-Aceptará el nombre de columna, un alias o la notación posicional.

•El nombre de columna, o el alias, si se utiliza en una cláusula ORDER BY, debe ser de la primera lista SELECT. Si se omite el ORDER BY, Por defecto, la salida se clasifica por orden ascendente de la primera columna de la cláusula SELECT. 

•Los operadores SET se pueden utilizar en subconsultas.

•Cuando una consulta utiliza operadores SET, Oracle Server elimina automáticamente las filas duplicadas excepto en el caso del operador UNION ALL. 

•Los nombres de columna de la salida los decide la lista de columnas de la primera sentencia SELECT. 

Ejemplo:

SELECT employee_id name, job_id trabajo

FROM employees

MINUS

SELECT employee_id nombre, job_id job

FROM job_history;

/*Note que el encabezado de la la primero columna dice NAME y el segundo TRABAJO.*/

---

Las expresiones correspondientes de las listas SELECT de las consultas componentes de una consulta compuesta deben corresponder en número y en tipo de datos. Si las consultas componentes seleccionan datos de carácter, el tipo de datos de los valores de retorno se determinan de este modo: 

•Si ambas consultas seleccionan valores el tipo de datos CHAR, los valores devueltos tienen el tipo de datos CHAR. 

•Si alguna de las dos o las dos consultas seleccionan valores del tipo de datos VARCHAR2, los valores devueltos tienen el tipo de datos VARCHAR2.

_____________________________________________________________________________________

Correspondencia de sentencias SELECTs en ambas Tablas.

Hay escenarios en lo cuales tienes que usar algún tipo de UNION con tablas que no tienen la misma cantidad de campos o los campos no son del mismo tipo. Como las expresiones de las listas SELECT de las consultas deben corresponder en número, puede utilizar columnas ficticias y las funciones de conversión de tipo de datos para cumplir esta regla.

Ejemplo:

SELECT employee_id ID, job_id job, salary, TO_DATE(NULL) "Correspon 1"
FROM employees
UNION
SELECT employee_id, job_id, 0, end_date
FROM job_history

ORDER BY ID DESC, job DESC;

/*Noten que la tabla employees no tiene el campo end_date y que job_history no tiene salary; si se da el caso que tengamos que mostrar un reporta en cual ambos sean necesarios esta seria una forma de mostrarlos.*/

---

SELECT department_id, TO_NUMBER(NULL)  

       location, hire_date 

FROM employees 

UNION

SELECT department_id, location_id,  TO_DATE(NULL) 

FROM departments; 

/*Este Query muestra el identificador de departamento, la ubicación y la fecha de contratación de todos los empleados. Noten que la tabla employees no tiene el campo location_id y que la tabla departments no tiene hire_date*/

_____________________________________________________________________________________

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1