Viernes, 12 Junio 2020 03:37

Operadores SET

Rate this item
(0 votes)

Objetivos:

  • Describir operadoresSET.
  • 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_HISTORYRegistra 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 sentenciasSELECTutilizadas en la consulta. No es necesario que los nombres de las columnas sean idénticos.
  • UNIONopera sobre todas las columnas que se están seleccionando.
  • Los valoresNULLno se ignoran durante la comprobación de duplicados.
  • El operadorINtiene 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 UNION ALL son las mismas, con estas dos excepciones relacionadas con UNION ALL:

  • A diferencia de lo que sucede conUNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto.
  • No se puede utilizar la palabra claveDISTINCT.(Nota: esto solo aplica en versionesORACLE 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 SELECTdeben ser idénticos en todas las sentencias SELECTutilizadas 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. 
  • INTERSECTno 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 SELECTdeben ser idénticos en todas las sentencias SELECTutilizadas 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áusulaWHEREdeben 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 UNIONUNION ALLINTERSECT MINUS en su cláusula WHERE deben tener el mismo número y el mismo tipo de columnas de su lista SELECTPor 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áusulaORDER 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 operadoresSETse pueden utilizar en subconsultas.
  • Cuando una consulta utiliza operadoresSET,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 datosVARCHAR2, 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) AS 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

Read 864 times Last modified on Viernes, 03 Julio 2020 18:24

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.