Técnicas de Subconsultas en SQLite

Introducción

En este laboratorio, explorarás las técnicas de subconsultas (subqueries) de SQLite para mejorar tus capacidades de recuperación y filtrado de datos. Aprenderás cómo usar subconsultas dentro de la cláusula WHERE, incrustarlas en la sentencia SELECT y construir subconsultas correlacionadas (correlated subqueries).

Skills Graph

Crear Tablas e Insertar Datos

En este primer paso, crearás dos tablas, departments y employees, e insertarás algunos datos de ejemplo en ellas. Esto proporcionará los datos que necesitas para practicar el uso de subconsultas (subqueries) en los siguientes pasos.

Primero, abre tu terminal en la VM de LabEx. Tu ruta predeterminada es /home/labex/project.

Para comenzar, conéctate a una base de datos SQLite llamada company.db. Si la base de datos no existe, SQLite la creará por ti. Ejecuta el siguiente comando:

sqlite3 company.db

Este comando abre la herramienta de línea de comandos de SQLite y se conecta a la base de datos company.db. Verás el prompt sqlite>.

Ahora, crea la tabla departments con el siguiente comando SQL:

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

Este comando crea una tabla llamada departments con tres columnas: department_id, department_name y location. La columna department_id es la clave primaria (primary key) para esta tabla.

A continuación, inserta algunos datos de ejemplo en la tabla departments:

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

Este comando inserta tres filas en la tabla departments, representando tres departamentos diferentes y sus ubicaciones.

Ahora, crea la tabla employees con el siguiente comando SQL:

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Este comando crea una tabla llamada employees con tres columnas: employee_id, employee_name y department_id. La columna department_id es una clave externa (foreign key) que hace referencia a la columna department_id en la tabla departments.

Finalmente, inserta algunos datos de ejemplo en la tabla employees:

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);

Este comando inserta cuatro filas en la tabla employees, representando cuatro empleados diferentes y sus IDs de departamento.

Usar Subconsultas en la Cláusula WHERE

En este paso, aprenderás cómo usar subconsultas (subqueries) en la cláusula WHERE para filtrar los resultados basándote en la salida de otra consulta.

Una subconsulta es una sentencia SELECT anidada dentro de otra sentencia SQL. En este caso, usarás una subconsulta para seleccionar valores department_id de la tabla departments y luego usar esos valores para filtrar los resultados de una consulta en la tabla employees.

Busquemos a todos los empleados que trabajan en departamentos ubicados en 'New York'. Para hacer esto, primero necesitarás encontrar el department_id para los departamentos en 'New York', y luego encontrar a todos los empleados con ese department_id.

Ingresa el siguiente comando SQL en el prompt sqlite>:

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Este comando selecciona el employee_name de la tabla employees donde el department_id está en la lista de valores department_id devueltos por la subconsulta. La subconsulta selecciona el department_id de la tabla departments donde la location es 'New York'.

Después de ejecutar el comando, deberías ver la siguiente salida:

Alice
Charlie

Esta salida muestra los nombres de los empleados que trabajan en el departamento de Ventas (Sales), que está ubicado en Nueva York.

Incrustar Subconsultas en la Cláusula SELECT

En este paso, aprenderás cómo incrustar subconsultas (subqueries) dentro de la cláusula SELECT de una sentencia SQL para recuperar datos relacionados.

Incrustar una subconsulta en la cláusula SELECT te permite recuperar un único valor para cada fila en la consulta externa. Este valor es a menudo un valor calculado o un valor relacionado de otra tabla.

Recuperemos el nombre de cada empleado junto con el nombre de su departamento. Ingresa el siguiente comando SQL en el prompt sqlite>:

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

Este comando selecciona el employee_name de la tabla employees y también incluye una subconsulta que recupera el department_name de la tabla departments. La subconsulta usa el department_id de la tabla employees para coincidir con el departamento correcto. El resultado de la subconsulta se define con el alias department_name.

Después de ejecutar el comando, deberías ver la siguiente salida:

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

Esta salida muestra el nombre de cada empleado y el nombre de su departamento correspondiente.

Construir Subconsultas Correlacionadas

En este paso, aprenderás cómo construir subconsultas correlacionadas (correlated subqueries). Las subconsultas correlacionadas son subconsultas que hacen referencia a una columna de la consulta externa. Esto significa que la subconsulta se ejecuta una vez por cada fila de la consulta externa.

A diferencia de las subconsultas simples que se ejecutan una vez y su resultado es utilizado por la consulta externa, las subconsultas correlacionadas dependen de la consulta externa para sus valores. Se utilizan cuando necesitas comparar valores dentro de la subconsulta con valores en la fila actual de la consulta externa.

Busquemos a todos los empleados que trabajan en un departamento ubicado en la misma ciudad que el nombre del empleado. Para que esto funcione, primero actualizaremos los nombres de los empleados para que sean nombres de ciudades.

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

Estos comandos actualizan la columna employee_name en la tabla employees a nombres de ciudades.

Ahora, escribamos la subconsulta correlacionada:

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

Este comando selecciona el employee_name de la tabla employees (con el alias e) donde el department_id está en la lista de valores department_id devueltos por la subconsulta. La subconsulta selecciona el department_id de la tabla departments (con el alias d) donde la location coincide con el employee_name de la consulta externa.

Después de ejecutar el comando, deberías ver la siguiente salida:

New York
Los Angeles
San Francisco

Esta salida muestra los nombres de los empleados (ahora nombres de ciudades) que trabajan en departamentos ubicados en la misma ciudad.

Evaluar la Eficiencia de las Subconsultas con JOIN

En este paso, aprenderás cómo evaluar la eficiencia de las subconsultas (subqueries) y explorar enfoques alternativos utilizando operaciones JOIN para la optimización.

Si bien las subconsultas son poderosas, a veces pueden generar cuellos de botella en el rendimiento, especialmente con grandes conjuntos de datos (datasets). En muchos casos, puedes reescribir las subconsultas utilizando operaciones JOIN, que pueden ser más eficientes.

Reescribamos la subconsulta correlacionada del paso anterior utilizando un JOIN:

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

Este comando selecciona el employee_name de la tabla employees (con el alias e) y la une (joins) con la tabla departments (con el alias d) en la columna department_id. La cláusula WHERE luego filtra los resultados para incluir solo las filas donde la location en la tabla departments coincide con el employee_name en la tabla employees.

Para verificar el resultado, ejecuta el comando. Deberías ver la misma salida que en el paso anterior:

New York
Los Angeles
San Francisco

Para evaluar la eficiencia, normalmente usarías EXPLAIN QUERY PLAN antes y después del cambio. Sin embargo, debido a las limitaciones en el entorno LabEx, no podemos demostrar completamente el comando EXPLAIN QUERY PLAN. La conclusión clave es que las operaciones JOIN son a menudo más eficientes que las subconsultas correlacionadas, especialmente para conjuntos de datos más grandes.

Finalmente, sal del shell sqlite3:

.exit

Esto te devolverá al prompt de bash.

Resumen

En este laboratorio, has aprendido cómo usar subconsultas (subqueries) de SQLite para mejorar tus capacidades de recuperación y filtrado de datos. Practicaste el uso de subconsultas dentro de la cláusula WHERE, incrustándolas en la declaración SELECT y construyendo subconsultas correlacionadas (correlated subqueries). También aprendiste cómo reescribir subconsultas utilizando operaciones JOIN para una mejor eficiencia. Estas técnicas te proporcionan herramientas poderosas para trabajar con datos en SQLite.