Subconsultas
## Objetivos de Aprendizaje
- Entender tipos de subconsultas
- Usar subconsultas en WHERE
- Usar subconsultas en FROM
- Subconsultas correlacionadas vs no correlacionadas
## ¿Qué es una Subconsulta?
Una consulta dentro de una consulta:
```sql
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
## Tipos de Subconsultas
### Por Ubicación
- **Subconsulta escalar**: Devuelve un solo valor
- **Subconsulta de columna**: Devuelve una sola columna
- **Subconsulta de tabla**: Devuelve una tabla entera
### Por Dependencia
- **No correlacionada**: Independiente de la consulta externa
- **Correlacionada**: Referencia la consulta externa
## Subconsulta en WHERE
### Comparación con Escalar
```sql
-- Empleados que ganan acima del promedio
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
```
### Operador IN
```sql
-- Empleados en Ventas o Marketing
SELECT first_name, department
FROM employees
WHERE department IN (
SELECT department_name
FROM departments
WHERE location = 'New York'
);
```
### NOT IN (con manejo de NULL)
```sql
-- Empleados NO en ciertos departamentos
SELECT first_name
FROM employees
WHERE department NOT IN (
SELECT department_name
FROM departments
WHERE is_active = 1
);
```
### EXISTS / NOT EXISTS
```sql
-- Departamentos con empleados
SELECT department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);
```
### ANY / ALL
```sql
-- Salario mayor que CUALQUIERA de estos valores
WHERE salary > ANY (SELECT salary FROM interns)
-- Salario mayor que TODOS estos valores
WHERE salary > ALL (SELECT salary FROM interns)
```
## Subconsulta en FROM
### Tabla Derivada
```sql
SELECT
department,
avg_salary
FROM (
SELECT
department,
AVG(salary) as avg_salary
FROM employees
GROUP BY department
) AS dept_stats
WHERE avg_salary > 70000;
```
### Require Alias
```sql
-- Debe usar alias en tablas derivadas
FROM (SELECT ...) AS alias_name
```
## Subconsulta en SELECT
### Subconsulta Escalar
```sql
SELECT
first_name,
salary,
(SELECT AVG(salary) FROM employees) as avg_salary,
salary - (SELECT AVG(salary) FROM employees) as diff_from_avg
FROM employees;
```
### Alternativa con JOIN
```sql
-- Mismo resultado usando JOIN
SELECT
e.first_name,
e.salary,
dept.avg_salary
FROM employees e
JOIN (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
) dept ON e.department = dept.department;
```
## Subconsultas Correlacionadas
### Qué la Hace Correlacionada
Referencias a consulta externa:
```sql
SELECT
e.first_name,
e.salary,
e.department
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);
```
### Para Cada Empleado
1. Tomar empleado
2. Calcular promedio para su departamento
3. Comparar salario con ese promedio
## Optimización de Subconsultas
### JOIN vs Subconsulta
```sql
-- Subconsulta
SELECT *
FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE location = 'New York'
);
-- JOIN (usualmente más rápido)
SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = 'New York';
```
### Optimizadores Modernos
La mayoría de las bases de datos optimizan subconsultas a JOINs automáticamente.
## Patrones Comunes
### Top N por Grupo
```sql
SELECT *
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department = e1.department
);
```
### Filas con Max en Grupo
```sql
SELECT *
FROM employees
WHERE (department, salary) IN (
SELECT department, MAX(salary)
FROM employees
GROUP BY department
);
```
### Encontrar Registros Faltantes
```sql
-- IDs que no están en otra tabla
SELECT id
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees);
```
## Subconsulta con NULL
### Problema
```sql
-- NOT IN con NULLs devuelve resultados inesperados
WHERE id NOT IN (SELECT department_id FROM employees)
-- Si cualquier department_id es NULL, ninguna fila coincide!
```
### Solución
```sql
WHERE id NOT IN (
SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
);
```
## EXISTS vs COUNT(*)
```sql
-- EXISTS (se detiene en la primera coincidencia - más rápido)
WHERE EXISTS (SELECT 1 FROM table WHERE condition)
-- COUNT (cuenta todos - más lento)
WHERE (SELECT COUNT(*) FROM table WHERE condition) > 0
```
## Resumen
- Las subconsultas se anidan dentro de otras consultas
- Las subconsultas escalares devuelven un solo valor
- IN / NOT IN para múltiples valores
- EXISTS / NOT EXISTS para verificaciones de existencia
- Las subconsultas correlacionadas referencian la consulta externa
- Los JOINs frecuentemente tienen mejor rendimiento que las subconsultas
- Maneja los NULLs cuidadosamente con NOT IN
Comments
Comments powered by Giscus
To enable comments, add your Giscus embed code here.
Learn more about Giscus →