JOIN

La cláusula JOIN genera una tabla nueva al combinar columnas de una o varias tablas mediante valores comunes entre ellas. Es una operación habitual en las bases de datos con soporte para SQL, que se corresponde con la operación join del álgebra relacional. El caso especial de unir una tabla consigo misma suele denominarse "autounión".

Sintaxis

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

Las expresiones de la cláusula ON y las columnas de la cláusula USING se denominan "claves de JOIN". A menos que se indique lo contrario, un JOIN produce un producto cartesiano de las filas con "claves de JOIN" coincidentes, lo que puede generar resultados con muchas más filas que las tablas de origen.

Se admiten todos los tipos estándar de SQL JOIN:

• JOIN sin especificar un tipo implica INNER.
• La palabra clave OUTER puede omitirse sin problema.
• Una sintaxis alternativa para CROSS JOIN consiste en especificar varias tablas en la cláusula FROM, separadas por comas.
• Si no hay columnas coincidentes para un NATURAL JOIN, funciona como un CROSS JOIN.

Los tipos de join adicionales disponibles en ClickHouse son:

El tipo de JOIN predeterminado puede sobrescribirse mediante la configuración join_default_strictness.

El comportamiento del servidor ClickHouse en las operaciones ANY JOIN depende de la configuración any_join_distinct_right_table_keys.

Véase también

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

Use la configuración cross_to_inner_join_rewrite para definir el comportamiento cuando ClickHouse no consigue reescribir un CROSS JOIN como un INNER JOIN. El valor predeterminado es 1, lo que permite que el JOIN continúe, aunque será más lento. Establezca cross_to_inner_join_rewrite en 0 si desea que se genere un error, y en 2 si no quiere ejecutar los cross joins, sino forzar la reescritura de todos los comma/cross joins. Si la reescritura falla cuando el valor es 2, recibirá un mensaje de error que indica "Please, try to simplify WHERE section".

Una sección ON puede contener varias condiciones combinadas con los operadores AND y OR. Las condiciones que especifican claves de JOIN deben:

• hacer referencia tanto a la tabla izquierda como a la derecha
• usar el operador de igualdad

Las demás condiciones pueden usar otros operadores lógicos, pero deben hacer referencia a la tabla izquierda o a la derecha de una consulta.

Las filas se unen si se cumple toda la condición compuesta. Si no se cumplen las condiciones, las filas pueden seguir incluyéndose en el resultado según el tipo de JOIN. Tenga en cuenta que, si las mismas condiciones se colocan en una sección WHERE y no se cumplen, las filas siempre se excluyen del resultado.

El operador OR dentro de la cláusula ON funciona con el algoritmo hash join: para cada argumento OR con claves de JOIN para JOIN, se crea una tabla hash independiente, por lo que el consumo de memoria y el tiempo de ejecución de la consulta aumentan linealmente a medida que crece el número de expresiones OR en la cláusula ON.

Ejemplo

Considere table_1 y table_2:

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

Consulta con una condición sobre una clave de join y una condición adicional para table_2:

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

Ten en cuenta que el resultado contiene la fila con el nombre C y la columna de texto vacía. Se incluye en el resultado porque se usa un join de tipo OUTER.

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

Consulta con JOIN de tipo INNER y múltiples condiciones:

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

┌

Consulta con un join de tipo INNER y una condición con OR:

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Consulta con un join de tipo INNER y condiciones con OR y AND:

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

Actualmente, ClickHouse admite ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN con condiciones de desigualdad además de las condiciones de igualdad. Las condiciones de desigualdad solo se admiten con los algoritmos de join hash, parallel_hash y grace_hash. Una condición de no equivalencia evaluada durante el join no puede contener arrayJoin, ya que debe conservar el número de filas; las condiciones que se aplican a un solo lado y una clave de igualdad sobre arrayJoin se extraen antes del join y no se ven afectadas; una condición no disyuntiva de ALL INNER JOIN tampoco se ve afectada, ya que en ese caso se aplica después del join. Cuando la expansión depende de un solo lado, muévala a un ARRAY JOIN en una subconsulta antes del join; una condición cuyo argumento de arrayJoin lee columnas de ambos lados debe reestructurarse.

Ejemplo

Tabla t1:

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

Tabla t2

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

