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.

Tipos de unión compatibles

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

Tipo	Descripción
`INNER JOIN`	solo se devuelven las filas coincidentes.
`LEFT OUTER JOIN`	además de las filas coincidentes, se devuelven las filas no coincidentes de la tabla izquierda.
`RIGHT OUTER JOIN`	además de las filas coincidentes, se devuelven las filas no coincidentes de la tabla derecha.
`FULL OUTER JOIN`	además de las filas coincidentes, se devuelven las filas no coincidentes de ambas tablas.
`CROSS JOIN`	produce el producto cartesiano de las tablas completas; no se especifican “join keys”.
`NATURAL JOIN`	une automáticamente todas las columnas con el mismo nombre en ambas tablas; cada columna común aparece una sola vez en el resultado. Admite las variantes `INNER` (predeterminada), `LEFT`, `RIGHT` y `FULL`. Equivale a `JOIN ... USING (col1, col2, ...)`, donde la lista de columnas se obtiene automáticamente.

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 unión adicionales disponibles en ClickHouse son:

Tipo	Descripción
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	Una lista de permitidos sobre “join keys”, sin generar un producto cartesiano.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	Una lista de bloqueados sobre “join keys”, sin generar un producto cartesiano.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	Desactiva parcial (para el lado opuesto de `LEFT` y `RIGHT`) o completamente (para `INNER` y `FULL`) el producto cartesiano de los tipos estándar de `JOIN`.
`ASOF JOIN`, `LEFT ASOF JOIN`	Une secuencias con una coincidencia no exacta. El uso de `ASOF JOIN` se describe a continuación.
`PASTE JOIN`	Realiza una concatenación horizontal de dos tablas.

Al usar el analizador, si se deshabilita la configuración semi_join_include_columns_from_both_sides o anti_join_include_columns_from_both_sides, la unión correspondiente solo expone su lado preservado a las expresiones que se resuelven una vez formado el resultado de la unión.

LEFT SEMI JOIN y LEFT ANTI JOIN solo exponen las columnas del lado izquierdo.
RIGHT SEMI JOIN y RIGHT ANTI JOIN solo exponen las columnas del lado derecho.
Esto afecta a cláusulas como SELECT, PREWHERE, WHERE, GROUP BY, HAVING, QUALIFY, ORDER BY y LIMIT BY, incluidos los comodines calificados como t1.*.
La expresión ON del mismo JOIN puede seguir haciendo referencia a ambos lados.

Cuando estas configuraciones están habilitadas (opción predeterminada), ClickHouse conserva el comportamiento heredado: ambos lados siguen siendo accesibles y SELECT * expande las columnas de ambas tablas.

Cuando join_algorithm se establece en partial_merge, RIGHT JOIN y FULL JOIN solo son compatibles con estrictez ALL (SEMI, ANTI, ANY y ASOF no son compatibles).

LATERAL JOIN

JOIN LATERAL permite que la subconsulta situada en el lado derecho de una unión haga referencia a columnas de las expresiones de tabla de su lado izquierdo; la subconsulta se evalúa para cada combinación distinta de los valores de columna del lado izquierdo a los que hace referencia, y su resultado se une a cada fila del lado izquierdo que presente esa combinación:

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

El predicado ON true es obligatorio, al igual que en cualquier otro INNER o LEFT JOIN; omitirlo produce un error de sintaxis. Es una funcionalidad experimental y está deshabilitada de forma predeterminada; habilítela con la configuración allow_experimental_lateral_join. Por ahora solo se admite el siguiente subconjunto; cualquier otra cosa se rechaza con un error:

