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.

person encoding in laptop
Photo by Lukas on Pexels.com
error: Content is protected !!

Descubre más desde InfoGonzalez - Blog de formador e informático

Suscríbete ahora para seguir leyendo y obtener acceso al archivo completo.

Seguir leyendo

Este sitio web utiliza cookies, si necesitas más información puedes visitar nuestra política de privacidad    Ver
Privacidad
Creative Commons License
Except where otherwise noted, the content on this site is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.