INNER JOIN

1. Qué es

Hasta aquí todas las consultas leían una tabla. Y por eso los resultados salían con category_id = 4 en vez de Dairy Products, y con employee_id = 3 en vez de Janet Leverling.

Eso no es un defecto del diseño: es el diseño. Una base relacional no repite el nombre de la categoría en cada producto, guarda un id que apunta a ella (que-es-una-base-de-datos-relacional). El precio de esa decisión es que hay que volver a juntar las piezas al consultar. Eso es un JOIN.

Un INNER JOIN empareja filas de dos tablas según una condición, y devuelve solo las que encuentran pareja. Lo que no empareja, desaparece — y esa es la parte que hay que vigilar.

2. Sintaxis

SELECT ...
FROM tabla_a AS a
JOIN tabla_b AS b ON b.columna = a.columna;

• JOIN a secas significa INNER JOIN. Las dos formas son idénticas; INNER es documentación.
• ON es la condición de emparejamiento. No es un filtro de filas: dice qué fila de A va con qué fila de B.
• Se pueden encadenar tantos JOIN como haga falta.
• Los alias de tabla dejan de ser opcionales en cuanto hay dos tablas.

Tres formas de escribir el mismo emparejamiento:

NATURAL JOIN empareja por todas las columnas que se llamen igual, sin que tú las escribas. Hoy funciona; el día que alguien añada una columna descripcion a las dos tablas, la consulta cambia de significado en silencio. Es de las pocas cosas en SQL que se pueden descartar sin matices.

3. Ejemplos sobre Northwind

El caso básico — poner nombre a un id:

SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON c.category_id = p.category_id
ORDER BY p.product_id
LIMIT 5;

         product_name         | category_name
------------------------------+---------------
 Chai                         | Beverages
 Chang                        | Beverages
 Aniseed Syrup                | Condiments
 Chef Anton's Cajun Seasoning | Condiments
 Chef Anton's Gumbo Mix       | Condiments

77 filas, las mismas que tiene products. Cada producto encontró exactamente una categoría: el JOIN no multiplicó ni perdió nada. Eso pasa porque category_id es la clave primaria de categories, así que la pareja es única.

Comprueba siempre el conteo antes y después de un JOIN. Si products tenía 77 y el resultado tiene 77, todo bien. Si tiene más, el JOIN multiplica. Si tiene menos, está perdiendo filas. Las dos cosas son bugs con aspecto de resultado.

Ahora sí: el informe que en group-by salía con ids

SELECT c.category_name,
       count(*)                 AS productos,
       round(avg(p.unit_price),2) AS precio_medio
FROM products p
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.category_name
ORDER BY precio_medio DESC;

 category_name  | productos | precio_medio
----------------+-----------+--------------
 Meat/Poultry   |         6 |        54.01
 Beverages      |        12 |        37.98
 Produce        |         5 |        32.37
 Dairy Products |        10 |        28.73
 Confections    |        13 |        25.16
 Condiments     |        12 |        22.85
 Seafood        |        12 |        20.68
 Grains/Cereals |         7 |        20.25

Es exactamente el mismo GROUP BY de group-by, pero ahora se puede leer sin diccionario. Esa es toda la diferencia que aporta un JOIN, y es enorme.

Encadenar tres tablas:

SELECT o.order_id,
       c.company_name,
       e.first_name || ' ' || e.last_name AS empleado,
       o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN employees e ON e.employee_id = o.employee_id
ORDER BY o.order_id
LIMIT 4;

 order_id |       company_name        |     empleado     | order_date
----------+---------------------------+------------------+------------
    10248 | Vins et alcools Chevalier | Steven Buchanan  | 1996-07-04
    10249 | Toms Spezialitäten        | Michael Suyama   | 1996-07-05
    10250 | Hanari Carnes             | Margaret Peacock | 1996-07-08
    10251 | Victuailles en stock      | Janet Leverling  | 1996-07-08

830 filas, las mismas que orders. Cada JOIN añade columnas, no filas — mientras el lado al que unes tenga como mucho una fila por pareja.

3.1 Lo que el INNER JOIN se lleva por delante

SELECT count(*)                   FROM customers;                          -- 91
SELECT count(DISTINCT c.customer_id)
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;           -- 89

Dos clientes desaparecen. No están mal: simplemente no han hecho ningún pedido, así que no encuentran pareja en orders y el INNER JOIN los descarta.