Solo INNER JOIN LATERAL y LEFT JOIN LATERAL; las uniones RIGHT, FULL, PASTE y NATURAL no son compatibles, y LATERAL no puede combinarse en ningún caso con una unión CROSS o con coma.
Solo la estrictez predeterminada ALL; ANY, SEMI, ANTI y ASOF no son compatibles.
No se admite ningún predicado de unión distinto de ON true (también se acepta ON 1); USING no es compatible y el predicado no puede omitirse. Coloque los filtros que relacionan ambos lados en la cláusula WHERE de la subconsulta lateral.
Los modificadores de unión GLOBAL y LOCAL no son compatibles.
La subconsulta lateral debe hacer referencia al menos a una columna del lado izquierdo. Para una subconsulta no correlacionada, use una unión normal.
La subconsulta lateral se evalúa una vez por cada valor distinto de las columnas del lado izquierdo a las que hace referencia, no una vez por cada fila izquierda, por lo que no debe contener funciones que no sean deterministas dentro de una consulta, como rand o generateUUIDv4, ni funciones de tabla que generen filas aleatorias, como generateRandom. Sí se permiten las funciones que son constantes dentro de una consulta, como now.
Solo se admite una subconsulta como expresión de tabla lateral. Las formas de origen de tabla de PostgreSQL LATERAL unnest(...) y CROSS JOIN UNNEST(...) no son compatibles; en su lugar, use la cláusula ARRAY JOIN.
Los GROUP BY y ORDER BY de la subconsulta lateral se ejecutan una sola vez sobre el conjunto de todas las evaluaciones, por lo que los límites max_rows_to_group_by, max_rows_to_sort y max_bytes_to_sort cuentan las filas de todas las evaluaciones, no las de una sola. Solo se admiten con el modo de desbordamiento throw; any y break se rechazan.
Las filas de todas las evaluaciones se emparejan con las filas izquierdas mediante una única unión. Como en cualquier hash unión, max_rows_in_join y max_bytes_in_join limitan el lado de esta unión que se mantiene en memoria, y es el planificador quien elige ese lado: con la configuración predeterminada (correlated_subqueries_use_in_memory_buffer = 1) es el lado izquierdo de JOIN LATERAL, ya que las filas izquierdas deben leerse por completo antes de evaluar la subconsulta lateral; de lo contrario, pueden ser los resultados de todas las evaluaciones en conjunto. Los límites siempre se aplican como si join_overflow_mode fuera throw: con break, la unión descartaría silenciosamente filas izquierdas no relacionadas.

Ejemplo

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS allow_experimental_lateral_join = 1;

Configuración

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

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”.

Condiciones de la sección `ON`

Una sección ON puede contener varias condiciones combinadas con los operadores AND y OR. Las condiciones que especifican join keys 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 y pueden hacer referencia a la tabla izquierda de una consulta, a la derecha o a ambas. 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 unión: para cada argumento OR con join keys 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.

Solo el operador de igualdad (=) convierte una condición en una join key. Se admiten otros operadores entre columnas de distintas tablas, pero si no hay ninguna igualdad, la unión tiene que examinar cada par de filas y resulta mucho más lento; consulte Unión con una condición ON arbitraria.

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 join key y una condición adicional para table_2:

Query

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 una unión de tipo OUTER.

Response

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

Consulta con unión de tipo INNER y múltiples condiciones:

Query

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');

Response

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

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

Query

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;

Response

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

Consulta con una unión de tipo INNER y condiciones con OR y AND:

Una condición de desigualdad que usa columnas de una sola tabla, como t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, se aplica únicamente a esa tabla: t1.b > 0 usa únicamente columnas de t1 y t2.b > t2.c usa únicamente columnas de t2. También se admiten condiciones de desigualdad que comparan columnas de tablas diferentes, como t1.a = t2.key AND t1.b > t2.key; consulte las secciones siguientes para obtener más información.

Query

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

Response

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

Unión con condiciones de desigualdad para columnas de diferentes tablas

ClickHouse admite ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN con condiciones de desigualdad además de las condiciones de igualdad. Cuando la sección ON contiene, junto a la desigualdad, una igualdad entre las dos tablas, la igualdad actúa como join key y la desigualdad se comprueba sobre las filas que esta empareja. Este tipo de condición mixta solo se ejecuta con los algoritmos de unión hash, parallel_hash y grace_hash. Cuando la sección ON no contiene ninguna igualdad entre las dos tablas, no hay ninguna join key con la que emparejar filas. Este tipo de unión se ejecuta mediante ie_join cuando la condición es un par de desigualdades o, en caso contrario, como una block nested loop unión. Una condición evaluada durante la unión no puede contener arrayJoin, ya que debe conservar el número de filas; en su lugar, utilice ARRAY JOIN en una subconsulta. 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

