Muchas tablas, y una tabla unida consigo misma

En resumen: Varios JOIN seguidos enlazan una cadena de tablas, y un self join enlaza una tabla consigo misma, por ejemplo una categoría con su categoría padre.

Problema

Facturación por categoría

La dueña quiere saber de qué categorías viene el dinero. La cantidad y el precio están en order_items. Si el pedido se canceló está en orders. La categoría del producto está en products, y el nombre de la categoría en categories.

Cuatro tablas para un informe. JOIN no se detiene en dos.

La idea

Una cadena de JOIN

Cada JOIN siguiente añade una tabla a lo que ya se ha armado. El pedido 60, con sus productos y las categorías de estos:

SQLbase de datos: tienda

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.id = 60;

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.id = 60;

▶ EjecutarRestaurar la consultaBase nueva

El camino sigue las claves foráneas: orders → order_items (por order_id) → products (por product_id) → categories (por category_id). Cada eslabón es un JOIN … ON aparte.

Tanto products como categories tienen una columna name. Por eso aquí les ponemos nombre con AS: product y category. Si no, el resultado tendría dos columnas name.

Pruébalo

Quién valoró qué

Para cada reseña, muestra el nombre del cliente, el nombre del producto y la puntuación.

SQLbase de datos: tienda

SELECT r.customer_id, r.product_id, r.rating
FROM reviews r;

SELECT r.customer_id, r.product_id, r.rating FROM reviews r;

Comprobar▶ Ejecutar

Míralo

El dinero perdido

Facturación por categoría, sin los pedidos cancelados:

SQLbase de datos: tienda

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ EjecutarRestaurar la consultaBase nueva

Parece correcto. Ahora la facturación total, sin categorías:

SQLbase de datos: tienda

SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled';

SELECT SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled';

▶ EjecutarRestaurar la consultaBase nueva

Las seis categorías dan 1537 €, pero la facturación es de 1697 €.

1. Las categorías suman 1537 €, pero la facturación total es de 1697 €. ¿Dónde están los 160 €?

Un error de redondeoEn los pedidos canceladosEn las ventas de tarjetas de regalo: la tarjeta de regalo no tiene categoría, y JOIN categories la echó

Comprobar

En una cadena de JOIN, cada eslabón puede echar filas sin avisar. Para un producto sin categoría no hay pareja en categories. Con un LEFT JOIN a las categorías las ventas se quedan, y COALESCE le da un nombre a la categoría que falta:

SQLbase de datos: tienda

SELECT COALESCE(cat.name, 'No category') AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT COALESCE(cat.name, 'No category') AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id LEFT JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ EjecutarRestaurar la consultaBase nueva

Siete filas, y el total es de 1697 €. Siempre que un informe cuente dinero, compara su total con el total general. La diferencia muestra las filas perdidas.

Pruébalo

Facturación por ciudad

Muestra la facturación por ciudad del cliente, sin los pedidos cancelados.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

La idea

Una tabla unida consigo misma

En las categorías, parent_id apunta a id de la misma tabla. Para ver una categoría junto a su padre, unes categories consigo misma. La tabla participa dos veces, con dos nombres cortos distintos:

SQLbase de datos: tienda

SELECT c.name AS category, parent.name AS parent
FROM categories c
LEFT JOIN categories parent ON parent.id = c.parent_id;

SELECT c.name AS category, parent.name AS parent FROM categories c LEFT JOIN categories parent ON parent.id = c.parent_id;

▶ EjecutarRestaurar la consultaBase nueva

c es la categoría, parent es la misma tabla vista como el padre. ON parent.id = c.parent_id dice: el padre es la fila cuyo id es igual al parent_id de la categoría. Esto se llama self join. Aquí los nombres cortos son obligatorios: sin ellos no se puede distinguir name de las dos copias.

Es un LEFT JOIN para que «Alimentos» y «Hogar», que no tienen padre, se queden.

Ejercicio

Dos tareas

1. El nuevo catálogo necesita los 25 productos con su categoría y su categoría padre. La tarjeta de regalo tiene que estar también en la lista.

El producto, su categoría y la categoría padre, para todos los productos:

SQLbase de datos: tienda

SELECT p.name AS product, cat.name AS category
FROM products p
JOIN categories cat ON cat.id = p.category_id;

SELECT p.name AS product, cat.name AS category FROM products p JOIN categories cat ON cat.id = p.category_id;

Comprobar▶ Ejecutar

2. ¿Cuál es la facturación de las dos categorías de arriba, «Alimentos» y «Hogar»? Sin los pedidos cancelados.

Facturación por categoría de arriba:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Más precisamente, para curiosos

El orden de los JOIN normales en una consulta no cambia el resultado: la base misma elige con qué tabla empezar. Con LEFT JOIN el orden importa, porque «izquierda» es todo lo que se ha armado antes.

Una tabla unida consigo misma da solo un nivel hacia arriba. Un árbol con muchos niveles (una subcategoría de una subcategoría) se recorre con WITH RECURSIVE, en el módulo de subconsultas.

Test

Ponte a prueba

1. Una consulta une orders, order_items, products y categories. ¿Cuántos JOIN … ON tiene?

DosTresCuatro

2. ¿Por qué son obligatorios los nombres cortos en FROM categories c LEFT JOIN categories parent …?

Porque LEFT JOIN los exigeNo son obligatoriosPorque la misma tabla participa dos veces, y sin ellos no se pueden distinguir c.name y parent.name

3. Un informe por categoría con JOIN categories pierde los productos sin categoría. ¿Cómo los conservas?

Con LEFT JOIN categories y COALESCE para el nombreCon WHERE category_id IS NOT NULLCon ORDER BY

4. El resultado tiene dos columnas name (la del cliente y la del producto). ¿Qué haces?

Quitas unaNada, la base las distinguiráLes pones nombre con AS: customer, product

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: GROUP BY: un informe por grupos · Clave primaria y clave foránea · JOIN: dos tablas en una consulta · LEFT JOIN: también las filas sin pareja
• Continúa en: UNION, INTERSECT, EXCEPT y CROSS JOIN · Un informe por pasos: WITH · Árboles y secuencias: WITH RECURSIVE · Vistas
• Ejercicios sobre el tema: Unir tablas