Consultas SQL Avanzadas: Outer Join, Inner Join y Subconsultas Complejas

En el mundo de las bases de datos, el dominio de las consultas avanzadas es crucial para extraer y manipular datos de manera eficiente. En este artículo, exploraremos el uso de INNER JOIN, OUTER JOIN y subconsultas complejas con la base de datos de una comisaría.

Creación de la Base de Datos

Para ilustrar los conceptos, comenzamos con la creación de una base de datos y las tablas necesarias:

CREATE DATABASE Comisaria;
USE Comisaria;

Tablas de la Base de Datos

Tabla de Agentes

CREATE TABLE Agentes (
    id_agente INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    rango VARCHAR(30),
    salario DECIMAL(10, 2) NOT NULL,
    fecha_ingreso DATE
);

Tabla de Ciudadanos

CREATE TABLE Ciudadanos (
    id_ciudadano INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL,
    direccion VARCHAR(100),
    telefono VARCHAR(15)
);

Tabla de Casos

CREATE TABLE Casos (
    id_caso INT AUTO_INCREMENT PRIMARY KEY,
    descripcion TEXT,
    fecha_inicio DATE,
    estado VARCHAR(20),
    id_agente INT,
    FOREIGN KEY (id_agente) REFERENCES Agentes(id_agente)
);

Tabla de Reportes

CREATE TABLE Reportes (
    id_reporte INT AUTO_INCREMENT PRIMARY KEY,
    id_caso INT,
    id_ciudadano INT,
    descripcion TEXT,
    fecha_reporte DATE,
    FOREIGN KEY (id_caso) REFERENCES Casos(id_caso),
    FOREIGN KEY (id_ciudadano) REFERENCES Ciudadanos(id_ciudadano)
);

Inserción de Datos

Insertamos algunos datos en estas tablas:

INSERT INTO Agentes (nombre, rango, salario, fecha_ingreso) VALUES
('Juan Pérez', 'Sargento', 45000.00, '2015-06-23'),
('Ana Gómez', 'Teniente', 50000.00, '2018-09-15'),
('Carlos López', 'Cabo', 40000.00, '2020-01-10');

INSERT INTO Ciudadanos (nombre, direccion, telefono) VALUES
('Pedro Martínez', 'Calle Falsa 123', '555-1234'),
('María Fernández', 'Avenida Siempre Viva 742', '555-5678'),
('Luis García', 'Boulevard Central 456', '555-8765');

INSERT INTO Casos (descripcion, fecha_inicio, estado, id_agente) VALUES
('Robo en tienda', '2022-03-10', 'Abierto', 1),
('Asalto a mano armada', '2022-04-22', 'Cerrado', 2),
('Vandalismo en parque', '2022-05-15', 'En Proceso', 3);

INSERT INTO Reportes (id_caso, id_ciudadano, descripcion, fecha_reporte) VALUES
(1, 1, 'Robo de mercancía en tienda', '2022-03-10'),
(2, 2, 'Asalto a mano armada en banco', '2022-04-22'),
(3, 3, 'Daños en parque público', '2022-05-15');

Consultas SQL Avanzadas

INNER JOIN

El INNER JOIN devuelve las filas que tienen coincidencias en ambas tablas.

Ejemplo: Obtener los nombres de los agentes y las descripciones de los casos que están gestionando

SELECT A.nombre, C.descripcion
FROM Agentes A
INNER JOIN Casos C ON A.id_agente = C.id_agente;

LEFT OUTER JOIN

El LEFT OUTER JOIN devuelve todas las filas de la tabla de la izquierda y las filas coincidentes de la tabla de la derecha. Si no hay coincidencia, los resultados contendrán NULL en las columnas de la tabla de la derecha.

Ejemplo: Listar todos los agentes y los casos que están gestionando, incluyendo agentes sin casos asignados

SELECT A.nombre, C.descripcion
FROM Agentes A
LEFT OUTER JOIN Casos C ON A.id_agente = C.id_agente;

RIGHT OUTER JOIN

El RIGHT OUTER JOIN devuelve todas las filas de la tabla de la derecha y las filas coincidentes de la tabla de la izquierda. Si no hay coincidencia, los resultados contendrán NULL en las columnas de la tabla de la izquierda.

Ejemplo: Listar todos los casos y los nombres de los agentes responsables, incluyendo casos sin agentes asignados

SELECT C.descripcion, A.nombre
FROM Casos C
RIGHT OUTER JOIN Agentes A ON C.id_agente = A.id_agente;

Subconsultas Complejas

Ejemplo: Encontrar los agentes que tienen más de un caso asignado y listar sus nombres y la cantidad de casos

SELECT A.nombre, COUNT(C.id_caso) AS numero_de_casos
FROM Agentes A
INNER JOIN Casos C ON A.id_agente = C.id_agente
GROUP BY A.nombre
HAVING COUNT(C.id_caso) > 1;

Uso de SELECT IN

El operador IN se utiliza para especificar múltiples valores en una cláusula WHERE.

Ejemplo: Obtener los nombres de los agentes que han gestionado casos cerrados

SELECT nombre
FROM Agentes
WHERE id_agente IN (SELECT id_agente FROM Casos WHERE estado = 'Cerrado');

Combinación de Subconsultas y JOIN

Ejemplo: Listar los ciudadanos que han reportado casos gestionados por el agente «Juan Pérez»

SELECT C.nombre
FROM Ciudadanos C
WHERE C.id_ciudadano IN (
    SELECT R.id_ciudadano
    FROM Reportes R
    INNER JOIN Casos CA ON R.id_caso = CA.id_caso
    INNER JOIN Agentes A ON CA.id_agente = A.id_agente
    WHERE A.nombre = 'Juan Pérez'
);

Subconsultas Correlacionadas

Las subconsultas correlacionadas dependen de la consulta externa para cada fila procesada.

Ejemplo: Encontrar los agentes cuyo salario es mayor que el salario promedio de todos los agentes

SELECT nombre, salario 
FROM Agentes A1
WHERE salario > (SELECT AVG(salario) FROM Agentes A2 WHERE A1.id_agente != A2.id_agente);

Vistas y Consultas Complejas

Las vistas pueden simplificar la consulta de datos complejos.

Ejemplo: Crear una vista de los casos abiertos y luego listar los reportes relacionados

CREATE VIEW CasosAbiertos AS
SELECT id_caso, descripcion, id_agente
FROM Casos
WHERE estado = 'Abierto';

SELECT R.id_reporte, R.descripcion, CA.descripcion AS caso_descripcion
FROM Reportes R
INNER JOIN CasosAbiertos CA ON R.id_caso = CA.id_caso;

Conclusión

En este artículo, hemos explorado cómo realizar consultas SQL avanzadas utilizando INNER JOIN, OUTER JOIN y subconsultas complejas con una base de datos de comisaría como ejemplo. Estas técnicas son esenciales para manejar y analizar datos de manera efectiva en un entorno de base de datos relacional. Dominar estos conceptos te permitirá extraer información valiosa y realizar análisis detallados, lo cual es crucial para cualquier profesional que trabaje con SQL.