NULL no es igual a ningún valor, incluido él mismo. Esto significa que, si una clave de JOIN tiene un valor NULL en una tabla, no coincidirá con un valor NULL de la otra tabla.

Ejemplo

Tabla A:

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

Tabla B:

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

Observe que la fila con Charlie de la tabla A y la fila con puntuación 88 de la tabla B no aparecen en el resultado debido al valor NULL en la clave del JOIN.

Si quiere hacer coincidir valores NULL, use la función isNotDistinctFrom para comparar las claves del JOIN.

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

Los valores NaN en claves JOIN de tipo float no siguen la regla de NULL anterior. La comparación escalar de dos valores NaN (NaN = NaN) da como resultado 0; sin embargo, las claves de JOIN no se comparan con semántica escalar, por lo que las claves NaN pueden coincidir. Que realmente coincidan es un detalle de implementación y depende del algoritmo de join, el tipo de clave y la configuración de sesión. No dependa de un comportamiento específico. Si necesita que las filas con NaN no coincidan, asígnelas a valores NULL: ON if(isNaN(A.id), NULL, A.id) = B.id. En un ASOF JOIN, la columna de coincidencia más próxima se compara mediante ordenación, algo que NaN no admite; filtre esas filas en ambos lados.

ASOF JOIN es útil cuando necesitas unir registros que no tienen una correspondencia exacta.

Este algoritmo de JOIN requiere una columna especial en las tablas. Esta columna:

• Debe contener una secuencia ordenada.
• Puede ser de uno de los siguientes types: Int, UInt, Float, Date, DateTime, Decimal.
• En el caso del algoritmo de join hash, no puede ser la única columna de la cláusula JOIN.

Sintaxis ASOF JOIN ... ON:

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

Puede usar cualquier número de condiciones de igualdad y exactamente una condición de coincidencia más próxima. Por ejemplo, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t.

Condiciones admitidas para la coincidencia más próxima: >, >=, <, <=.

Sintaxis de ASOF JOIN ... USING:

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

ASOF JOIN usa equi_columnX para unir por igualdad y asof_column para unir por la coincidencia más cercana con la condición table_1.asof_column >= table_2.asof_column. La columna asof_column siempre es la última de la cláusula USING.

Por ejemplo, considere las siguientes tablas:

         table_1                           table_2
      event   | ev_time | user_id       event   | ev_time | user_id
    ----------|---------|----------   ----------|---------|----------
                  ...                               ...
    event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
                  ...                 event_2_2 |  12:30  |   42
    event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
                  ...                               ...

ASOF JOIN puede tomar el timestamp de un evento de usuario de table_1 y encontrar un evento en table_2 cuyo timestamp sea el más cercano al del evento de table_1 que corresponda a la condición de coincidencia más próxima. Si existen, los valores de timestamp iguales son los más cercanos. Aquí, la columna user_id puede usarse para hacer el JOIN por igualdad y la columna ev_time para hacer el JOIN por coincidencia más próxima. En nuestro ejemplo, event_1_1 puede unirse con event_2_1 y event_1_2 puede unirse con event_2_3, pero event_2_2 no puede unirse.

El resultado de PASTE JOIN es una tabla que contiene todas las columnas de la subconsulta izquierda, seguidas de todas las columnas de la subconsulta derecha. Las filas se emparejan según su posición en las tablas originales (el orden de las filas debe estar definido). Si las subconsultas devuelven un número distinto de filas, las filas sobrantes se descartan.

Ejemplo:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌

Nota: en este caso, el resultado puede no ser determinista si la lectura es paralela. Por ejemplo:

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌

Hay dos formas de ejecutar un JOIN que implica tablas distribuidas:

• Al usar un JOIN normal, la consulta se envía a los servidores remotos. Las subconsultas se ejecutan en cada uno de ellos para crear la tabla del lado derecho, y el JOIN se realiza con esa tabla. En otras palabras, la tabla del lado derecho se forma por separado en cada servidor.
• Al usar GLOBAL ... JOIN, primero el servidor solicitante ejecuta una subconsulta para calcular uno de los lados del JOIN y recopila el resultado en una tabla temporal. Esta tabla temporal se envía luego a cada servidor remoto, y las consultas se ejecutan en ellos usando los datos temporales transmitidos. En los JOIN LEFT e INNER, la tabla del lado derecho se calcula como la subconsulta. En los JOIN RIGHT, en cambio, se calcula la tabla del lado izquierdo, ya que la tabla del lado derecho es la que se conserva y debe leerse desde los segmentos.

