JOIN estándar de SQL, lo que permite realizar análisis de datos de forma eficiente.
En esta guía, explorarás algunos de los tipos de JOIN más comunes y cómo utilizarlos con ayuda de diagramas de Venn y consultas de ejemplo sobre un conjunto de datos IMDB normalizado, procedente del repositorio de conjuntos de datos relacionales.
Datos y recursos de prueba
movie_id de una fila de la tabla genres contiene el valor id de una fila de la tabla movies.
Existe una relación de muchos a muchos entre películas y actores.
Esta relación de muchos a muchos se normaliza en dos relaciones de uno a muchos mediante la tabla roles.
Cada fila de la tabla roles contiene los valores de las columnas id de la tabla movies y de la tabla actors.
Tipos de JOIN compatibles con ClickHouse
INNER JOIN
INNER JOIN devuelve, para cada par de filas que coincide en las claves de unión, los valores de las columnas de la fila de la tabla izquierda combinados con los valores de las columnas de la fila de la tabla derecha.
Si una fila tiene más de una coincidencia, se devuelven todas las coincidencias (es decir, se produce el producto cartesiano para las filas con claves de unión coincidentes).
Esta consulta encuentra los géneros de cada película uniendo la tabla movies con la tabla genres:
La palabra clave
INNER puede omitirse.INNER JOIN puede ampliarse o modificarse mediante alguno de los siguientes tipos de join.
(LEFT / RIGHT / FULL) OUTER JOIN
LEFT OUTER JOIN se comporta como INNER JOIN; además, para las filas de la tabla izquierda que no tienen coincidencia, ClickHouse devuelve valores predeterminados para las columnas de la tabla derecha.
Una consulta RIGHT OUTER JOIN es similar y también devuelve valores de las filas sin coincidencia de la tabla derecha, junto con valores predeterminados para las columnas de la tabla izquierda.
Una consulta FULL OUTER JOIN combina LEFT y RIGHT OUTER JOIN y devuelve valores de las filas sin coincidencia de las tablas izquierda y derecha, junto con valores predeterminados para las columnas de las tablas derecha e izquierda, respectivamente.
ClickHouse puede configurarse para devolver NULLs en lugar de valores predeterminados (aunque, por motivos de rendimiento, es menos recomendable).
movies que no tienen coincidencias en la tabla genres y que, por lo tanto, reciben (durante la consulta) el valor predeterminado 0 para la columna movie_id:
Se puede omitir la palabra clave
OUTER.CROSS JOIN
CROSS JOIN genera el producto cartesiano completo de las dos tablas sin tener en cuenta las claves de unión.
Cada fila de la tabla izquierda se combina con cada fila de la tabla derecha.
Por lo tanto, la siguiente consulta combina cada fila de la tabla movies con cada fila de la tabla genres:
WHERE para asociar las filas coincidentes y replicar el comportamiento de INNER JOIN para obtener los géneros de cada película:
CROSS JOIN especifica varias tablas en la cláusula FROM, separadas por comas.
ClickHouse reescribe un CROSS JOIN como un INNER JOIN si hay expresiones de join en la sección WHERE de la consulta.
Puede comprobarlo en la consulta de ejemplo mediante EXPLAIN SYNTAX (que devuelve la versión sintácticamente optimizada a la que se reescribe una consulta antes de ejecutarse):
INNER JOIN en la versión de la consulta CROSS JOIN optimizada sintácticamente contiene la palabra clave ALL, que se añadió explícitamente para mantener la semántica del producto cartesiano de CROSS JOIN incluso al reescribirse como INNER JOIN, para el cual el producto cartesiano puede deshabilitarse.
OUTER puede omitirse en un RIGHT OUTER JOIN y se puede añadir la palabra clave opcional ALL, por lo que puede escribir ALL RIGHT JOIN y funcionará perfectamente.
(LEFT / RIGHT) SEMI JOIN
LEFT SEMI JOIN devuelve los valores de las columnas de cada fila de la tabla izquierda que tenga al menos una coincidencia de clave de join en la tabla derecha.
Solo se devuelve la primera coincidencia encontrada (el producto cartesiano está deshabilitado).
Una consulta RIGHT SEMI JOIN es similar y devuelve valores para todas las filas de la tabla derecha con al menos una coincidencia en la tabla izquierda, pero solo se devuelve la primera coincidencia encontrada.
Esta consulta encuentra todos los actores y actrices que participaron en una película en 2023.
Ten en cuenta que, con un join normal (INNER), el mismo actor o actriz aparecería más de una vez si hubiera tenido más de un papel en 2023:
(LEFT / RIGHT) ANTI JOIN
LEFT ANTI JOIN devuelve los valores de las columnas de todas las filas sin coincidencia de la tabla izquierda.
De forma similar, RIGHT ANTI JOIN devuelve los valores de las columnas de todas las filas sin coincidencia de la tabla derecha.
Una formulación alternativa de la consulta de ejemplo anterior con outer join consiste en usar un anti join para encontrar películas que no tienen ningún género en el conjunto de datos:
(LEFT / RIGHT / INNER) ANY JOIN
LEFT ANY JOIN es la combinación de LEFT OUTER JOIN + LEFT SEMI JOIN, lo que significa que ClickHouse devuelve los valores de las columnas de cada fila de la tabla izquierda, ya sea combinados con los valores de las columnas de una fila coincidente de la tabla derecha o con los valores predeterminados de las columnas de la tabla derecha si no existe ninguna coincidencia.
Si una fila de la tabla izquierda tiene más de una coincidencia en la tabla derecha, ClickHouse solo devuelve los valores de las columnas combinadas de la primera coincidencia encontrada (el producto cartesiano está deshabilitado).
De forma similar, RIGHT ANY JOIN es la combinación de RIGHT OUTER JOIN + RIGHT SEMI JOIN.
Y INNER ANY JOIN es un INNER JOIN con el producto cartesiano deshabilitado.
El siguiente ejemplo muestra LEFT ANY JOIN mediante un ejemplo abstracto que utiliza dos tablas temporales (left_table y right_table) creadas con la values función de tabla:
RIGHT ANY JOIN:
INNER ANY JOIN:
ASOF JOIN
ASOF JOIN permite realizar coincidencias no exactas.
Si una fila de la tabla izquierda no tiene una coincidencia exacta en la tabla derecha, se usa en su lugar la fila más cercana de la tabla derecha.
Esto resulta especialmente útil para análisis de series temporales y puede reducir drásticamente la complejidad de la consulta.
El siguiente ejemplo realiza un análisis de series temporales sobre datos del mercado bursátil.
Una tabla quotes contiene cotizaciones de símbolos bursátiles en momentos específicos del día.
En los datos de ejemplo, el precio se actualiza cada 10 segundos.
Una tabla trades enumera operaciones sobre símbolos: se compró un volumen concreto de un símbolo en un momento determinado:
Para calcular el coste real de cada operación, necesitamos hacer coincidir las operaciones con el instante de cotización más cercano.
Esto es sencillo y conciso con ASOF JOIN, donde se usa la cláusula ON para especificar una condición de coincidencia exacta y la cláusula AND para especificar la condición de coincidencia más cercana: para un símbolo concreto (coincidencia exacta), se busca la fila con el tiempo “más cercano” de la tabla quotes en el mismo instante o antes del momento (coincidencia no exacta) de una operación de ese símbolo:
La cláusula
ON de ASOF JOIN es obligatoria y especifica una condición de coincidencia exacta junto con la condición de coincidencia no exacta de la cláusula AND.