Subconsulta correlacionada en SQL: Una Guía para Principiantes

A veces, el uso de una subconsulta correlacionada con SQL es la única manera de resolver una sentencia. Pero estas subconsultas pueden ser muy lentas. En este post, hablaremos de cuándo usar una subconsulta correlacionada, por qué y cómo hacerlo.

Las sub consultas son un recurso importante para aumentar el poder expresivo de SQL. Si no has leído nuestro artículo anterior, las subconsultas son simplemente una sentencia SELECT dentro de otra SELECT. Podemos usarlas en diferentes lugares dentro de una SELECT, como en las cláusulas WHERE, HAVING, o FROM. Incluso podemos utilizarlas en otras sentencias como UPDATE o DELETE.

Al igual que las subconsultas simples, una subconsulta correlacionada SQL contiene una consulta dentro de otra consulta. Recibe su nombre porque las dos consultas están relacionadas; la consulta interna utiliza información obtenida de la consulta externa (por ejemplo, de una tabla referenciada en la consulta externa). Para los programadores, esto es similar a una estructura de bucle anidado.

Antes de pasar a un ejemplo de subconsulta correlacionada en SQL, vamos a hacer un rápido repaso de las subconsultas simples (no correlacionadas).

Una subconsulta simple

El siguiente ejemplo de SQL utiliza una subconsulta simple para obtener una lista de empleados que ganan más que el salario medio de la empresa. En este caso, la subconsulta se muestra en negrita y su resultado es el salario medio de la empresa. La consulta externa compara el salario de cada empleado con la media de la empresa. Es muy fácil.

SELECT 
  lastname, 
  firstname, 
  salary 
FROM employee 
WHERE salary > (SELECT avg(salary) 
                FROM employee)

Ejemplo 1: Una simple subconsulta SQL no correlacionada

A continuación están las subconsultas correlacionadas. Estas son muy especiales, porque a veces son la única manera de resolver una consulta. Sin embargo, piénselo dos veces antes de utilizar una subconsulta correlacionada en SQL. Pueden ser lentas, como explicaremos más adelante.

Uso de una subconsulta correlacionada en SQL

Comencemos con un ejemplo de subconsulta correlacionada en SQL. Supongamos que queremos encontrar a todos los empleados con un salario superior al promedio de su departamento. Utilizaríamos la siguiente consulta. Una vez más, he puesto en negrita la subconsulta:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE e1.salary > (SELECT avg(salary)
                   FROM employee e2 
                   WHERE e2.dept_id = e1.dept_id)

Ejemplo 2: Una subconsulta correlacionada en SQL

La principal diferencia entre una subconsulta correlacionada en SQL y una subconsulta simple es que las subconsultas correlacionadas hacen referencia a columnas de la tabla externa. En el ejemplo anterior, e1.dept_id es una referencia a la tabla de la subconsulta externa. Para identificar una consulta correlacionada, basta con buscar este tipo de referencias. Si encuentra al menos una, tiene una subconsulta correlacionada SQL.

Veamos otro ejemplo. Supongamos que queremos obtener los nombres de los departamentos que tienen más de 10 empleados. Podemos utilizar la siguiente subconsulta correlacionada SQL:

SELECT deptname
FROM department d1
WHERE 10 < (SELECT count(*)
            FROM employee e
            WHERE e.dept_id = d1.dept_id)

Ejemplo 3: Otra subconsulta correlacionada en SQL

Es hora de un último ejemplo. Hemos mencionado que las subconsultas pueden formar parte de las cláusulas WHERE, FROM, HAVING y SELECT. En este ejemplo, utilizaremos una subconsulta correlacionada en SQL en la lista SELECT para encontrar el nombre de cada empleado, su salario y el salario medio de su departamento. Obtendremos el salario medio utilizando una subconsulta correlacionada dentro de SELECT.

Este es el código:

SELECT 	
  lastname,
  firstname, 
  salary,
  (SELECT avg(salary) 
    FROM employee e2
    WHERE e2.dep_id = e1.dep_id) AS avg_dept_salary
FROM employee e1

Ejemplo 4: Una subconsulta SQL correlacionada en la lista SELECT

¿Cuántas veces se ejecuta una subconsulta correlacionada SQL?

Supongamos que tenemos una tabla llamada "assigned_to_project"que almacena los nombres de los empleados asignados a los proyectos. Queremos encontrar todos los empleados que no están asignados a ningún proyecto. La solución es la siguiente consulta:

SELECT 
  lastname, 
  firstname, 
  salary
