Cómo usar consultas anidadas en SQL

Introducción

El Lenguaje de Consulta Estructurado (SQL) se utiliza para gestionar datos en un sistema de gestión de bases de datos relacionales (RDBMS). Una función útil en SQL es crear una consulta dentro de otra, también conocida como una subconsulta o consulta anidada. Una consulta anidada es una instrucción SELECT que típicamente está encerrada entre paréntesis y se inserta dentro de una operación principal de SELECT, INSERT o DELETE.

En este tutorial, utilizarás consultas anidadas con las instrucciones SELECT, INSERT y DELETE. También utilizarás funciones de agregado dentro de una consulta anidada para comparar los valores de datos con los valores de datos ordenados que especificaste con las cláusulas WHERE y LIKE.

Requisitos previos

Para seguir esta guía, necesitarás una computadora que ejecute algún tipo de sistema de gestión de bases de datos relacionales (RDBMS) que utilice SQL. Las instrucciones y ejemplos en este tutorial fueron validados utilizando el siguiente entorno:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL instalado y asegurado en el servidor. Siga nuestra guía Cómo instalar MySQL en Ubuntu 20.04 para configurarlo. Esta guía asume que también ha configurado un usuario MySQL no root, como se describe en el Paso 3 de esta guía.

Para practicar el uso de consultas anidadas en este tutorial, necesitará una base de datos y una tabla cargadas con datos de muestra. Si no tiene una lista para insertar, puede leer la siguiente sección Conexión a MySQL y configuración de una base de datos de muestra para aprender cómo crear una base de datos y una tabla. Este tutorial hará referencia a esta base de datos de muestra y tabla en todo momento.

Conexión a MySQL y configuración de una base de datos de muestra

Si su base de datos SQL se ejecuta en un servidor remoto, conéctese por SSH a su servidor desde su máquina local:

ssh sammy@your_server_ip

A continuación, abra el símbolo del sistema de MySQL, reemplazando sammy con la información de su cuenta de usuario de MySQL:

mysql -u sammy -p

Cree una base de datos llamada zooDB:

CREATE DATABASE zooDB;

Si la base de datos se creó correctamente, recibirás la siguiente salida:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos zooDB, ejecuta la siguiente instrucción USE:

USE zooDB;

Output
Database changed

Después de seleccionar la base de datos, crea una tabla dentro de ella. Para el ejemplo de este tutorial, crearemos una tabla que almacene información sobre los visitantes del zoológico. Esta tabla contendrá las siguientes siete columnas:

• guest_id: almacena valores para los visitantes del zoológico, y utiliza el tipo de datos int. Esto también sirve como la clave primaria de la tabla, lo que significa que cada valor en esta columna funcionará como un identificador único para su respectiva fila.
• first_name: guarda el nombre de pila de cada visitante utilizando el tipo de datos varchar con un máximo de 30 caracteres.
• last_name: utiliza el tipo de datos varchar, nuevamente con un máximo de 30 caracteres, para almacenar el apellido de cada visitante.
• guest_type: contiene el tipo de visitante (adulto o niño) para cada visitante utilizando el tipo de datos varchar con un máximo de 15 caracteres.
• membership_type: representa el tipo de membresía que cada visitante posee, utilizando el tipo de datos varchar para almacenar un máximo de 30 caracteres.
• membership_cost: almacena el costo para varios tipos de membresía. Esta columna utiliza el tipo de datos decimal con una precisión de cinco y una escala de dos, lo que significa que los valores en esta columna pueden tener cinco dígitos y dos dígitos a la derecha del punto decimal.
• total_visitas: utiliza el tipo de dato int para registrar el número total de visitas de cada invitado.

Crea una tabla llamada invitados que contenga cada una de estas columnas ejecutando el siguiente comando CREATE TABLE:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

A continuación, inserta algunos datos de muestra en la tabla vacía:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Una vez que hayas insertado los datos, estás listo para comenzar a usar consultas anidadas en SQL.

Usando Consultas Anidadas con SELECT

En SQL, una consulta es una operación que recupera datos de una tabla en una base de datos y siempre incluye una declaración SELECT. Una consulta anidada es una consulta completa incrustada dentro de otra operación. Una consulta anidada puede tener todos los elementos utilizados en una consulta regular, y cualquier consulta válida puede ser incrustada dentro de otra operación para convertirse en una consulta anidada. Por ejemplo, una consulta anidada puede ser incrustada dentro de operaciones INSERT y DELETE. Dependiendo de la operación, una consulta anidada debe ser incrustada encerrando la declaración dentro del número correcto de paréntesis para seguir un orden particular de operaciones. Una consulta anidada también es útil en escenarios donde deseas ejecutar múltiples comandos en una declaración de consulta, en lugar de escribir múltiples para devolver tu(s) resultado(s) deseado(s).

Para entender mejor las consultas anidadas, ilustremos cómo pueden ser útiles utilizando los datos de muestra del paso anterior. Por ejemplo, digamos que deseas encontrar todos los huéspedes en la tabla guests que han visitado el zoológico con una frecuencia más alta que el número promedio. Podrías asumir que puedes encontrar esta información con una consulta como la siguiente:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

Sin embargo, una consulta que utilice esta sintaxis devolverá un error:

Output
ERROR 1111 (HY000): Invalid use of group function

La razón de este error es que las funciones de agregado como AVG() no funcionan a menos que se ejecuten dentro de una cláusula SELECT.

Una opción para recuperar esta información sería primero ejecutar una consulta para encontrar el número promedio de visitas de huéspedes, y luego ejecutar otra consulta para encontrar resultados basados en ese valor, como en los siguientes dos ejemplos:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