Unión con una condición ON arbitraria

La sección ON puede ser cualquier expresión boolean sobre las columnas de ambas tablas, como una comprobación de rango, una comparación aritmética o una llamada a una función escalar. Si no contiene ninguna igualdad entre las dos tablas, no hay ninguna join key con la que emparejar las filas. Un par de condiciones de desigualdad se ejecuta mediante ie_join cuando ese algoritmo figura en join_algorithm, como sucede de forma predeterminada. Cualquier otra condición se ejecuta como una block nested loop unión: la tabla derecha se materializa y la condición se evalúa para cada par de filas. Un ALL INNER JOIN equivale a un CROSS JOIN con la condición aplicada como filtro. La block nested loop unión admite todos los tipos de unión y niveles de estrictez, salvo ASOF JOIN, PASTE JOIN y ANY FULL JOIN. No figura entre los valores de join_algorithm: es el último recurso y solo se utiliza cuando ningún otro algoritmo puede ejecutar la condición. En la salida de EXPLAIN aparece como un paso BlockNestedLoopJoin. Cuando allow_block_nested_loop_join está deshabilitado, cualquier consulta que lo necesite se rechaza con INVALID_JOIN_ON_EXPRESSION. Ejemplo

Query

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

Response

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

La fila que no coincidió con nada se rellena según join_use_nulls, como en cualquier otra unión: con un valor predeterminado en el ejemplo anterior y con NULL cuando la configuración está habilitada. Estrictez ANY y SEMI normalmente conservan una fila por cada grupo de filas que comparten una join key. Aquí no existe tal grupo, por lo que conservan una fila por cada fila de la tabla que dirige la unión: LEFT ANY y LEFT SEMI emiten cada fila izquierda como máximo una vez, y RIGHT ANY y RIGHT SEMI, cada fila derecha como máximo una vez. ANY INNER limita ambos lados a la vez: cada fila de cualquiera de las dos tablas se usa como máximo una vez, por lo que el resultado no puede tener más filas que la tabla más pequeña. Qué pares forman dicho resultado es arbitrario, tal como implica ANY, y puede variar entre dos ejecuciones de la misma consulta. En el caso de ANY INNER, esto se extiende también al número de filas: al emparejar de forma voraz cada fila de ambos lados, en el orden en que se examinen las filas, puede quedar un número distinto de filas sin emparejar, por lo que el resultado de count() sobre una unión ANY INNER sin join key no es reproducible y depende del número de threads y del orden físico de la tabla derecha. Rendimiento Se examina cada par de filas, por lo que el trabajo crece con el producto del número de filas de ambas tablas y no con su suma. Por ello, una block nested loop unión es varios órdenes de magnitud más costosa que una hash unión sobre los mismos datos, y la diferencia aumenta a medida que crecen las tablas. Si una consulta puede escribirse con al menos una igualdad en su sección ON, escríbala así. Las uniones LEFT ANY, ANY INNER, LEFT SEMI y LEFT ANTI dejan de escanear la tabla derecha al encontrar la primera fila coincidente para cada fila izquierda, lo que normalmente las hace menos costosas que la unión ALL correspondiente. Sus equivalentes dirigidas por la derecha (RIGHT ANY, RIGHT SEMI, RIGHT ANTI) examinan todos los pares, porque el resultado depende de qué filas derechas coincidieron. join_any_take_last_row no tiene efecto aquí: sin join key no existe ningún grupo de filas coincidentes del que tomar la última. El consumo de memoria está acotado por la tabla derecha materializada y no crece con el tamaño del resultado. La tabla derecha está sujeta a la misma configuración que en otros algoritmos de unión, descrita en Limitaciones de memoria: max_rows_in_join, max_bytes_in_join y join_overflow_mode la limitan, y max_bytes_before_external_join hace que se vuelque a disco.

