JOIN: dos tablas en una consulta

En resumen: JOIN … ON combina una fila de una tabla con la fila de otra tabla a la que apunta su clave foránea, por ejemplo un pedido con su cliente, y conserva solo las filas que tienen pareja.

Problema

Pedidos con nombres

La empresa de mensajería quiere una lista: número de pedido, fecha y nombre del cliente. El número y la fecha están en orders, el nombre está en customers.

En la última lección lo hicimos con dos consultas y pasamos el número de una a otra a mano. Con 60 pedidos son 60 búsquedas. Necesitamos una consulta que encuentre sola el cliente de cada pedido.

La idea

JOIN … ON

JOIN pone junto a cada fila de una tabla la fila correspondiente de la otra. ON dice qué fila corresponde a cuál:

SQLbase de datos: tienda

SELECT orders.id, orders.ordered_at, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

SELECT orders.id, orders.ordered_at, customers.name FROM orders JOIN customers ON customers.id = orders.customer_id;

▶ EjecutarRestaurar la consultaBase nueva

Para cada pedido la base busca el cliente cuyo customers.id es igual a orders.customer_id, y los pega en una sola fila. Por eso las columnas se escriben con el nombre de la tabla delante: orders.id, customers.name.

Salen 60 filas, tantas como pedidos: cada pedido tiene exactamente un cliente. Esto se llama JOIN (o INNER JOIN): solo quedan los pares que se encuentran. Teodora y Yavor no están en la lista porque no tienen pedidos. Volveremos a ellos en la próxima lección.

En tablas pequeñas se puede ver cómo ocurre: cuatro pedidos y tres clientes, y Teodora no tiene pedidos.

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Paso a paso, en palabras

1. 4 pedidos y 3 clientes. El customer_id del pedido es el id de su cliente.
2. El pedido 1 tiene customer_id 2. La base busca al cliente con id 2: Georgi Petrov. El par se convierte en una fila.
3. Pedido 2: customer_id 1, es Maria Ivanova.
4. El pedido 3 es otra vez del cliente 1: Maria Ivanova está en el resultado por segunda vez.
5. El pedido 4 es otra vez del cliente 2: Georgi Petrov está en el resultado por segunda vez.
6. Teodora Genova (id 3) no es el customer_id de ningún pedido. No hay par, por eso no está en el resultado.
7. 4 filas, tantas como pedidos. JOIN conserva solo los pares que se encuentran.

La misma consulta sobre estas tablas pequeñas. Ejecútala y compárala con el último paso:

SQLbase de datos vacía

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

SELECT orders.id, orders.customer_id, customers.name FROM orders JOIN customers ON customers.id = orders.customer_id;

▶ EjecutarRestaurar la consultaBase nueva

La idea

Nombres cortos

orders.ordered_at y customers.name se hacen largos. A una tabla se le puede dar un nombre corto para la consulta: lo escribes después del nombre de la tabla en FROM y en JOIN.

SQLbase de datos: tienda

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;

▶ EjecutarRestaurar la consultaBase nueva

La misma consulta, el mismo resultado. o es orders, c es customers. El nombre corto vale solo dentro de esta consulta. También puedes escribir FROM orders AS o.

Pruébalo

Los pedidos nuevos

Muestra el número de los pedidos nuevos (estado new) con el nombre y el correo del cliente, para poder enviar una confirmación.

SQLbase de datos: tienda

SELECT id, customer_id FROM orders WHERE status = 'new';

SELECT id, customer_id FROM orders WHERE status = 'new';

Comprobar▶ Ejecutar

Míralo

«ambiguous column name»

Aquí hay una consulta sin nombres de tabla. Ejecútala:

SQLbase de datos: tienda

SELECT id, name, ordered_at
FROM orders
JOIN customers ON customer_id = id;

SELECT id, name, ordered_at FROM orders JOIN customers ON customer_id = id;