Y aquí está el peligro real: si te piden "la lista de clientes con su número de pedidos" y usas INNER JOIN, entregas 89 y faltan justo los dos que más te interesarían — los que no compran nunca. Sin error, sin aviso, sin fila vacía.

Un INNER JOIN es un filtro disfrazado. Filtra las filas que no tienen correspondencia. Cuando lo que quieres es conservarlas, necesitas un LEFT JOIN (left-right-y-full-join).

3.2 Columnas ambiguas

SELECT category_id FROM products p JOIN categories c ON c.category_id = p.category_id;

ERROR:  column reference "category_id" is ambiguous

Las dos tablas tienen una columna con ese nombre y PostgreSQL no adivina. Se resuelve con el prefijo: p.category_id.

El caso donde más muerde:

SELECT company_name FROM customers c JOIN suppliers s ON s.country = c.country;

ERROR:  column reference "company_name" is ambiguous

customers y suppliers tienen las dos company_name. Aquí el error es una suerte: te obliga a decidir de cuál hablas. Cuando el nombre coincide pero el significado no, un SELECT * te devuelve dos columnas llamadas igual y ningún error.

Regla: prefija siempre. o.order_id, c.company_name. Cuesta dos caracteres y hace la consulta legible cuando tenga cuatro tablas.

3.3 ON con más de una condición

ON no está limitado a una igualdad:

SELECT count(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
                AND o.ship_country = c.country;

 count
-------
   830

830 de 830: en Northwind, todos los pedidos se envían al país del cliente. Es un hecho comprobado de estos datos, no una garantía del modelo — la dirección de envío existe precisamente para poder ser distinta.

Y también hay JOIN sin igualdad (por rangos, por ejemplo ON f.fecha BETWEEN t.desde AND t.hasta). Son legítimos y caros; se ven en cross-join-y-self-join.

Una tabla puente:

SELECT t.territory_description, r.region_description
FROM territories t
JOIN region r ON r.region_id = t.region_id
ORDER BY t.territory_id LIMIT 4;

 territory_description | region_description
-----------------------+--------------------
 Westboro              | Eastern
 Bedford               | Eastern
 Georgetow             | Eastern
 Boston                | Eastern

4. Errores comunes

1. Olvidar el ON. En PostgreSQL da error de sintaxis. Con la sintaxis antigua de coma (FROM a, b) no da error: hace un producto cartesiano. products, categories devuelve 616 filas (77 × 8) en vez de 77.
2. Usar INNER JOIN cuando querías conservar las filas sin pareja. Los 2 clientes perdidos.
3. No prefijar las columnas. Error de ambigüedad, o peor: dos columnas iguales en un SELECT *.
4. Unir por la columna equivocada. ON c.category_id = p.supplier_id no da error: da un resultado. Solo el conteo de filas te avisa.
5. NATURAL JOIN. Ver la sección 2.
6. No comprobar el conteo antes y después.
7. Unir por texto en vez de por la clave. ON c.company_name = x.nombre falla con un espacio de más, un acento o una mayúscula. Une por ids.

5. Diferencias con SQL Server

INNER JOIN es idéntico en los dos motores: misma sintaxis, mismo ON, mismo USING... salvo un detalle:

USING es la única incompatibilidad práctica, y es fácil de evitar: escribe siempre el ON completo.

La sintaxis antigua con coma —FROM a, b WHERE a.id = b.id— funciona en ambos y hay que dejar de escribirla: mezcla la condición de emparejamiento con los filtros en el mismo WHERE, y el día que necesitas un LEFT JOIN no hay forma de expresarlo.

6. Ejercicios

1. Cada producto con el nombre de su proveedor y el país del proveedor.
2. Los 10 pedidos más caros por flete, con el nombre del cliente y su país.
3. Facturación por categoría de producto, de mayor a menor. (Necesita tres tablas.)
4. Los territorios de la región Western, ordenados alfabéticamente.
5. Comprueba si algún producto tiene un supplier_id que no exista en suppliers. ¿Cómo lo sabes sin mirar las dos tablas a mano?

7. Pregunta de negocio

Ventas quiere el ranking de comerciales de 1997: nombre, pedidos y facturación. Es el mismo informe que ya hiciste en el tema de GROUP BY, pero ahora con nombres. Entrégalo — y comprueba que el número de pedidos sigue siendo el mismo que antes.

La comprobación es el ejercicio.