JOIN, conjuntos y subconsultas

Un JOIN combina las filas de dos tablas emparejándolas por una condición que se escribe detrás de ON. Lo que distingue a cada tipo es qué hace con las filas que no encuentran pareja: unas se pierden y otras se conservan rellenando con nulos las columnas de la tabla que falta.

La palabra INNER es opcional: escribir JOIN a secas es exactamente lo mismo. También lo es OUTER en los tres externos, de modo que LEFT JOIN y LEFT OUTER JOIN son la misma sentencia. Y un CROSS JOIN equivale a nombrar las dos tablas separadas por coma en el FROM sin poner condición, que es la forma antigua de escribirlo.

El NATURAL JOIN conviene conocerlo para el examen y evitarlo en producción: como el emparejamiento lo decide el gestor mirando qué columnas comparten nombre, el día que alguien añade a las dos tablas una columna llamada «observaciones» la consulta cambia de significado sin que nadie la haya tocado.

La forma segura de fijar los tipos de JOIN es contar filas sobre un caso pequeño. Se parte de tres alumnos y tres intentos, con Ana teniendo dos intentos, Bruno uno y Carla ninguno.

alumno                      intento
id_alumno | nombre          id_intento | id_alumno
--------- | ------          ---------- | ---------
        1 | Ana                    900 |         1
        2 | Bruno                  901 |         1
        3 | Carla                  902 |         2

SELECT a.nombre, i.id_intento
FROM   alumno a
  ...JOIN intento i ON a.id_alumno = i.id_alumno;

Ese ejemplo desmonta un error frecuente: se suele decir que un LEFT JOIN devuelve tantas filas como tenga la tabla de la izquierda, y aquí la izquierda tiene tres alumnos y el resultado tiene cuatro filas. Solo se cumple cuando la relación es de uno a uno como mucho. Con una relación de uno a muchos, cada fila de la izquierda se repite tantas veces como parejas encuentre.

Las filas del FULL salen de sumar las del LEFT y las del RIGHT y restar una vez las del INNER, que están contadas dos veces: 4 más 3 menos 3 son 4.

Los operadores de conjuntos trabajan de otra manera que el JOIN. El JOIN pega tablas a lo ancho, añadiendo columnas; estos las apilan a lo alto, sumando o restando filas de dos consultas que devuelven la misma forma de resultado.

Por eso exigen compatibilidad: las dos consultas tienen que devolver el mismo número de columnas y tipos compatibles entre sí, columna a columna y en el mismo orden. Los nombres de las columnas no tienen que coincidir, y el resultado hereda los de la primera consulta.

-- Temas que ha tocado Ana o ha tocado Bruno, sin repetir
SELECT id_tema FROM intento WHERE id_alumno = 1
UNION
SELECT id_tema FROM intento WHERE id_alumno = 2;

-- Temas que han tocado los dos
SELECT id_tema FROM intento WHERE id_alumno = 1
INTERSECT
SELECT id_tema FROM intento WHERE id_alumno = 2;

-- Temas que existen y que Ana todavía no ha tocado
SELECT id_tema FROM tema
EXCEPT
SELECT id_tema FROM intento WHERE id_alumno = 1;

UNION elimina las filas duplicadas del resultado; UNION ALL no las elimina y las devuelve todas. Esa diferencia se pregunta mucho y tiene además consecuencia de rendimiento: para quitar duplicados el gestor tiene que ordenar o comparar todo el resultado, así que UNION ALL es más rápido y es el que hay que usar cuando se sabe que no puede haber repeticiones. EXCEPT se llama MINUS en Oracle.

Una subconsulta es un SELECT escrito dentro de otra sentencia. Lo habitual es verla en el WHERE, pero también puede aparecer en la lista de columnas del SELECT, en el FROM haciendo las veces de tabla, y en el USING de un MERGE. Se clasifican por lo que devuelven y por si dependen o no de la consulta que las envuelve.

• Escalar: devuelve un único valor, y por eso se puede comparar con un igual, un mayor o un menor.
• De lista o de fila múltiple: devuelve una columna con varias filas, y entonces la comparación tiene que ser con IN, con ANY, con SOME o con ALL.
• De tabla: devuelve varias filas y varias columnas, y se usa en el FROM como si fuera una tabla más.
• No correlacionada: se puede ejecutar sola, porque no menciona nada de la consulta externa. El gestor la resuelve una vez y reutiliza el resultado.
• Correlacionada: usa una columna de la consulta externa, así que no tiene sentido por separado y conceptualmente se reevalúa para cada fila de la externa.

-- Escalar: la interna devuelve un solo valor
SELECT i.id_intento, i.aciertos
FROM   intento i
WHERE  i.aciertos > (SELECT AVG(aciertos) FROM intento);

-- De lista: se compara con IN
SELECT nombre
FROM   alumno
WHERE  id_alumno IN (SELECT id_alumno FROM intento WHERE fallos > 20);

-- Correlacionada: la interna usa a.id_alumno, de la externa
SELECT a.nombre,
       (SELECT COUNT(*) FROM intento i
        WHERE  i.id_alumno = a.id_alumno) AS intentos
FROM   alumno a;

Si la subconsulta se puede ejecutar por separado y da un resultado con sentido, es no correlacionada. Si al aislarla se queda hablando de una columna que ya no existe, es correlacionada.

EXISTS no compara valores: solo pregunta si la subconsulta devuelve alguna fila. Por eso da igual qué se ponga en su SELECT interno, y es costumbre escribir un 1. Es la forma natural de expresar «tiene al menos uno» y su negación, NOT EXISTS, la de expresar «no tiene ninguno».

-- Alumnos que no han hecho ningún intento
SELECT a.nombre
FROM   alumno a
WHERE  NOT EXISTS (SELECT 1 FROM intento i
                   WHERE  i.id_alumno = a.id_alumno);

-- Temas con más peso que ALGUNO de los del bloque 1
SELECT titulo FROM tema
WHERE  peso > ANY (SELECT peso FROM tema WHERE id_bloque = 1);

-- Temas con más peso que TODOS los del bloque 1
SELECT titulo FROM tema
WHERE  peso > ALL (SELECT peso FROM tema WHERE id_bloque = 1);

ANY y SOME son sinónimos y se cumplen si la comparación es cierta para al menos un valor de la lista, así que un mayor que ANY equivale a ser mayor que el mínimo. ALL exige que la comparación sea cierta para todos, de modo que un mayor que ALL equivale a ser mayor que el máximo. Un igual a ANY es lo mismo que un IN.

Queda una trampa que conviene llevar sabida: si la subconsulta de un NOT IN devuelve algún nulo, la condición no se cumple nunca y la consulta no devuelve ninguna fila. La razón es que comparar cualquier cosa con un nulo no da falso, da desconocido, y NOT IN necesita que todas las comparaciones sean falsas. Con NOT EXISTS ese problema no aparece, y por eso se prefiere.