Tenga cuidado al usar GLOBAL. Para obtener más información, consulte la sección Subconsultas distribuidas.

Las consultas INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN admiten la conversión implícita de tipos para las "claves de JOIN". Sin embargo, la consulta no puede ejecutarse si las claves de JOIN de las tablas izquierda y derecha no pueden convertirse a un único tipo (por ejemplo, no existe ningún tipo de dato que pueda contener todos los valores de UInt64 e Int64, o de String e Int32).

Ejemplo

Considere la tabla t_1:

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

y la tabla t_2:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

La consulta

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b);

devuelve el conjunto:

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

Al unir tablas, pueden aparecer celdas vacías. La configuración join_use_nulls define cómo ClickHouse completa estas celdas.

Si las claves de JOIN son campos Nullable, las filas en las que al menos una de las claves tiene el valor NULL no se unen.

Las columnas especificadas en USING deben tener los mismos nombres en ambas subconsultas, y las demás columnas deben llamarse de forma diferente. Puede usar alias para cambiar los nombres de las columnas en las subconsultas.

La cláusula USING especifica una o varias columnas para el join, lo que establece la igualdad entre ellas. La lista de columnas se indica sin paréntesis. No se admiten condiciones de join más complejas.

Para varias cláusulas JOIN en una sola consulta SELECT:

• Seleccionar todas las columnas con * solo está disponible cuando se unen tablas, no subconsultas.
• La cláusula PREWHERE no está disponible.
• La cláusula USING no está disponible.

Para las cláusulas ON, WHERE y GROUP BY:

• No se pueden usar expresiones arbitrarias en las cláusulas ON, WHERE y GROUP BY, pero puede definir una expresión en una cláusula SELECT y luego usarla en estas cláusulas mediante un alias.

Al ejecutar un JOIN, no se optimiza el orden de ejecución con respecto a otras etapas de la consulta. El JOIN (una búsqueda en la tabla de la derecha) se ejecuta antes del filtrado en WHERE y antes de la agregación.

Cada vez que se ejecuta una consulta con el mismo JOIN, la subconsulta se vuelve a ejecutar porque el resultado no se almacena en caché. Para evitarlo, use el motor de tabla especial Join, que es un array preparado para operaciones de join y que siempre está en RAM.

En algunos casos, es más eficiente usar IN en lugar de JOIN.

Si necesita un JOIN para unir tablas de dimensiones (tablas relativamente pequeñas que contienen propiedades de dimensión, como nombres de campañas publicitarias), puede que JOIN no sea la opción más adecuada, ya que se vuelve a acceder a la tabla de la derecha en cada consulta. Para esos casos, existe la funcionalidad de "diccionarios", que debería usar en lugar de JOIN. Para obtener más información, consulte la sección Diccionarios.

De forma predeterminada, ClickHouse usa el algoritmo hash join. ClickHouse toma la right_table y crea una tabla hash para ella en la RAM. Si join_algorithm = 'auto' está habilitado, al superar cierto umbral de consumo de memoria, ClickHouse cambia al algoritmo merge join. Para ver la descripción de los algoritmos JOIN, consulte la configuración join_algorithm.

Si necesita restringir el consumo de memoria de la operación JOIN, use la siguiente configuración:

• max_rows_in_join — Limita el número de filas de la tabla hash.
• max_bytes_in_join — Limita el tamaño de la tabla hash.

Cuando se alcanza cualquiera de estos límites, ClickHouse actúa según lo que indique la configuración join_overflow_mode.

Ejemplo:

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

• Blog: ClickHouse: un DBMS rapidísimo con soporte completo para SQL JOIN - Parte 1
• Blog: ClickHouse: un DBMS rapidísimo con soporte completo para SQL JOIN - Por dentro - Parte 2
• Blog: ClickHouse: un DBMS rapidísimo con soporte completo para SQL JOIN - Por dentro - Parte 3
• Blog: ClickHouse: un DBMS rapidísimo con soporte completo para SQL JOIN - Por dentro - Parte 4