Un join construye filas a partir de dos tablas, emparejadas según una condición: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) conserva solo las filas que coinciden, LEFT JOIN conserva todas las filas de la primera tabla y FULL JOIN todas las filas de ambas.
Salen tres filas. Grace no tiene pedidos, y el pedido 13 pertenece a un cliente que no existe, así que los dos quedan fuera. Los joins de abajo usan las mismas dos tablas.
Tipos de join
| Join | Filas que devuelve |
|---|---|
INNER JOIN | Los pares que cumplen la condición |
LEFT JOIN | Todas las filas de la izquierda; columnas de la derecha en NULL donde nada coincide |
RIGHT JOIN | Todas las filas de la derecha; el reflejo de LEFT JOIN |
FULL JOIN | Todas las filas de ambos lados, con NULLs rellenando los huecos |
CROSS JOIN | Cada fila de la izquierda emparejada con cada fila de la derecha |
LEFT JOIN: conservar filas sin coincidencia
Grace aparece con 0 pedidos. Cuenta o.id, no *: count(*) contaría como 1 la fila de NULLs de Grace.
- Filas sin coincidencia (un anti join): añade
WHERE o.id IS NULLpara quedarte solo con los clientes sin pedidos.WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)hace lo mismo y deja clara la intención. - ON vs WHERE: una condición sobre la tabla de la derecha va en el
ON.LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20descarta a Grace, porque su totalNULLno pasa elWHERE. El join se ha convertido en un inner join. EscribeON o.customer_id = c.id AND o.total > 20para conservar a todos los clientes.
FULL JOIN: filas que faltan en cualquiera de los lados
FULL JOIN encuentra desajustes en las dos direcciones, algo útil al comparar dos listas:
El resultado tiene a Grace sin pedido y el pedido 13 sin cliente. Una clave foránea en orders.customer_id habría impedido la segunda fila.
CROSS JOIN: todas las combinaciones
CROSS JOIN no tiene condición. Empareja cada fila de un lado con cada fila del otro, así que 3 tallas y 2 colores dan 6 filas:
Las dos listas aquí son listas VALUES con nombre de tabla. Con tablas reales se escribe FROM sizes CROSS JOIN colors, y FROM sizes, colors hace lo mismo. Un join cuya condición ON siempre es verdadera multiplica las filas de la misma forma, una causa habitual de una consulta que de repente devuelve muchísimas filas.
LATERAL: una subconsulta por cada fila
Una subconsulta en el FROM normalmente no puede ver las demás tablas del mismo FROM. Con LATERAL sí puede, y se evalúa una vez por cada fila de la tabla anterior. Eso resuelve el "top N por grupo":
Ada obtiene sus dos pedidos más grandes, Linus el único que tiene, y Grace una fila de NULLs gracias a LEFT JOIN ... ON true. CROSS JOIN LATERAL descartaría a los clientes sin pedidos. Con un índice en orders (customer_id, total), cada subconsulta lee solo unas pocas entradas del índice. Para una fila por grupo, DISTINCT ON suele ser más corto.
Preguntas frecuentes
¿JOIN es lo mismo que INNER JOIN en PostgreSQL?
Sí. JOIN a secas significa INNER JOIN, y LEFT JOIN significa LEFT OUTER JOIN; las palabras INNER y OUTER son opcionales.
¿Por qué mi LEFT JOIN devuelve más filas que la tabla de la izquierda?
Una fila de la izquierda aparece una vez por cada fila de la derecha que coincide. Un cliente con tres pedidos aparece tres veces. Agrega con GROUP BY, o haz el join con una subconsulta que ya tenga una fila por clave.
¿Qué diferencia hay entre ON y USING?
ON admite cualquier condición. USING (id) es una forma corta para columnas iguales con el mismo nombre en las dos tablas, y la salida tiene una sola columna id en vez de dos.
¿Importa el orden de los joins para el rendimiento en PostgreSQL?
Normalmente no. El planificador reordena los inner joins para encontrar un plan barato cuando la consulta tiene hasta join_collapse_limit tablas (8 por defecto). Los outer joins solo se pueden reordenar en algunos casos, porque moverlos puede cambiar el resultado.