Sean bienvenidos a esta secci贸n donde se revisa el uso de los operdores de definicion, recuerden estas sentencias estan siendo ejecutadas en SQL developer administrador de la Base de datos Oracle.
Objetivos de la publicaci贸n:
Al finalizar esta lecci贸n, deber铆a estar capacitado para lo siguiente:En esta lecci贸n aprender谩 a escribir consultas utilizando los operadores de definici贸n.
- Describir los operadores de definici贸n
- Utilizar un operador de definici贸n para combinar varias consultas en una sola
- Controlar del orden de las filas devueltas
Operadores de Definici贸n: tipos e instrucciones
Operadores de Definici贸n
Los operadores de definici贸n combinan el resultado de dos o m谩s consultas de componente en un resultado. Las consultas que contienen operadores de definici贸n se denominan consultas compuestas.
Todos los operadores de definici贸n tienen la misma prioridad. Si una sentencia SQL contiene varios operadores de definici贸n, el servidor de Oracle los eval煤a de izquierda (parte superior) a derecha (parte inferior), si no hay ning煤n par茅ntesis que especifique expl铆citamente otro orden. Debe utilizar los par茅ntesis para especificar el orden de evaluaci贸n expl铆citamente en las consultas que utilizan el operador INTERSECT en otros operadores de definici贸n.
Instrucciones de los Operadores de Definici贸n
- La expresiones de las listas SELECT debe coincidir en n煤mero.
- Los tipos de dato para cada columna de la segunda consulta deben coincidir con los tipos de dato de su columna correspondiente en la primera consulta.
- Los par茅ntesis se pueden utilizar para modificar la secuencia de ejecuci贸n.
- La sentencia ORDER BY puede aparecer s贸lo una vez al final de la sentencia.
La expresiones de las listas SELECT deben coincidir en n煤mero y tipo de dato. Las consultas que utilizan los operadores UNION, UNION ALL, INTERSECT y MINUS en su cl谩usula WHERE deben tener el mismo n煤mero y tipo de dato de columnas en su lista SELECT. El tipo de dato de las columnas de la lista SELECT de las consultas de la consulta compuesta puede no ser exactamente el mismo. La columna de la segunda consulta debe estar en el mismo grupo de tipo de dato (por ejemplo, num茅rico o de caracteres) que la columna correspondiente de la primera consulta.
Los operadores de definici贸n se pueden utilizar en subconsultas.
Debe utilizar los par茅ntesis para especificar el orden de evaluaci贸n en las consultas que utilizan el operador INTERSECT en otros operadores de definici贸n. Esto garantiza el cumplimiento de los emergentes est谩ndares SQL que otorgar谩n al operador INTERSECT mayor prioridad que los operadores de definici贸n.
Servidor de Oracle y Operadores de Definici贸n
- Las filas duplicadas se eliminan autom谩ticamente excepto en UNION ALL.
- Los nombres de columna de la primera consulta aparecen en el resultado.
- Por defecto, la salida se ordena en orden ascendente, excepto en UNION ALL.
Cuando una consulta utiliza operadores de definici贸n, el servidor de Oracle elimina autom谩ticamente las filas duplicadas, excepto en el caso del operador UNION ALL. Los nombres de columna de la salida est谩n determinados por la lista de columnas de la primera sentencia SELECT. Por defecto, la salida se ordena en orden ascendente seg煤n la primera columna de la cl谩usula SELECT.
Las expresiones correspondientes en las listas SELECT de las consultas de componente de una consulta compuesta deben coincidir en n煤mero y tipo de dato. Si las consultas de componente seleccionan datos de caracteres, el tipo de dato del valor de retorno se determina de la siguiente forma:
Si ambas consultas seleccionan valores del tipo de dato CHAR, de la misma longitud, los valores devueltos tienen el mismo tipo de dato CHAR con esa longitud. Si las consultas seleccionan valores del tipo CHAR con diferentes longitudes, el valor devuelto es VARCHAR2 con la longitud del valor CHAR m谩s largo.
Si una o ambas consultas seleccionan valores del tipo de dato VARCHAR2, los valores devueltos tienen un tipo de dato VARCHAR2.
Si las consultas de componente seleccionan datos num茅ricos, el tipo de dato del valor de retorno se determina seg煤n la prioridad num茅rica. Si todas las consultas seleccionan valores del tipo NUMBER, los valores devueltos tienen el tipo de dato NUMBER. En las consultas que utilizan operadores de definici贸n, el servidor de Oracle no realiza una conversi贸n expl铆cita a trav茅s de los grupos de tipos de dato. Por lo tanto, si las expresiones correspondientes de las consultas de componente se resuelven tanto en datos num茅ricos como de caracteres, el servidor de Oracle devuelve un error.
Tablas utilizadas en esta lecci贸n
Las tablas utilizadas en esta lecci贸n son:
EMPLOYEES: proporciona los detalles de todos los empleados actuales.
JOB_HISTORY: cuando un empleado cambia de cargo, registra los detalles de la fecha de inicio y de finalizaci贸n del cargo anterior, el n煤mero de identificaci贸n del cargo y el departamento.
En esta lecci贸n se utilizan dos tablas: la tabla EMPLOYEES y la tabla JOB_HISTORY.
Ya est谩 familiarizado con la tabla EMPLOYEES que almacena detalles sobre los empleados como un n煤mero 煤nico de identificaci贸n, la direcci贸n de correo electr贸nico, el ID de cargo (por ejemplo, ST_CLERK, SA_REP, etc.), el salario, el gestor, etc.
Algunos de los empleados llevan mucho tiempo en la compa帽铆a y han cambiado varias veces de puesto. Esto se supervisa con la tabla JOB_HISTORY. Cuando un empleado cambia de cargo, los detalles de fecha de inicio y finalizaci贸n del cargo anterior, el job_id (por ejemplo, ST_CLERK, SA_REP, etc.) y el departamento se registran en la tabla JOB_HISTORY.
La estructura y los datos de las tablas EMPLOYEES y JOB_HISTORY se muestran en las siguientes p谩ginas.
Existen casos en los que la misma persona ha ocupado el mismo puesto m谩s de una vez durante el tiempo que ha permanecido en la compa帽铆a. Por ejemplo, el empleado Taylor, que empez贸 a trabajar en la empresa el 24 de marzo de 1998. Taylor ocup贸 el puesto de SA_REP desde el 24 de marzo de 1998 al 31de diciembre de 1998 y el puesto de SA_MAN desde el 1 de enero de 1999 al 31 de diciembre de 1999. Taylor volvi贸 a ocupar el puesto de SA_REP, que es su puesto actual.
DESCRIBE employees;
SELECT employee_id, last_name, job_id, hire_date, department_id
FROM employees;
SELECT * FROM job_history;
Operador UNION y UNION ALL
Operador UNION
El operador UNION devuelve todas las filas seleccionadas en cualquier consulta. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas.
Instrucciones
El n煤mero de columnas seleccionadas debe ser el mismo.
Los tipos de dato de las columnas seleccionadas deben pertenecer al mismo grupo de tipo de dato (por ejemplo, num茅rico o de caracteres).
No es necesario que los nombres de las columnas sean id茅nticos.
El operador UNION funciona en todas las columnas seleccionadas.
Los valores NULL no se ignoran durante la comprobaci贸n de duplicados.
Por defecto, la salida se ordena en orden ascendente seg煤n de las columnas de la cl谩usula SELECT.
Uso del Operador UNION
Mostrar los detalles actuales y anteriores del puesto de todos los empleados. Mostrar cada empleado s贸lo una vez.
El operador UNION elimina cualquier registro duplicado. Si los registros de las tablas EMPLOYEES y JOB_HISTORY son id茅nticos, 茅stos s贸lo aparecer谩n una vez. Observe en la salida del ejemplo que el registro del empleado con un EMPLOYEE_ID de 200 aparece dos veces porque JOB_ID es diferente en cada fila.
Considere el siguiente ejemplo:
SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;
En la salida anterior, el empleado 200 aparece tres veces. ¿Por qu茅? Observe los valores de DEPARTMENT_ID del empleado 200. Una fila tiene un DEPARTMENT_ID de 90, otra 10 y la tercera, 90. Debido a estas combinaciones 煤nicas de ID de cargos y de departamentos, cada fila del empleado 200 es 煤nica y, por lo tanto, no se considera un duplicado. Observe que la salida est谩 ordenada en orden ascendente seg煤n la primera columna de la cl谩usula SELECT (en este caso, EMPLOYEE_ID).
Operador UNION ALL
Utilice el operador UNION ALL para devolver todas las filas de varias consultas.
Instrucciones
Las instrucciones para UNION y UNION ALL son las mismas, excepto en los dos siguientes casos que pertenecen a UNION ALL: a diferencia de UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto.
Uso del Operador UNION ALL
Mostrar los departamentos actuales y anteriores de todos los empleados.
En el ejemplo se seleccionan 30 filas. La combinaci贸n de las dos tablas tiene un total de 30 filas. El operador UNION ALL no elimina las filas duplicadas. UNION devuelve todas las filas distintas seleccionadas por cualquier consulta. UNION ALL devuelve todas las filas seleccionadas por cualquier consulta, incluyendo las duplicadas. Considere la consulta con la cl谩usula UNION:
SELECT employee_id, job_id,department_id
FROM employees
UNION SELECT employee_id, job_id,department_id
FROM job_history
ORDER BY employee_id;
la consulta anterior devuelve 29 filas. Esto se debe a que elimina la siguiente fila (porque est谩 duplicada).
Operador INTERSECT
Operador INTERSECT
Utilice el operador INTERSECT para devolver todas las filas comunes a varias consultas.
Instrucciones
- El n煤mero de columnas y los tipos de dato de las columnas seleccionadas por las sentencias SELECT en las consultas deben ser id茅nticos en todas las sentencias SELECT utilizadas en la consulta. No es necesario, sin embargo, que los nombres de las columnas sean id茅nticos.
- Si se invierte el orden de las tablas intersectadas no se alterar谩 el resultado.
- INTERSECT no ignora los valores NULL.
Uso del Operador INTERSECT
Mostrar los ID de empleado y de cargo de los empleados que actualmente tienen el mismo puesto que anteriormente (es decir, han cambiado de cargo pero ahora han vuelto a realizar el mismo trabajo que realizaban anteriormente).
En el ejemplo, la consulta devuelve s贸lo los registros que tienen los mismos valores en las columnas seleccionadas en ambas tablas.
¿Cu谩l ser谩 el resultado si agrega la columna DEPARTMENT_ID a la sentencia SELECT de la tabla EMPLOYEES y la columna DEPARTMENT_ID a la sentencia SELECT de la tabla JOB_HISTORY y ejecuta esta consulta? El resultado puede variar debido a la introducci贸n de otra columna cuyos valores pueden o no estar duplicados.
Ejemplo:
SELECT employee_id, job_id, department_id
FROM employees
INTERSECT
SELECT employee_id, job_id, department_id
FROM job_history;
El empleado 200 ya no forma parte de los resultados porque el valor EMPLOYEES.DEPARTMENT_ID es diferente del valor JOB_HISTORY.DEPARTMENT_ID.
Operador MINUS
Utilice el operador MINUS para devolver todas las filas distintas seleccionadas por la primero consulta, pero que no est谩n presentes en el juego de resultados de la segunda consulta (la primera sentencia SELECT MINUS la segunda sentencia SELECT).
Nota: el n煤mero de columnas y los tipos de dato de las columnas seleccionadas por las sentencias SELECT de las consultas deben pertenecer al mismo grupo de tipo de dato en todas las sentencias SELECT utilizadas en la consulta. No es necesario, sin embargo, que los nombres de las columnas sean id茅nticos.
Uso del Operador MINUS
Mostrar los identificadores de empleado cuyos empleados no han cambiado sus puestos ni una vez.
En el ejemplo, los ID de empleado de la tabla JOB_HISTORY se restan de los de la tabla EMPLOYEES. El juego de resultados muestra los empleados resultantes despu茅s de la resta; est谩n representados por filas que existen en la tabla EMPLOYEES pero que no existen en la tabla JOB_HISTORY. 脡stos son los registros de los empleados que no han cambiado sus puestos ni una vez.
Coincidencia de las sentencias SELECT
- Con el operador UNION, se muestra el ID de ubicaci贸n, el nombre de departamento y el estado en el que est谩 ubicado.
- Debe hacer que coincida el tipo de dato (mediante la funci贸n TO_CHAR o cualquier otra funci贸n de conversi贸n) cuando las columnas no existan en una tabla o en la otra.
Debido a que las expresiones de las listas SELECT de las consultas deben coincidir en n煤mero, puede utilizar columnas ficticias y funciones de conversi贸n de tipos de dato para cumplir con esta regla. En la imagen, se asigna el nombre Warehouse location como la cabecera de columna ficticia. La funci贸n TO_CHAR se utiliza en la primera consulta para que coincida el tipo de dato VARCHAR2 de la columna state_province que recupera la segunda consulta. Igualmente, la funci贸n TO_CHAR se utiliza en la segunda consulta para que coincida el tipo de dato VARCHAR2 de la columna department_name que recupera la primera consulta.
La salida de la consulta se muestra a continuaci贸n:
Coincidencia de las Sentencias SELECT: Ejemplo
Utilizar el operador UNION, mostrar el ID de empleado, ID de cargo y salario de todos los empleados.
Las tablas EMPLOYEES y JOB_HISTORY tienen varias columnas en com煤n (por ejemplo, EMPLOYEE_ID, JOB_ID y DEPARTMENT_ID). Pero, ¿y si lo que desea es que la consulta muestre el ID de empleado, ID de cargo y salario con el operador UNION sabiendo que el salario s贸lo existe en la tabla EMPLOYEES?
El c贸digo de ejemplo coinciden las columnas EMPLOYEE_ID y JOB_ID de las tablas EMPLOYEES y JOB_HISTORY. Se agrega el valor literal 0 a la sentencia JOB_HISTORY SELECT para que coincida con columna num茅rica SALARY de la sentencia EMPLOYEES SELECT.
En los resultados mostrados, cada fila de la salida que corresponde a un registro de la tabla JOB_HISTORY contiene un 0 en la columna SALARY.
Uso de la cl谩usula ORDER BY en operaciones de definici贸n
- La cl谩usula ORDER BY s贸lo puede aparecer una vez al final de la consulta compuesta.
- Las consultas de componente no pueden tener cl谩usulas ORDER BY individuales.
- La cl谩usula ORDER BY reconoce s贸lo las columnas de la primera consulta SELECT.
- Por defecto, la primera columna de la primera consulta SELECT se utiliza para ordenar la salida en orden ascendente.
La cl谩usula ORDER BY s贸lo se puede utilizar una vez en una consulta compuesta. Si se utiliza, la cl谩usula ORDER BY se debe colocar al final de la consulta. La cl谩usula ORDER BY acepta el nombre de columna o alias. Por defecto, la salida se ordena en orden ascendente seg煤n la primera columna de la cl谩usula SELECT.
Nota: la cl谩usula ORDER BY no reconoce los nombres de columna de la segunda consulta SELECT. Para evitar la confusi贸n con los nombres de columna, es una pr谩ctica habitual utilizar cl谩usula ORDER BY seg煤n las posiciones de las columnas.
Por ejemplo, en la siguiente sentencia, la salida se mostrar谩 en orden ascendente seg煤n job_id.
SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history
ORDER BY 2;
Si omite ORDER BY, por defecto, la salida se ordenar谩 en orden ascendente seg煤n employee_id. No puede utilizar las columnas de la segunda consulta para ordenar la salida.
Resumen
En esta lecci贸n, debe haber aprendido a utilizar:
- UNION para devolver todas las filas distintas
- UNION ALL para devolver todas las filas, incluyendo los duplicados
- INTERSECT para devolver todas las filas que comparten ambas consultas
- MINUS para devolver todas las filas distintas seleccionadas por la primera consulta, pero no por la segunda
- ORDER BY s贸lo al final de la sentencia
El operador UNION devuelve todas las filas distintas seleccionadas por cada consulta de la consulta compuesta. Utilice el operador UNION para devolver todas las filas de varias tablas y eliminar las filas duplicadas.
Utilice el operador UNION ALL para devolver todas las filas de varias consultas. A diferencia del operador UNION, las filas duplicadas no se eliminan y la salida no se ordena por defecto.
Utilice el operador INTERSECT para devolver todas las filas comunes a varias consultas.
Utilice el operador MINUS para devolver filas en la primera consulta que no est谩n en la segunda.
Recuerde utilizar la cl谩usula ORDER BY s贸lo al final de la sentencia compuesta.
Aseg煤rese de que las expresiones correspondientes de las listas SELECT coinciden en n煤mero y tipo de dato.