▶ EjecutarRestaurar la consultaBase nueva

Tanto orders como customers tienen una columna id. Después de un JOIN las columnas de las dos están juntas, y la base no sabe a qué id te refieres. «Ambiguous» significa ambiguo: se puede leer de dos maneras. La columna name existe solo en customers, así que de esa no se queja.

La solución es decir de qué tabla es la columna:

SQLbase de datos: tienda

SELECT o.id, c.name, o.ordered_at
FROM orders o
JOIN customers c ON c.id = o.customer_id;

SELECT o.id, c.name, o.ordered_at FROM orders o JOIN customers c ON c.id = o.customer_id;

▶ EjecutarRestaurar la consultaBase nueva

Una buena costumbre es poner el nombre corto delante de cada columna en una consulta con JOIN, aunque no se exija. Quien la lea ve enseguida de dónde viene cada columna.

La idea

JOIN, WHERE y ORDER BY

Después de un JOIN, todo lo que conoces funciona como antes, y las condiciones pueden usar columnas de las dos tablas. Los pedidos de los clientes de Varna, por fecha:

SQLbase de datos: tienda

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Varna'
ORDER BY o.ordered_at;

SELECT o.id, o.ordered_at, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.city = 'Varna' ORDER BY o.ordered_at;

▶ EjecutarRestaurar la consultaBase nueva

La ciudad está en customers, la fecha en orders. El orden de las partes es: SELECT … FROM … JOIN … ON … WHERE … ORDER BY.

Pruébalo

Qué hay en el pedido 60

Muestra el nombre del producto, la cantidad y el precio de cada línea del pedido 60.

SQLbase de datos: tienda

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

Comprobar▶ Ejecutar

Ejercicio

Dos tareas

1. La tienda está investigando por qué se cancelan pedidos. Muestra el número de los pedidos cancelados (cancelled) con el nombre y la ciudad del cliente.

Los pedidos cancelados con el cliente y su ciudad:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. El equipo de calidad quiere las reseñas con puntuación de 3 o menos junto con el nombre del producto, para saber qué revisar.

Las puntuaciones bajas: el producto, la puntuación y el texto de la reseña:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Más precisamente, para curiosos

También te encontrarás con la forma más antigua: las tablas en FROM separadas por una coma, y la condición para los pares en WHERE.

SQLbase de datos: tienda

SELECT o.id, c.name
FROM orders o, customers c
WHERE c.id = o.customer_id;

SELECT o.id, c.name FROM orders o, customers c WHERE c.id = o.customer_id;

▶ EjecutarRestaurar la consultaBase nueva

El resultado es el mismo. La forma JOIN … ON mantiene la condición de unión separada de las condiciones de filtrado. Por eso el curso escribe JOIN.

Test

Ponte a prueba

1. ¿Qué dice ON c.id = o.customer_id?

Cómo ordenar las filasQué fila de customers corresponde a qué fila de ordersQué columnas mostrar

2. ¿Por qué SELECT id FROM orders JOIN customers … da «ambiguous column name: id»?

Porque id es una palabra reservadaPorque las dos tablas tienen una columna id y la base no sabe cuál quieresPorque id no se puede mostrar después de un JOIN

3. En FROM orders o JOIN customers c, ¿qué es o?

Una columnaUna copia nueva de la tablaUn nombre corto para orders en esta consulta

4. ¿Dónde va WHERE en una consulta con JOIN?

Después de JOIN … ON y antes de ORDER BYAntes de JOINAl final, después de ORDER BY

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: WHERE: filtrar filas · NULL: sin valor · ORDER BY, LIMIT y DISTINCT · Clave primaria y clave foránea
• Continúa en: LEFT JOIN: también las filas sin pareja · Muchas tablas, y una tabla unida consigo misma · UNION, INTERSECT, EXCEPT y CROSS JOIN · Subconsultas
• Ejercicios sobre el tema: Unir tablas