Operaciones de conjuntos — UNION / INTERSECT / EXCEPT

Datos usados en este artículo — employee y contractor

Las operaciones de conjuntos (operaciones que combinan varios resultados de SELECT en uno como la unión, intersección o diferencia de conjuntos) son la sintaxis para apilar verticalmente resultados obtenidos por separado, o extraer las filas comunes a ambos o las filas que aparecen en un solo lado.

Trabajarás con cuatro de ellas: UNION (unión), UNION ALL (unión que conserva duplicados), INTERSECT (intersección) y EXCEPT (diferencia).

El material es la tabla de empleados employee (30 filas) y la tabla de contratistas contractor (6 filas).

Probarás las cuatro operaciones de conjuntos una por una — uniendo dos resultados con condiciones diferentes dentro de employee, o combinando en uno los resultados extraídos de tablas distintas como employee y contractor.

Antes de meterte en los ejercicios, echa un vistazo a las definiciones de columnas y una muestra de los datos de las dos tablas que usa este artículo — employee y contractor.

① Ejecuta PRAGMA table_info(employee); y PRAGMA table_info(contractor); para comprobar las definiciones de columnas de ambas tablas.

② Ejecuta SELECT * FROM employee LIMIT 5; y SELECT * FROM contractor LIMIT 5; para previsualizar las primeras 5 filas de datos.

La premisa de las operaciones de conjuntos — la compatibilidad UNION

Las cuatro operaciones de conjuntos unen dos o más resultados de SELECT en la forma SELECT ... operador SELECT ....

La premisa para unirlos es que el SELECT superior y el inferior sean compatibles UNION (igual número de columnas, y los tipos de las columnas correspondientes son compatibles).

SELECT name FROM employee y SELECT name FROM contractor son ambos una sola columna de cadena, así que son compatibles.

Por otro lado, SELECT name FROM employee y SELECT name, city FROM contractor tienen un número de columnas diferente, así que no puedes unirlos.

-- compatible UNION: ambos lados son 2 columnas (name, city)
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY va una sola vez al final del todo
-- no puedes escribir un ORDER BY por SELECT en medio

UNION y UNION ALL — eliminar duplicados o conservarlos

UNION apila dos resultados verticalmente y fusiona en una las filas completamente idénticas (eliminación de duplicados).

UNION ALL no elimina duplicados y conserva tal cual todas las filas de ambos lados.

Cuando no necesitas eliminar duplicados, o cuando quieres conservar "cuántas filas aparecieron en ambos", usa UNION ALL.

UNION ejecuta una ordenación interna para detectar duplicados, así que UNION ALL es la operación más ligera.

El ejemplo de abajo une el resultado "vive en Kyoto" y el resultado "salario 7 millones o más" dentro de employee.

Un empleado que satisface ambas condiciones se vuelve 1 fila con UNION, y 2 filas con UNION ALL.

-- UNION: las filas duplicadas se fusionan en una
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL: los duplicados se conservan (las filas que coinciden con ambas aparecen dos veces)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

Imagina el requisito: "quiero combinar a los empleados que viven en Kyoto y a los empleados con un salario alto en una sola lista. Quien encaje en ambos debe aparecer solo una vez". (Ejecútalo correctamente y aparecerá la explicación.)

① Escribe un SELECT que extraiga el name de employee donde city es Kyoto.

② Únelo con un SELECT que extraiga el name de las filas donde salary es 7,000,000 o más, usando un operador de conjunto que elimina duplicados.

③ Ordena el resultado por name ascendente (para garantizar el orden de las filas).

Imagina el requisito: "quiero apilar los resultados de las mismas dos condiciones que la Práctica 1 sin eliminar duplicados — todos — para poder confirmar visualmente qué nombres aparecieron en ambos".

① Une los dos mismos SELECT que la Práctica 1 (el name para vive en Kyoto / salario 7,000,000 o más) usando un operador de conjunto que conserva duplicados.

② Ordena el resultado por name ascendente. Confirma que un nombre que aparece en ambos se muestra como dos filas consecutivas.

INTERSECT y EXCEPT — filas comunes y filas de diferencia

• INTERSECT (intersección) devuelve solo las filas que aparecen en común en ambos resultados.
• EXCEPT (diferencia) devuelve las filas que están en el resultado superior pero no en el resultado inferior.

Ambas eliminan duplicados automáticamente (el mismo comportamiento que UNION).

EXCEPT es una operación asimétrica cuyo resultado cambia si intercambias el lado superior y el inferior — A EXCEPT B y B EXCEPT A son cosas distintas.

El ejemplo de abajo une el name para "vive en Kyoto" y "salario 6.5 millones o más" dentro de employee con INTERSECT, extrayendo los empleados que satisfacen ambas (las filas comunes).

-- INTERSECT: empleados que viven en Osaka Y con salario 6000000 o más
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT: empleados que viven en Osaka pero NO con salario 6000000 o más
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

Resuelve el requisito "quiero extraer solo a los empleados que viven en Kyoto Y con un salario de 6,500,000 o más" usando operaciones de conjuntos.

① Escribe un SELECT que extraiga el name de employee donde city es Kyoto.

② Únelo con un SELECT que extraiga el name de las filas donde salary es 6,500,000 o más, usando un operador de conjunto que devuelve solo las filas comunes.

③ Ordena el resultado por name ascendente.

Imagina el requisito: "de los empleados que viven en Kyoto, quiero extraer solo a los que NO están en salario 6,500,000 o más (la gente de Kyoto con salario más bajo)".

① Escribe un SELECT que extraiga el name de employee donde city es Kyoto.

② Únelo con un SELECT que extraiga el name de las filas donde salary es 6,500,000 o más, usando un operador de conjunto que devuelve las filas del lado superior pero no del lado inferior (cuidado — el orden del lado superior e inferior afecta al resultado).

③ Ordena el resultado por name ascendente.

Operaciones de conjuntos entre tablas distintas — employee y contractor

Las operaciones de conjuntos funcionan no solo dentro de una sola tabla sino también entre resultados extraídos de tablas separadas.

Mientras sean compatibles UNION (coincidiendo número de columnas y tipo), las tablas pueden ser distintas.

Cuando quieres ordenar el resultado unido, escribe un único ORDER BY después del último SELECT (se aplica a todo el resultado unido).

Unir el name de la tabla de empleados employee y la tabla de contratistas contractor con INTERSECT te permite sacar a la luz a personas que aparecen en ambas con el mismo nombre.

El ejemplo de abajo extrae solo la única columna name y las une con INTERSECT.

Como el name de ambas tablas tiene Alice y Bob, se devuelven 2 personas como filas comunes.

-- nombres que aparecen tanto en employee como en contractor
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- nombres en contractor pero no en employee (solo externos)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

Imagina el requisito: "quiero listar a las personas de la tabla de contratistas contractor cuyo mismo nombre NO existe en la tabla de empleados employee (nombres puramente externos)".

① Escribe un SELECT que extraiga el name de contractor.

② Únelo con un SELECT que extraiga el name de employee, usando un operador de conjunto que devuelve las filas del lado superior pero no del lado inferior. Coloca el SELECT de contractor arriba.

③ Ordena el resultado por name ascendente.