FROM employee e1
WHERE NOT EXISTS (SELECT project_id
                  FROM assigned_to_project 
                  WHERE employee_id = e1.employee_id)

Ejemplo 5: Una subconsulta correlacionada en SQL

En primer lugar, esta consulta es fácil de analizar. La cláusula NOT EXISTS es TRUE cuando la subconsulta devuelve un conjunto de resultados vacío. Esto ocurre sólo para los empleados que no están asignados a ningún proyecto. De nuevo, ¡es bastante fácil!

Sin embargo, el propósito de esta sección es analizar cuántas veces se ejecuta la subconsulta correlacionada con SQL. Intente averiguar cuál de las siguientes afirmaciones es la correcta:

1. Se ejecuta una sola vez.
2. Se ejecuta una vez por cada proyecto.
3. Se ejecuta una vez por cada empleado no asignado a ningún proyecto.
4. Se ejecuta una vez por cada empleado de la empresa.

Analicemos estas opciones. Supongamos que tenemos 1.000 empleados y 20 proyectos. Además, tenemos 800 empleados que ya están asignados a un proyecto. En este caso, la primera opción tiene una ejecución, la segunda tiene 20 ejecuciones, la tercera tiene 200 y la última opción tiene 1.000.

Si has dicho que la respuesta correcta es "una vez por cada empleado de la empresa", estás en lo cierto. Si repasamos el enunciado, esta respuesta resulta obvia; hay que comprobar si cada empleado tiene proyectos o no. Sin embargo, según el ejemplo anterior, esto significa que la subconsulta se ejecutará 1.000 veces. En términos de rendimiento, este es el peor escenario de los cuatro.

Como las subconsultas correlacionadas suelen implicar muchas ejecuciones, también suelen ser lentas. Como regla, debido a esto, siempre tratamos de evitar el uso de una subconsulta correlacionada en SQL. Pero como ya hemos mencionado, a veces la correlación es la única manera de resolver una consulta.

Cuándo utilizar una subconsulta correlacionada en SQL

En este punto del artículo el lector debe estar pensando: ¿Cuándo utilizar una subconsulta correlacionada en SQL? En realidad, hay algunos casos en los que debemos hacerlo. Esto es especialmente cierto en las consultas en las que buscamos lo que podríamos llamar negativos.

He aquí un ejemplo de consulta "negativa". Supongamos que tenemos una tabla de historial de pagos con una columna llamada payment_type que indica si un pago es un salario regular, una bonificación o un premio. Si queremos una consulta que nos devuelva los empleados que nunca recibieron un premio, utilizaríamos esta consulta:

SELECT 
  lastname, 
  firstname
FROM employees e1
WHERE NOT EXISTS (SELECT ph.lastname 
                  FROM payment_history ph 
                  WHERE ph.emp_id = e1.employee_id 
                  AND ph.payment_type =’award’)

Ejemplo 6: Una subconsulta correlacionada con SQL que utiliza NOT EXISTS

EXISTS es un operador unario. Sólo tiene un operando, que es una subconsulta (correlacionada o no). Si la subconsulta devuelve al menos un registro, entonces EXISTS devuelve TRUE. Si la subconsulta no devuelve ningún registro, EXISTS devuelve FALSE. En este caso, debe utilizar una subconsulta correlacionada para obtener los resultados.

Uso de subconsultas correlacionadas en la sentencia UPDATE o DELETE

A veces encontramos subconsultas correlacionadas en sentencias UPDATE o DELETE. El siguiente UPDATE tiene una subconsulta correlacionada SQL que obtiene el nuevo valor de la columna all_money_made:

UPDATE employee emp
SET all_money_made = (SELECT SUM(payment)
                      FROM payment_history 
                      WHERE employee_id = emp.emp_id)

Ejemplo 7: Una subconsulta SQL correlacionada en un UPDATE

Este artículo nos ha mostrado cuándo utilizar una subconsulta correlacionada en SQL. Las consultas que buscan negativos son buenas candidatas, aunque hay otras ocasiones en las que una correlación es la única opción real. También hemos visto cuántas veces se ejecuta una subconsulta correlacionada, normalmente muchas, muchas veces. Este es su mayor inconveniente.

Pruébelo usted mismo

Las sub consultas correlacionadas son un recurso importante para el desarrollador de SQL. Para aprender más y mejorar sus habilidades de subconsulta, pruebe el curso LearnSQL.es's SQL para principiantes. Tenemos una sección específica para las subconsultas, además de muchos ejercicios y ejemplos.