Sin embargo, puedes obtener este mismo conjunto de resultados con una sola consulta anidando la primera consulta (SELECT AVG(total_visits) FROM guests;) dentro de la segunda. Ten en cuenta que con las consultas anidadas, es necesario usar la cantidad apropiada de paréntesis para completar la operación que deseas realizar. Esto se debe a que la consulta anidada es la primera operación que se realiza:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

Según este resultado, cinco invitados estaban visitando más que el promedio. Esta información podría ofrecer ideas útiles para pensar en formas creativas de asegurar que los miembros actuales continúen visitando el zoológico con frecuencia y renueven sus pases de membresía cada año. Además, este ejemplo demuestra el valor de usar una consulta anidada en una declaración completa para obtener los resultados deseados, en lugar de tener que ejecutar dos consultas separadas.

Uso de Consultas Anidadas con INSERT

Con una consulta anidada, no estás limitado solo a incrustarla dentro de otras declaraciones de SELECT. De hecho, también puedes usar consultas anidadas para insertar datos en una tabla existente al incrustar tu consulta anidada dentro de una operación INSERT.

Para ilustrar, supongamos que un zoológico afiliado solicita información sobre tus huéspedes porque están interesados en ofrecer un descuento del 15% a los huéspedes que compren una membresía “Residente” en su ubicación. Para hacer esto, utiliza CREATE TABLE para crear una nueva tabla llamada upgrade_guests que contenga seis columnas. Presta mucha atención a los tipos de datos, como int y varchar, y los caracteres máximos que pueden contener. Si no se alinean con los tipos de datos originales de la tabla guests que creaste en la sección de configuración de una base de datos de ejemplo, entonces recibirás un error cuando intentes insertar datos de la tabla guests usando una consulta anidada y los datos no se transferirán correctamente. Crea tu tabla con la siguiente información:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

Para mantener la consistencia y precisión, hemos mantenido la mayoría de la información del tipo de datos en esta tabla igual que en la tabla guests. También hemos eliminado cualquier columna adicional que no queremos en la nueva tabla. Con esta tabla vacía lista para usar, el siguiente paso es insertar los valores de datos deseados en la tabla.

En esta operación, escribe INSERT INTO y la nueva tabla upgrade_guests, para que haya una dirección clara de dónde se están insertando los datos. Luego, escribe tu consulta anidada con la declaración SELECT para recuperar los valores de datos relevantes y FROM para asegurarte de que provengan de la tabla guests.

Además, aplique el descuento del 15% a cualquiera de los miembros “Resident” incluyendo la operación matemática de multiplicación, *, para multiplicar por 0.85, dentro de la declaración de consulta anidada (membership_cost * 0.85). Luego, use la cláusula WHERE para ordenar los valores en la columna membership_type. Puede reducir aún más los resultados solo a las membresías “Resident” utilizando la cláusula LIKE y colocar el símbolo de porcentaje % antes y después de la palabra “Resident” entre comillas simples para seleccionar cualquier membresía que siga el mismo patrón, o en este caso, la misma terminología. Su consulta se escribirá de la siguiente manera:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

La salida indica que se agregaron cinco registros a la nueva tabla upgrade_guests. Para confirmar que los datos que solicitó se transfirieron correctamente desde la tabla guests a la tabla upgrade_guests vacía que creó, y con las condiciones que especificó para la consulta anidada y la cláusula WHERE, ejecute lo siguiente:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

Según esta salida de su nueva tabla upgrade_guests, la información de membresía de huéspedes relacionada con “Resident” de la tabla guests se insertó correctamente. Además, el nuevo membership_cost se ha recalculado con el descuento del 15% aplicado. Como resultado, esta operación ha ayudado a segmentar y dirigirse al público adecuado y tiene los precios con descuento disponibles para compartir con estos nuevos posibles miembros.

Usar consultas anidadas con DELETE

Para practicar el uso de una consulta anidada con una declaración DELETE, digamos que quieres eliminar a cualquier huésped que sea visitante frecuente porque solo quieres enfocarte en promocionar el descuento de pase premium mejorado a los miembros que actualmente no visitan mucho el zoológico.

Comienza esta operación con la declaración DELETE FROM para que quede claro de dónde se están eliminando los datos, en este caso, de la tabla upgrade_guests. Luego, usa la cláusula WHERE para ordenar cualquier total_visits que sean más que la cantidad especificada en la consulta anidada. En tu consulta anidada incrustada, utiliza SELECT para encontrar el promedio, AVG, de total_visits, para que la cláusula WHERE precedente tenga los valores de datos apropiados para comparar. Por último, usa FROM para recuperar esa información de la tabla guests. La declaración completa de la consulta será como la siguiente:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Confirma que esos registros se eliminaron correctamente de la tabla upgrade_guests y usa ORDER BY para organizar los resultados por total_visits en orden numérico y ascendente:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

Como indica esta salida, la declaración DELETE y la consulta anidada funcionaron correctamente al eliminar los valores de datos especificados. Esta tabla ahora contiene la información de los tres invitados con menos que el número promedio de visitas, lo cual es un gran punto de partida para que el representante del zoológico se comunique con ellos para que actualicen a un pase premium a un precio con descuento y, con suerte, los anime a visitar el zoológico con más frecuencia.

Conclusión

Las consultas anidadas son útiles porque te permiten obtener resultados altamente detallados que de otra manera solo podrías obtener ejecutando consultas separadas. Además, usar las declaraciones INSERT y DELETE con consultas anidadas te proporciona otra forma de insertar o eliminar datos en un solo paso. Si deseas obtener más información sobre cómo organizar tus datos, consulta nuestra serie sobre Cómo usar SQL.