JOINs completo

Los JOINs son la operación que hace a las bases de datos relacionales especiales. Permiten combinar datos de múltiples tablas basándose en relaciones entre ellas, respondiendo preguntas que ninguna tabla puede responder por sí sola: "¿qué productos compró cada cliente?", "¿qué vendedor tiene más ventas este mes?", "¿qué artículos están en pedidos pendientes?".

Preparar el esquema de práctica

-- Schema completo para practicar JOINs
CREATE TABLE clientes (
  id      SERIAL PRIMARY KEY,
  nombre  VARCHAR(150) NOT NULL,
  email   VARCHAR(255) NOT NULL UNIQUE,
  ciudad  VARCHAR(100)
);

CREATE TABLE vendedores (
  id     SERIAL PRIMARY KEY,
  nombre VARCHAR(150) NOT NULL,
  region VARCHAR(50)
);

CREATE TABLE pedidos (
  id          SERIAL        PRIMARY KEY,
  cliente_id  INTEGER       REFERENCES clientes(id),
  vendedor_id INTEGER       REFERENCES vendedores(id),
  total       NUMERIC(10,2) NOT NULL,
  estado      VARCHAR(20)   NOT NULL DEFAULT 'pendiente',
  creado_en   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

CREATE TABLE pedidos_items (
  pedido_id   INTEGER NOT NULL REFERENCES pedidos(id) ON DELETE CASCADE,
  producto_id INTEGER NOT NULL REFERENCES productos(id),
  cantidad    INTEGER NOT NULL DEFAULT 1,
  precio_unit NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (pedido_id, producto_id)
);

INSERT INTO clientes (nombre, email, ciudad) VALUES
  ('Ana García',    '[email protected]',   'Madrid'),
  ('Luis Martínez', '[email protected]',  'Barcelona'),
  ('Eva López',     '[email protected]',   'Sevilla'),
  ('Carlos Ruiz',   '[email protected]', 'Madrid'),
  ('Marta Soto',    '[email protected]', 'Valencia');

INSERT INTO vendedores (nombre, region) VALUES
  ('Pedro Ramos',   'Norte'),
  ('Laura Vega',    'Sur'),
  ('Diego Torres',  'Este');

INSERT INTO pedidos (cliente_id, vendedor_id, total, estado) VALUES
  (1, 1, 1499.98, 'completado'),
  (1, 2,  199.99, 'completado'),
  (2, 1,   89.99, 'pendiente'),
  (3, 3,  299.99, 'enviado'),
  (5, NULL, 119.99, 'pendiente');
-- Nota: cliente_id=4 (Carlos) no tiene pedidos
-- pedido_id=5 no tiene vendedor asignado

INNER JOIN — La unión básica

INNER JOIN devuelve solo las filas que tienen correspondencia en AMBAS tablas. Es el JOIN más común y el predeterminado cuando escribes JOIN sin especificar el tipo.

-- INNER JOIN: solo clientes CON pedidos
SELECT
  c.nombre  AS cliente,
  c.ciudad,
  p.id      AS pedido_id,
  p.total,
  p.estado
FROM clientes c
INNER JOIN pedidos p ON p.cliente_id = c.id
ORDER BY c.nombre, p.id;

-- Resultado: 4 filas (Ana=2, Luis=1, Eva=1, Marta=1)
-- Carlos (id=4) NO aparece porque no tiene pedidos

-- JOIN es sinónimo de INNER JOIN
SELECT c.nombre, p.total
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;

LEFT JOIN — El más importante en aplicaciones

LEFT JOIN devuelve TODAS las filas de la tabla izquierda, más las filas coincidentes de la tabla derecha. Si no hay coincidencia, las columnas de la tabla derecha son NULL.

-- LEFT JOIN: TODOS los clientes, tengan o no pedidos
SELECT
  c.nombre       AS cliente,
  c.ciudad,
  COUNT(p.id)    AS total_pedidos,
  COALESCE(SUM(p.total), 0) AS total_gastado
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nombre, c.ciudad
ORDER BY total_gastado DESC;

-- Resultado: 5 filas (Carlos aparece con total_pedidos=0 y total_gastado=0)

-- Encontrar clientes SIN pedidos (anti-join pattern)
SELECT c.nombre, c.email
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE p.id IS NULL;
-- Resultado: Carlos Ruiz (nunca ha pedido nada)

El patrón LEFT JOIN ... WHERE tabla_derecha.id IS NULL es el anti-join: encuentra filas de la tabla izquierda que NO tienen correspondencia en la derecha. Muy útil para encontrar registros "huérfanos" o "inactivos".

RIGHT JOIN — El espejo del LEFT

RIGHT JOIN es exactamente como LEFT JOIN pero al revés: devuelve todas las filas de la tabla derecha. En la práctica se usa muy poco porque siempre puedes reordenar las tablas y usar LEFT JOIN.

-- RIGHT JOIN: todos los vendedores, tengan o no pedidos asignados
SELECT
  v.nombre       AS vendedor,
  v.region,
  COUNT(p.id)    AS pedidos_asignados,
  COALESCE(SUM(p.total), 0) AS total_ventas
FROM pedidos p
RIGHT JOIN vendedores v ON p.vendedor_id = v.id
GROUP BY v.id, v.nombre, v.region
ORDER BY total_ventas DESC;

-- Equivalente con LEFT JOIN (más legible)
SELECT
  v.nombre, v.region,
  COUNT(p.id) AS pedidos_asignados,
  COALESCE(SUM(p.total), 0) AS total_ventas
FROM vendedores v
LEFT JOIN pedidos p ON p.vendedor_id = v.id
GROUP BY v.id, v.nombre, v.region
ORDER BY total_ventas DESC;

FULL OUTER JOIN — Unión completa

FULL OUTER JOIN devuelve TODAS las filas de AMBAS tablas. Las filas sin correspondencia tienen NULL en las columnas de la otra tabla.

-- FULL OUTER JOIN: ver todos los clientes y todos los vendedores
-- con sus relaciones (si existen)
SELECT
  c.nombre  AS cliente,
  v.nombre  AS vendedor,
  p.total
FROM pedidos p
FULL OUTER JOIN clientes c   ON p.cliente_id  = c.id
FULL OUTER JOIN vendedores v ON p.vendedor_id = v.id
ORDER BY c.nombre NULLS LAST, v.nombre NULLS LAST;

-- Encontrar registros sin correspondencia en ningún lado
SELECT
  c.id   AS cliente_id,  c.nombre AS cliente,
  p.id   AS pedido_id,   p.total  AS total_pedido
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id IS NULL OR p.id IS NULL;
-- Muestra: Carlos (sin pedidos) y el pedido sin vendedor (si lo hubiera)

CROSS JOIN — Producto cartesiano

CROSS JOIN produce el producto cartesiano: combina cada fila de la primera tabla con cada fila de la segunda. Sin condición de unión, el resultado tiene n × m filas.

-- CROSS JOIN: generar todas las combinaciones posibles
-- Caso de uso: crear una tabla de horarios (todos los días × todos los turnos)
CREATE TEMP TABLE dias (nombre VARCHAR(20));
CREATE TEMP TABLE turnos (nombre VARCHAR(20), hora_inicio TIME, hora_fin TIME);

INSERT INTO dias VALUES ('Lunes'), ('Martes'), ('Miércoles'), ('Jueves'), ('Viernes');
INSERT INTO turnos VALUES ('Mañana', '08:00', '14:00'), ('Tarde', '14:00', '20:00');

SELECT d.nombre AS dia, t.nombre AS turno, t.hora_inicio, t.hora_fin
FROM dias d
CROSS JOIN turnos t
ORDER BY d.nombre, t.hora_inicio;
-- Resultado: 10 filas (5 días × 2 turnos)

-- También útil para generar series de prueba
SELECT
  p.nombre AS producto,
  c.nombre AS cliente
FROM productos p
CROSS JOIN clientes c
LIMIT 10;

Self-JOIN — Una tabla unida consigo misma

El self-join une una tabla consigo misma. Es necesario para modelar relaciones jerárquicas (empleados y sus managers, categorías y subcategorías):

-- Ejemplo: tabla de empleados con jerarquía
CREATE TABLE empleados (
  id         SERIAL PRIMARY KEY,
  nombre     VARCHAR(100) NOT NULL,
  manager_id INTEGER REFERENCES empleados(id)  -- Auto-referencia
);

INSERT INTO empleados (nombre, manager_id) VALUES
  ('Ana Directora',   NULL),   -- id=1, sin manager (es la jefa)
  ('Luis Manager',    1),      -- id=2, reporta a Ana
  ('Eva Manager',     1),      -- id=3, reporta a Ana
  ('Carlos Dev',      2),      -- id=4, reporta a Luis
  ('Marta Dev',       2),      -- id=5, reporta a Luis
  ('Pedro Dev',       3);      -- id=6, reporta a Eva

-- Self-JOIN: mostrar empleados con el nombre de su manager
SELECT
  e.nombre  AS empleado,
  m.nombre  AS manager
FROM empleados e
LEFT JOIN empleados m ON e.manager_id = m.id
ORDER BY m.nombre NULLS FIRST, e.nombre;
-- Ana aparece con manager=NULL (es la jefa)

-- Encontrar empleados que son del mismo manager (compañeros)
SELECT
  e1.nombre AS empleado1,
  e2.nombre AS empleado2,
  m.nombre  AS manager_comun
FROM empleados e1
JOIN empleados e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
JOIN empleados m  ON e1.manager_id = m.id
ORDER BY m.nombre, e1.nombre;

JOIN con múltiples tablas

En aplicaciones reales, frecuentemente necesitas unir 3, 4 o más tablas:

-- Detalle completo de pedidos: cliente + vendedor + items + productos
SELECT
  p.id                     AS pedido_id,
  c.nombre                 AS cliente,
  c.ciudad,
  COALESCE(v.nombre, 'Sin asignar') AS vendedor,
  pr.nombre                AS producto,
  pi.cantidad,
  pi.precio_unit,
  pi.cantidad * pi.precio_unit AS subtotal,
  p.estado,
  p.creado_en
FROM pedidos p
JOIN clientes c          ON p.cliente_id  = c.id
LEFT JOIN vendedores v   ON p.vendedor_id = v.id
JOIN pedidos_items pi    ON pi.pedido_id  = p.id
JOIN productos pr        ON pi.producto_id = pr.id
ORDER BY p.id, pr.nombre;

ON vs USING vs NATURAL JOIN

-- ON: condición explícita (más flexible, recomendado)
SELECT c.nombre, p.total
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;

-- USING: cuando las columnas tienen el mismo nombre en ambas tablas
-- (Solo funciona si ambas tablas tienen una columna con el mismo nombre)
-- Requiere que la columna se llame igual en ambas tablas
SELECT nombre, total
FROM clientes
JOIN pedidos USING (id);  -- Solo si pedidos tuviera columna 'id' que referencia clientes
-- En la práctica, usamos 'cliente_id' en pedidos, así que ON es necesario aquí

-- NATURAL JOIN: unión automática por columnas con el mismo nombre
-- ¡PELIGROSO! Evítalo en código de producción
SELECT * FROM clientes NATURAL JOIN pedidos;
-- Si ambas tablas tienen una columna 'nombre', también unirá por 'nombre',
-- lo que raramente es lo que quieres. El comportamiento cambia si añades columnas.

[!warning] Nunca uses NATURAL JOIN en código de producción. El comportamiento de NATURAL JOIN depende de los nombres de columnas de las tablas. Si alguien añade una columna activo a dos tablas, de repente el NATURAL JOIN también filtra por activo, cambiando los resultados silenciosamente. Siempre especifica la condición de unión explícitamente con ON.

Rendimiento de los JOINs

Los JOINs son más eficientes con índices en las columnas de unión:

-- Crear índices en claves foráneas para acelerar los JOINs
CREATE INDEX idx_pedidos_cliente_id  ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_vendedor_id ON pedidos(vendedor_id);
CREATE INDEX idx_pedidos_items_pedido_id  ON pedidos_items(pedido_id);
CREATE INDEX idx_pedidos_items_producto_id ON pedidos_items(producto_id);

-- EXPLAIN ANALYZE para ver el plan de ejecución
EXPLAIN ANALYZE
SELECT c.nombre, COUNT(p.id) AS pedidos
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nombre;

[!tip] La regla de los índices en JOINs: PostgreSQL crea automáticamente índices en las claves primarias y columnas UNIQUE. Sin embargo, NO crea índices automáticamente en las claves foráneas. Añade manualmente un índice en cada columna que uses como clave foránea (CREATE INDEX ON tabla(columna_fk)). Esto puede marcar una diferencia de 100x en el rendimiento de consultas con JOINs sobre tablas grandes.

Resumen visual de los tipos de JOIN

Tipo	Filas devueltas
INNER JOIN	Solo las que tienen coincidencia en ambas tablas
LEFT JOIN	Todas las de la tabla izquierda + coincidencias de la derecha
RIGHT JOIN	Todas las de la tabla derecha + coincidencias de la izquierda
FULL OUTER JOIN	Todas las de ambas tablas
CROSS JOIN	Producto cartesiano (n × m filas)

El dominio de los JOINs te da el poder de responder prácticamente cualquier pregunta sobre datos relacionados. En la siguiente lección exploraremos las subconsultas y los CTEs (Common Table Expressions), que permiten escribir consultas complejas de forma modular y legible.

---

nextSteps:

• slug: subqueries-y-ctes label: "Siguiente: Subconsultas y CTEs"