MySQL Subconsulta con ejemplos

¿Qué es una subconsulta en SQL?

A subconsulta Una consulta SELECT interna se encuentra dentro de otra consulta. Generalmente, la consulta SELECT interna se utiliza para determinar los resultados de la consulta SELECT externa, por lo que la base de datos evalúa primero la consulta interna y luego transmite su resultado hacia arriba.

La consulta interna se llama consulta interna o consulta anidada, y la instrucción que la contiene se llama consulta externaAnalicemos la sintaxis de las subconsultas.

El diagrama anterior muestra la estructura general de la instrucción: la cláusula SELECT externa especifica las columnas que se desean visualizar, y la cláusula SELECT interna entre corchetes especifica el valor o la lista de valores con los que se compara la cláusula WHERE.

¿Por qué usar una subconsulta?

Antes de analizar los diferentes tipos, conviene saber cuándo una subconsulta tiene cabida en una instrucción.

Una subconsulta responde a una pregunta cuyo valor de filtro se desconoce de antemano. Este valor debe calcularse a partir de los datos. Una queja común de los clientes de la videoteca MyFlix es la escasa cantidad de títulos de películas, y la gerencia desea adquirir películas de la categoría con menor número de títulos. Nadie sabe cuál es esa categoría hasta que se consulta la base de datos, por lo que el valor debe calcularse primero y luego utilizarse como filtro.

Las subconsultas están entractivo por tres razones prácticas:

• Legibilidad: Cada parte de la lógica se encuentra en su propio bloque entre corchetes, por lo que la afirmación se lee como una secuencia de preguntas pequeñas en lugar de una expresión compleja.
• Aislamiento: Se puede ejecutar una consulta interna de forma independiente para confirmar que devuelve el valor esperado, lo que facilita enormemente las pruebas y la depuración.
• Flexibilidad: El mismo patrón funciona en las cláusulas WHERE, HAVING, SELECT y FROM, y también dentro de las sentencias INSERT, UPDATE y DELETE.

La desventaja es la velocidad, que se analiza en la comparación con JOIN más adelante en este artículo.

Tipos de subconsultas en MySQL

MySQL Admite tres tipos de subconsultas, cuyo tipo se determina según la estructura del resultado que devuelve la consulta interna. Cada tipo se explica a continuación con un ejemplo práctico en la base de datos myflixdb.

1) Subconsulta escalar

A subconsulta escalar Devuelve exactamente una fila y una columna, lo que significa que devuelve un único valor. Dado que el resultado es un único valor, se puede utilizar en cualquier lugar donde se permita un valor literal. Volviendo al problema de MyFlix mencionado anteriormente, puede utilizar una consulta como esta:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Da como resultado:

Veamos cómo funciona esta consulta.

Como muestra el diagrama de ejecución, MySQL primeras carreras SELECT MIN(category_id) FROM movies, recibe un valor y solo entonces ejecuta la consulta externa con ese valor. Debido a que se devuelve un único valor, los operadores permitidos son el conjunto de comparación estándar: =, <> (o !=), >, >=, <, y <=.

💡 Consejo: Si una subconsulta colocada después = devuelve más de una fila, MySQL genera el error 1242, La subconsulta devuelve más de 1 fila.. Cambie el operador a INo bien, ajustar la cláusula WHERE interna.

2) Subconsulta de fila

A subconsulta de fila También devuelve una sola fila, pero esa fila puede contener más de una columna. Por lo tanto, la consulta externa compara una fila de valores con un constructor de filas en lugar de con un solo valor.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Los operadores permitidos son los mismos operadores de comparación enumerados anteriormente, aplicados a toda la fila a la vez.

3) Subconsulta de tabla

A subconsulta de tabla devuelve varias filas y, a menudo, varias columnas, por lo que la consulta externa debe utilizar un operador de conjunto como IN, NOT IN, ANY, ALL, o EXISTS.

Supongamos que quieres los nombres y números de teléfono de los miembros que han alquilado una película y aún no la han devuelto, para poder llamarlos y recordarles que lo hagan. Puedes usar una consulta como esta:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Veamos cómo funciona esta consulta.

En este caso, la consulta interna devuelve más de un resultado, por lo que la lista de números de membresía se entrega a la IN Se devuelve el operador y cada miembro coincidente.

Anidamiento de subconsultas a varios niveles de profundidad

Hasta ahora has visto dos niveles. Una subconsulta también puede contener otra subconsulta, lo que produce una triple sentencia anidada.

Supongamos que la gerencia quiere recompensar al miembro que más paga. Podemos ejecutar una consulta como esta:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

La consulta más interna encuentra el pago más grande, la consulta intermedia convierte esa cantidad en un número de membresía y la consulta externa convierte el número de membresía en un nombre. La consulta anterior arroja el siguiente resultado:

Cómo usar subconsultas con INSERT, UPDATE y DELETE

Las subconsultas no se limitan a las sentencias SELECT. El mismo patrón entre corchetes funciona dentro de las sentencias de modificación de datos, lo que permite modificar un conjunto completo de filas en una sola pasada sin crear una tabla temporal.

INSERTAR con una subconsulta. Una subconsulta puede proporcionar las filas que se insertan, copiando datos de una tabla a otra. La lista de columnas de la sentencia SELECT debe coincidir con la lista de columnas de la sentencia INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

Actualizar con una subconsulta. Aquí, la consulta interna decide qué filas se modifican. El ejemplo siguiente marca a todos los miembros cuyo alquiler aún está pendiente de pago.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

ELIMINAR con una subconsulta. La misma idea elimina las filas que cumplen una condición almacenada en una segunda tabla.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Advertencia: MySQL No permite que una instrucción modifique una tabla y seleccione datos de la misma tabla dentro de una subconsulta en la cláusula FROM. Si aparece el error 1093, envuelva la consulta interna en una tabla derivada, por ejemplo SELECT * FROM (SELECT ...) AS t, De modo que MySQL materializa el resultado antes de que se aplique el cambio. También es prudente ejecutar primero el SELECT interno por sí solo y confirmar el recuento de filas, antes de ejecutar un ACTUALIZAR BORRAR en producción.

Subconsultas frente a uniones

Tanto una subconsulta como una operación JOIN pueden combinar información de más de una tabla, por lo que la pregunta lógica es cuál utilizar.

En comparación con las uniones, las subconsultas son sencillas de usar y fáciles de leer. No son tan complicadas como Uney por lo tanto son frecuentemente utilizados por Principiantes de SQL.

Sin embargo, las subconsultas presentan problemas de rendimiento. Utilizar una unión en lugar de una subconsulta puede, en ocasiones, mejorar el rendimiento hasta 500 veces, ya que el optimizador puede resolver la unión en una sola pasada en lugar de evaluar la instrucción interna repetidamente.

Si tiene la opción, se recomienda utilizar JOIN en lugar de una subconsulta. Las subconsultas solo deben utilizarse como solución alternativa cuando no se pueda utilizar una operación JOIN para lograr lo anterior.

Las subconsultas también son fáciles de descomponer en componentes lógicos individuales, lo cual es muy útil cuando las pruebas y depurar las consultas.

Preguntas Frecuentes