Valores `NULL` y `NaN` en las claves de `JOIN`

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.

Uso de ASOF JOIN

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.

ASOF JOIN solo es compatible con los algoritmos de join hash y full_sorting_merge. No es compatible con el motor de tabla Join.

Uso de PASTE JOIN

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

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

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;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

JOIN distribuido

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.

Conversión implícita de tipos

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) │
└────┴──────┴───────────────┴─────────────────┘

Recomendaciones de uso

Procesamiento de celdas vacías o NULL

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.

Sintaxis

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.

Limitaciones de sintaxis

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.

Rendimiento

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.

Limitaciones de memoria

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. Estos dos son límites estrictos y nunca hacen que un join se vuelque al disco, por lo que configurarlos en el umbral de volcado o por debajo de él normalmente detiene la consulta antes de que pueda volcarse. Dos configuraciones cambian esto: enable_adaptive_memory_spill_scheduler aún puede volcar un join que el umbral inferior hizo capaz de volcarse, y legacy_join_size_limits_trigger_spilling vuelve a convertir los dos límites en activadores de volcado a disco. Para permitir que un join siga ejecutándose al volcar el lado derecho al disco en lugar de generar un error, use:

max_bytes_before_external_join — Umbral de volcado absoluto.
max_bytes_ratio_before_external_join — Umbral de volcado como proporción de la memoria disponible.

Estos son los activadores de volcado basados en umbrales para todos los algoritmos basados en hash, incluido grace_hash; bajo presión de memoria, enable_adaptive_memory_spill_scheduler puede volcar antes de lo que indican, pero solo cuando uno de ellos no es cero, ya que un join sin ningún umbral nunca se vuelca. El join_algorithm que elija decide cómo se vuelca un join — grace_hash particiona la tabla derecha desde el primer bloque, hash y parallel_hash la recopilan en memoria y cambian cuando se supera el umbral — no si se aplican estas configuraciones. La única excepción es legacy_join_size_limits_trigger_spilling: al activarlo, grace_hash independiente ignora ambos umbrales y se vuelca según los dos límites estrictos.

Ejemplos

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 │
└───────────┴────────┴────────┘

Referencia de SQL

Tipos de datos

Motores

Funciones

Formatos

Configuración

Tablas del sistema

Lagos de datos

Tipos de unión compatibles

LATERAL JOIN

Configuración

Condiciones de la sección `ON`

Unión con condiciones de desigualdad para columnas de diferentes tablas

Unión con una condición ON arbitraria

Valores `NULL` y `NaN` en las claves de `JOIN`

Uso de ASOF JOIN

Uso de PASTE JOIN

JOIN distribuido

Conversión implícita de tipos

Recomendaciones de uso

Procesamiento de celdas vacías o NULL

Sintaxis

Limitaciones de sintaxis

Rendimiento

Limitaciones de memoria

Ejemplos

​Tipos de unión compatibles

​LATERAL JOIN

​Configuración

​Condiciones de la sección ON

​Unión con condiciones de desigualdad para columnas de diferentes tablas

​Unión con una condición ON arbitraria

​Valores NULL y NaN en las claves de JOIN

​Uso de ASOF JOIN

​Uso de PASTE JOIN

​JOIN distribuido

​Conversión implícita de tipos

​Recomendaciones de uso

​Procesamiento de celdas vacías o NULL

​Sintaxis

​Limitaciones de sintaxis

​Rendimiento

​Limitaciones de memoria

​Ejemplos

​Contenido relacionado

Tipos de unión compatibles

LATERAL JOIN

Configuración

Condiciones de la sección `ON`

Unión con condiciones de desigualdad para columnas de diferentes tablas

Unión con una condición ON arbitraria

Valores `NULL` y `NaN` en las claves de `JOIN`

Uso de ASOF JOIN

Uso de PASTE JOIN

JOIN distribuido

Conversión implícita de tipos

Recomendaciones de uso

Procesamiento de celdas vacías o NULL

Sintaxis

Limitaciones de sintaxis

Rendimiento

Limitaciones de memoria

Ejemplos

Contenido relacionado