JOIN

تُنشئ عبارة JOIN جدولًا جديدًا من خلال دمج الأعمدة من جدول واحد أو عدة جداول باستخدام القيم المشتركة بينها. وهي عملية شائعة في قواعد البيانات التي تدعم SQL، وتقابل عملية join في الجبر العلاقي. وغالبًا ما يُشار إلى الحالة الخاصة المتمثلة في ربط جدول بنفسه باسم “self-join”. البنية

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

تُسمّى التعبيرات الواردة في عبارة ON والأعمدة الواردة في عبارة USING “مفاتيح الربط”. ما لم يُذكر خلاف ذلك، فإن JOIN يُنتج حاصل ضرب ديكارتيًا من الصفوف ذات “مفاتيح الربط” المتطابقة، وقد يؤدي ذلك إلى نتائج تضم عددًا من الصفوف أكبر بكثير من الجداول المصدرية.

أنواع JOIN المدعومة

جميع أنواع SQL JOIN القياسية مدعومة:

Type	Description
`INNER JOIN`	تُعاد الصفوف المتطابقة فقط.
`LEFT OUTER JOIN`	تُعاد الصفوف غير المتطابقة من الجدول الأيسر بالإضافة إلى الصفوف المتطابقة.
`RIGHT OUTER JOIN`	تُعاد الصفوف غير المتطابقة من الجدول الأيمن بالإضافة إلى الصفوف المتطابقة.
`FULL OUTER JOIN`	تُعاد الصفوف غير المتطابقة من كلا الجدولين بالإضافة إلى الصفوف المتطابقة.
`CROSS JOIN`	يُنتج حاصل الضرب الديكارتي للجدولين بالكامل، ولا تُحدَّد “مفاتيح الربط”.
`NATURAL JOIN`	يربط تلقائيًا جميع الأعمدة ذات الاسم نفسه في كلا الجدولين؛ ويظهر كل عمود مشترك مرة واحدة في النتيجة. ويدعم الصيغ `INNER` (الافتراضية) و`LEFT` و`RIGHT` و`FULL`. وهو مكافئ لـ `JOIN ... USING (col1, col2, ...)` حيث تُشتق قائمة الأعمدة تلقائيًا.

يشير JOIN من دون تحديد نوع إلى INNER.
يمكن حذف الكلمة المفتاحية OUTER بأمان.
من الصيغ البديلة لـ CROSS JOIN تحديد عدة جداول في FROM clause مفصولة بفواصل.
إذا لم تكن هناك أعمدة متطابقة لـ NATURAL JOIN، فإنه يعمل مثل CROSS JOIN.

تتوفر أيضًا في ClickHouse أنواع JOIN إضافية:

Type	Description
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	قائمة سماح على “مفاتيح الربط”، من دون إنتاج حاصل الضرب الديكارتي.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	قائمة حظر على “مفاتيح الربط”، من دون إنتاج حاصل الضرب الديكارتي.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	يعطّل جزئيًا (للجانب المقابل من `LEFT` و`RIGHT`) أو كليًا (بالنسبة إلى `INNER` و`FULL`) حاصل الضرب الديكارتي لأنواع `JOIN` القياسية.
`ASOF JOIN`, `LEFT ASOF JOIN`	ربط للتسلسلات مع مطابقة غير تامة. يُشرح استخدام `ASOF JOIN` أدناه.
`PASTE JOIN`	يُجري دمجًا أفقيًا لجدولين.

عند استخدام المحلّل (analyzer)، يؤدي تعطيل الإعداد semi_join_include_columns_from_both_sides أو anti_join_include_columns_from_both_sides إلى أن يقتصر الربط المعني على إتاحة جانبه المحفوظ فقط للتعابير التي تُحلَّل بعد تكوين نتيجة الربط.

يُتيح LEFT SEMI JOIN وLEFT ANTI JOIN أعمدة الجانب الأيسر فقط.
يُتيح RIGHT SEMI JOIN وRIGHT ANTI JOIN أعمدة الجانب الأيمن فقط.
يؤثر ذلك في عبارات مثل SELECT وPREWHERE وWHERE وGROUP BY وHAVING وQUALIFY وORDER BY وLIMIT BY، بما في ذلك محارف البدل المؤهَّلة مثل t1.*.
يظل بإمكان تعبير ON الخاص بـ JOIN نفسه الإشارة إلى كلا الجانبين.

عند تمكين هذه الإعدادات (وهو الوضع الافتراضي)، يحافظ ClickHouse على السلوك القديم، إذ يظل كلا الجانبين متاحَين ويُوسِّع SELECT * الأعمدة من كلا الجدولين.

عند ضبط join_algorithm على partial_merge، لا يكون RIGHT JOIN وFULL JOIN مدعومَين إلا مع صرامة من النوع ALL (SEMI وANTI وANY وASOF غير مدعومة).

LATERAL JOIN

يتيح JOIN LATERAL للاستعلام الفرعي الواقع على الجانب الأيمن من عملية الربط أن يشير إلى أعمدة تعبيرات الجداول الواقعة على يساره؛ إذ يُقيَّم الاستعلام الفرعي مرةً لكل تركيبة مميزة من قيم أعمدة الجانب الأيسر التي يشير إليها، ثم تُربط نتيجته بكل صف من صفوف الجانب الأيسر يحمل التركيبة نفسها:

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

المسند ON true إلزامي، كما هو الحال في أي INNER أو LEFT JOIN آخر؛ وحذفه يُعدّ خطأً نحويًا. هذه الميزة تجريبية ومعطّلة افتراضيًا؛ ويمكن تفعيلها عبر الإعداد allow_experimental_lateral_join. لا تُدعم حتى الآن سوى المجموعة الفرعية التالية؛ وكل ما عداها يُرفض مع ظهور خطأ:

INNER JOIN LATERAL وLEFT JOIN LATERAL فقط؛ ولا تُدعم عمليات الربط من نوع RIGHT وFULL وPASTE وNATURAL، ولا يمكن إطلاقًا الجمع بين LATERAL وربط CROSS أو الربط بالفاصلة.
الصرامة الافتراضية ALL فقط؛ ولا تُدعم ANY وSEMI وANTI وASOF.
لا يُسمح بأي مسند ربط غير ON true (يُقبل ON 1 أيضًا)؛ ولا يُدعم USING، ولا يمكن حذف المسند. ضع شروط التصفية التي تربط بين الطرفين في عبارة WHERE الخاصة بالاستعلام الفرعي الجانبي.
لا تُدعم مُعدِّلات الربط GLOBAL وLOCAL.
يجب أن يشير الاستعلام الفرعي الجانبي إلى عمود واحد على الأقل من الطرف الأيسر. أما الاستعلام الفرعي غير المترابط فاستخدم معه ربطًا عاديًا.
يُقيَّم الاستعلام الفرعي الجانبي مرة واحدة لكل قيمة مميزة من قيم أعمدة الطرف الأيسر التي يشير إليها، لا مرة لكل صف من الطرف الأيسر؛ لذا يجب ألا يحتوي على دوال غير حتمية ضمن الاستعلام، مثل rand أو generateUUIDv4، ولا على دالات جداول تولّد صفوفًا عشوائية، مثل generateRandom. أما الدوال الثابتة ضمن الاستعلام، مثل now، فمسموح بها.
لا يُدعم بوصفه تعبير الجدول الجانبي إلا الاستعلام الفرعي. ولا تُدعم صيغ مصادر الجداول المستخدمة في PostgreSQL مثل LATERAL unnest(...) وCROSS JOIN UNNEST(...) - استخدم بدلًا منها عبارة ARRAY JOIN.
يُنفَّذ GROUP BY وORDER BY الخاصان بالاستعلام الفرعي الجانبي مرة واحدة على جميع عمليات التقييم مجتمعةً، لذا فإن الحدود max_rows_to_group_by وmax_rows_to_sort وmax_bytes_to_sort تحتسب صفوف جميع عمليات التقييم، لا صفوف عملية واحدة فقط. ولا تُدعم هذه الحدود إلا مع وضع التجاوز throw؛ أما any وbreak فيُرفضان.
تُطابَق صفوف جميع عمليات التقييم مع صفوف الطرف الأيسر عبر عملية ربط واحدة. وكما في أي ربط بالتجزئة، يقيّد max_rows_in_join وmax_bytes_in_join الطرفَ الذي يُحتفظ به في الذاكرة من هذا الربط، ويتولى المخطِّط اختيار هذا الطرف: مع الإعدادات الافتراضية (correlated_subqueries_use_in_memory_buffer = 1) يكون الطرف الأيسر من JOIN LATERAL، لأن صفوف الطرف الأيسر يجب أن تُقرأ كاملةً قبل تقييم الاستعلام الفرعي الجانبي؛ وإلا فقد يكون نتائج جميع عمليات التقييم مجتمعةً. وتُطبَّق الحدود دائمًا كما لو كانت قيمة join_overflow_mode هي throw، إذ إن الربط مع break سيُسقط بصمت صفوفًا من الطرف الأيسر لا علاقة لها بالأمر.

مثال

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;

الإعدادات

يمكن تجاوز نوع JOIN الافتراضي باستخدام الإعداد join_default_strictness. يعتمد سلوك ClickHouse server في عمليات ANY JOIN على الإعداد any_join_distinct_right_table_keys. انظر أيضًا

استخدم الإعداد cross_to_inner_join_rewrite لتحديد السلوك عند فشل ClickHouse في إعادة كتابة CROSS JOIN إلى INNER JOIN. القيمة الافتراضية هي 1، ما يسمح بمتابعة عملية JOIN ولكنها ستكون أبطأ. اضبط cross_to_inner_join_rewrite على 0 إذا كنت تريد ظهور خطأ، واضبطه على 2 لعدم تشغيل عمليات cross joins، وبدلًا من ذلك فرض إعادة كتابة جميع عمليات comma/cross joins. وإذا فشلت إعادة الكتابة عندما تكون القيمة 2، فستتلقى رسالة خطأ تنص على: “Please, try to simplify WHERE section”.

شروط قسم `ON`

يمكن أن يحتوي قسم ON على عدة شروط مدمجة باستخدام المعاملين AND وOR. ويجب أن تستوفي الشروط التي تحدد مفاتيح الربط ما يلي:

أن تشير إلى كلٍّ من الجدول الأيسر والجدول الأيمن
أن تستخدم معامل المساواة

أما الشروط الأخرى، فيمكنها استخدام معاملات أخرى، ويمكنها أن تشير إلى الجدول الأيسر أو الجدول الأيمن في الاستعلام، أو إلى كليهما. تُربط الصفوف إذا تحقق الشرط المركب بالكامل. وإذا لم تتحقق الشروط، فقد تظل الصفوف مُضمَّنة في النتيجة بحسب نوع JOIN. لاحظ أنه إذا وُضعت الشروط نفسها في قسم WHERE ولم تتحقق، فستُستبعد الصفوف دائمًا من النتيجة. يعمل المعامل OR داخل عبارة ON باستخدام خوارزمية الربط بالتجزئة — إذ يُنشأ جدول تجزئة منفصل لكل وسيطة OR تحتوي على مفاتيح ربط لـ JOIN، لذلك يزداد استهلاك الذاكرة ووقت تنفيذ الاستعلام خطيًا مع زيادة عدد تعبيرات OR في عبارة ON.

معامل المساواة (=) وحده هو ما يجعل الشرط مفتاح ربط. والمعاملات الأخرى بين أعمدة من جداول مختلفة مدعومة، لكن في غياب أي شرط مساواة يتعيّن على عملية الربط فحص كل زوج من الصفوف، فتصبح أبطأ بكثير؛ راجع JOIN مع شرط ON عشوائي.

مثال لننظر إلى table_1 و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 │
└────┴──────┘     └────┴────────────────┴────────┘

استعلام يتضمن شرطًا واحدًا لمفتاح الربط وشرطًا إضافيًا لـ 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');

لاحظ أن النتيجة تتضمن الصف الذي يحمل الاسم C وعمود النص الفارغ. وقد أُدرجت في النتيجة لأن نوع OUTER من join مستخدم هنا.

Response

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

استعلام باستخدام عملية ربط من النوع INNER وبشروط متعددة:

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

استعلام مع join من النوع INNER وشرط يستخدم 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 │
└───┴────┴─────┘

استعلام بنوع الربط INNER وبشروط تتضمن OR وAND:

يُطبَّق شرط عدم المساواة الذي يستخدم أعمدة من جدول واحد، مثل t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c، على ذلك الجدول وحده: إذ يستخدم t1.b > 0 أعمدة من t1 فقط، وt2.b > t2.c يستخدم أعمدة من t2 فقط. كما أن شروط عدم المساواة التي تقارن أعمدة من جداول مختلفة، مثل t1.a = t2.key AND t1.b > t2.key، مدعومة كذلك؛ راجع الأقسام أدناه لمزيد من التفاصيل.

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

JOIN مع شروط عدم المساواة للأعمدة من جداول مختلفة

يدعم ClickHouse ‏ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN مع شروط عدم المساواة بالإضافة إلى شروط المساواة. عندما يحتوي قسم ON على شرط مساواة بين الجدولين إلى جانب شرط عدم المساواة، يكون شرط المساواة هو مفتاح الربط، ويُتحقَّق من شرط عدم المساواة على الصفوف التي طابقها. ولا يُنفَّذ هذا الشرط المختلط إلا بواسطة خوارزميات الربط hash وparallel_hash وgrace_hash. عندما لا يحتوي قسم ON على أي شرط مساواة بين الجدولين، فلا يوجد مفتاح ربط تُجرى المطابقة على أساسه. ويُنفَّذ هذا الربط بواسطة ie_join عندما يكون الشرط زوجًا من شروط عدم المساواة، وإلا فيُنفَّذ بوصفه ربطًا بحلقات متداخلة على مستوى الكتل. لا يجوز أن يحتوي الشرط الذي يُقيَّم أثناء الربط على arrayJoin، إذ يجب أن يحافظ على عدد الصفوف؛ استخدم بدلًا من ذلك ARRAY JOIN في استعلام فرعي. مثال الجدول 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 │
└──────┴──────┴───┴───┴───┘

الجدول 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

JOIN مع شرط ON عشوائي

يمكن أن يكون قسم ON أي تعبير منطقي على أعمدة الجدولين، مثل التحقق من نطاق، أو مقارنة حسابية، أو استدعاء دالة مفردة. وإذا لم يتضمن أي مساواة بين الجدولين، فلن يوجد مفتاح ربط تُطابَق الصفوف على أساسه. ويُنفَّذ زوج من شروط عدم المساواة باستخدام ie_join عندما تكون هذه الخوارزمية مدرجة في join_algorithm، وهو ما يحدث افتراضيًا. أما أي شرط آخر فيُنفَّذ بوصفه ربطًا بحلقات متداخلة على مستوى الكتل، إذ يُجسَّد الجدول الأيمن ثم يُقيَّم الشرط على كل زوج من الصفوف. ويأخذ ALL INNER JOIN في هذه الحالة صيغة مكافئة لـ CROSS JOIN يُستخدم فيها الشرط عاملَ تصفية. يدعم الربط بحلقات متداخلة على مستوى الكتل جميع أنواع الربط ومستويات الصرامة باستثناء ASOF JOIN وPASTE JOIN وANY FULL JOIN. وهو ليس من قيم join_algorithm، بل هو الملاذ الأخير الذي لا يُلجأ إليه إلا عندما تعجز الخوارزميات الأخرى عن تنفيذ الشرط. ويظهر في إخراج EXPLAIN على هيئة خطوة BlockNestedLoopJoin. وعند تعطيل allow_block_nested_loop_join، يُرفض أي استعلام يتطلبه ويُعاد الخطأ INVALID_JOIN_ON_EXPRESSION. مثال

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

يُكمَّل الصف الذي لم يطابق أي شيء وفقًا للإعداد join_use_nulls، كما هو الحال في أي ربط آخر: بقيمة افتراضية كما في المثال أعلاه، وبـ NULL عند تفعيل هذا الإعداد. الصرامة يحتفظ ANY وSEMI عادةً بصف واحد لكل مجموعة من الصفوف التي تشترك في مفتاح الربط نفسه. ولمّا كانت هذه المجموعة غير موجودة هنا، فإنهما يحتفظان بصف واحد لكل صف من الجدول الذي يقود الربط: يُخرج LEFT ANY وLEFT SEMI كل صف أيسر مرة واحدة على الأكثر، ويُخرج RIGHT ANY وRIGHT SEMI كل صف أيمن مرة واحدة على الأكثر. أما ANY INNER فيقيّد الجانبين معًا — إذ يُستخدم كل صف من كلا الجدولين مرة واحدة على الأكثر، فلا يتجاوز عدد صفوف النتيجة عدد صفوف الجدول الأصغر. ويكون اختيار الأزواج التي تُكوّن هذه النتيجة اعتباطيًا، تمامًا كما يقتضي ANY، وقد يختلف بين تشغيلين للاستعلام نفسه. وفي حالة ANY INNER يمتد ذلك ليشمل عدد الصفوف أيضًا: فإقران صفوف الجانبين بطريقة جشعة، بأي ترتيب تُفحص به الصفوف، قد يترك عددًا مختلفًا من الصفوف دون إقران، ولذلك فإن نتيجة count() على ربط ANY INNER بلا مفتاح ربط غير قابلة للتكرار، وتتوقف على عدد خيوط التنفيذ وعلى الترتيب الفعلي لصفوف الجدول الأيمن. الأداء يُفحص كل زوج من الصفوف، لذا يتزايد حجم العمل بحسب حاصل ضرب عددَي صفوف الجدولين لا بحسب مجموعهما. ومن ثَمّ فإن الربط بالحلقات المتداخلة على مستوى الكتل أعلى تكلفة بمراتب عديدة من الربط بالتجزئة على البيانات نفسها، وتتسع الفجوة كلما كبر حجم الجداول. فإذا أمكن كتابة الاستعلام بشرط مساواة واحد على الأقل في قسم ON، فاكتبه على هذا النحو. تتوقف عمليات الربط LEFT ANY وANY INNER وLEFT SEMI وLEFT ANTI عن فحص الجدول الأيمن عند أول صف يطابق الصف الأيسر، مما يجعلها عادةً أقل تكلفة من ربط ALL المقابل. أما نظيراتها التي يقودها الجانب الأيمن (RIGHT ANY وRIGHT SEMI وRIGHT ANTI) فتفحص كل الأزواج، لأن النتيجة تتوقف على الصفوف اليمنى التي وُجدت لها مطابقة. ولا أثر للإعداد join_any_take_last_row هنا: فعند غياب مفتاح الربط لا توجد مجموعة من الصفوف المطابقة يُؤخذ آخرها. يظل استهلاك الذاكرة محدودًا بحجم الجدول الأيمن المُجسَّد، ولا يزداد بازدياد حجم النتيجة. ويخضع الجدول الأيمن للإعدادات نفسها المطبقة في خوارزميات الربط الأخرى، والمذكورة في قسم قيود الذاكرة: إذ تُقيّد حجمَه الإعدادات max_rows_in_join وmax_bytes_in_join وjoin_overflow_mode، في حين يؤدي الإعداد max_bytes_before_external_join إلى كتابته على القرص.

قيم `NULL` و`NaN` في مفاتيح الربط

لا تساوي NULL أي قيمة، بما في ذلك نفسها. وهذا يعني أنه إذا كانت قيمة مفتاح الربط هي NULL في أحد الجدولين، فلن تطابق قيمة NULL في الجدول الآخر. مثال الجدول A:

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

الجدول 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 │
└─────────┴───────┘

لاحظ أن الصف الذي يحتوي على Charlie من الجدول A، والصف الذي درجته 88 من الجدول B، لا يظهران في النتيجة بسبب القيمة NULL في مفتاح الربط. إذا أردت مطابقة قيم NULL، فاستخدم الدالة isNotDistinctFrom لمقارنة مفاتيح الربط.

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

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

لا تتبع قيم NaN في مفاتيح الربط من نوع float قاعدة NULL المذكورة أعلاه. تكون المقارنة القياسية بين قيمتي NaN (NaN = NaN) هي 0، لكن لا تُقارن مفاتيح الربط وفق دلالات المقارنة القياسية؛ وقد تتطابق مفاتيح NaN. وما إذا كانت تتطابق فعليًا يعتمد على تفاصيل التنفيذ وخوارزمية الربط ونوع المفتاح وإعدادات الجلسة. لا تعتمد على سلوك محدد. إذا كنت تحتاج إلى عدم تطابق الصفوف التي تحتوي على NaN، فحوّلها إلى قيم NULL: ON if(isNaN(A.id), NULL, A.id) = B.id. في ASOF JOIN، يُقارن العمود الخاص بأقرب تطابق وفق الترتيب، وهو ما لا تدعمه NaN — لذا رشّح هذه الصفوف من كلا الجانبين.

استخدام ASOF JOIN

تُعد ASOF JOIN مفيدة عندما تحتاج إلى ربط سجلات لا يوجد بينها تطابق تام. تتطلب خوارزمية JOIN هذه عمودًا خاصًا في الجداول. ويجب أن يكون هذا العمود:

أن يحتوي على تسلسل مرتب.
من أحد الأنواع التالية: Int, UInt، Float، Date، DateTime، Decimal.
بالنسبة إلى خوارزمية hash join، لا يمكن أن يكون العمود الوحيد في عبارة JOIN.

البنية ASOF JOIN ... ON:

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

يمكنك استخدام أي عدد من شروط المساواة، وشرطًا واحدًا فقط لأقرب مطابقة. على سبيل المثال، SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t. الشروط المدعومة لأقرب مطابقة: >, >=, <, <=. الصياغة ASOF JOIN ... USING:

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

يستخدم ASOF JOIN ‏equi_columnX للربط وفقًا للمساواة، وasof_column للربط بأقرب قيمة مطابقة مع الشرط table_1.asof_column >= table_2.asof_column. ويكون العمود asof_column دائمًا هو الأخير في بند USING. على سبيل المثال، تأمل الجداول التالية:

         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 أخذ الطابع الزمني لحدث مستخدم من table_1 والعثور على حدث في table_2 يكون طابعه الزمني الأقرب إلى الطابع الزمني لحدث table_1 وفقًا لشرط أقرب تطابق. وتُعد قيم الطابع الزمني المتساوية هي الأقرب إذا كانت متاحة. هنا، يمكن استخدام العمود user_id للربط على أساس المساواة، ويمكن استخدام العمود ev_time للربط على أساس أقرب تطابق. في مثالنا، يمكن ربط event_1_1 مع event_2_1، ويمكن ربط event_1_2 مع event_2_3، لكن لا يمكن ربط event_2_2.

لا يدعم ASOF JOIN إلا خوارزميتي JOIN ‏hash وfull_sorting_merge. وهو غير مدعوم في محرك الجدول Join.

استخدام PASTE JOIN

نتيجة PASTE JOIN هي جدول يحتوي على جميع الأعمدة من الاستعلام الفرعي الأيسر، ثم جميع الأعمدة من الاستعلام الفرعي الأيمن. تُطابَق الصفوف بناءً على مواضعها في الجداول الأصلية (يجب أن يكون ترتيب الصفوف محددًا). إذا أعادت الاستعلامات الفرعية أعدادًا مختلفة من الصفوف، فستُقتطع الصفوف الزائدة. مثال:

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

ملاحظة: في هذه الحالة، قد تكون النتيجة غير حتمية إذا جرت القراءة بالتوازي. على سبيل المثال:

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 الموزّع

توجد طريقتان لتنفيذ JOIN يتضمن جداول موزعة:

عند استخدام JOIN عادي، يُرسَل الاستعلام إلى الخوادم البعيدة. وتُشغَّل الاستعلامات الفرعية على كل خادم منها لتكوين الجدول الأيمن، ثم تُنفَّذ عملية الربط باستخدام هذا الجدول. وبعبارة أخرى، يُنشأ الجدول الأيمن على كل خادم على حدة.
عند استخدام GLOBAL ... JOIN، يشغّل الخادم الذي أرسل الطلب أولًا استعلامًا فرعيًا لحساب أحد طرفَي الربط ويجمع النتيجة في جدول مؤقت. ثم يُمرَّر هذا الجدول المؤقت إلى كل خادم بعيد، وتُشغَّل الاستعلامات عليها باستخدام البيانات المؤقتة المنقولة. في عمليتَي الربط LEFT وINNER، يُحسَب الجدول الأيمن باعتباره الاستعلام الفرعي. أما في عملية الربط RIGHT، فيُحسَب الجدول الأيسر بدلًا من ذلك، لأن الجدول الأيمن هو الجدول الذي يجري الاحتفاظ به ويجب قراءته من الشظايا.

توخَّ الحذر عند استخدام GLOBAL. لمزيد من المعلومات، راجع قسم الاستعلامات الفرعية الموزعة.

تحويل النوع الضمني

تدعم استعلامات INNER JOIN وLEFT JOIN وRIGHT JOIN وFULL JOIN تحويل النوع الضمني لـ “join keys”. ومع ذلك، لا يمكن تنفيذ الاستعلام إذا تعذر تحويل مفاتيح الربط في الجدولين الأيسر والأيمن إلى نوع واحد (على سبيل المثال، لا يوجد نوع بيانات يمكنه استيعاب جميع القيم من كلٍّ من UInt64 وInt64، أو String وInt32). مثال لنأخذ الجدول t_1:

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

والجدول t_2:

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

الاستعلام

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

يُعيد المجموعة:

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

توصيات الاستخدام

معالجة الخلايا الفارغة أو NULL

أثناء ربط الجداول، قد تظهر خلايا فارغة. يحدّد الإعداد join_use_nulls كيفية ملء ClickHouse لهذه الخلايا. إذا كانت مفاتيح الربط JOIN حقولًا من النوع Nullable، فلن تُربط الصفوف التي تكون قيمة واحد على الأقل من هذه المفاتيح فيها NULL.

البنية

يجب أن تحمل الأعمدة المحددة في USING الأسماء نفسها في كلا الاستعلامين الفرعيين، وأن تختلف أسماء الأعمدة الأخرى. يمكنك استخدام الأسماء المستعارة لتغيير أسماء الأعمدة في الاستعلامات الفرعية. تحدّد عبارة USING عمودًا واحدًا أو أكثر للربط، وبذلك تفرض تساوي هذه الأعمدة. تُكتب قائمة الأعمدة من دون أقواس. ولا تُدعَم شروط ربط أكثر تعقيدًا.

قيود الصياغة

بالنسبة إلى عبارات JOIN المتعددة ضمن استعلام SELECT واحد:

لا يتاح استخدام جميع الأعمدة عبر * إلا إذا كان الربط بين جداول، لا استعلامات فرعية.
عبارة PREWHERE غير متاحة.
عبارة USING غير متاحة.

بالنسبة إلى عبارات ON وWHERE وGROUP BY:

لا يمكن استخدام تعبيرات عشوائية في عبارات ON وWHERE وGROUP BY، ولكن يمكنك تعريف تعبير في عبارة SELECT ثم استخدامه في هذه العبارات عبر اسم مستعار.

الأداء

عند تنفيذ JOIN، لا يوجد تحسين لترتيب التنفيذ بالنسبة إلى المراحل الأخرى من الاستعلام. ويُنفَّذ الـ join (أي البحث في الجدول الأيمن) قبل التصفية في WHERE وقبل التجميع. في كل مرة يُنفَّذ فيها استعلام يستخدم JOIN نفسه، يُعاد تنفيذ الاستعلام الفرعي لأن النتيجة غير مخزنة مؤقتًا. ولتجنّب ذلك، استخدم محرك الجدول الخاص Join، وهو مصفوفة مُعدّة مسبقًا لعمليات الربط وتبقى دائمًا في ذاكرة RAM. في بعض الحالات، يكون استخدام IN أكثر كفاءة من JOIN. إذا كنت بحاجة إلى JOIN للربط مع جداول الأبعاد (وهي جداول صغيرة نسبيًا تحتوي على خصائص الأبعاد، مثل أسماء الحملات الإعلانية)، فقد لا يكون JOIN مناسبًا جدًا لأن الجدول الأيمن يُعاد الوصول إليه مع كل استعلام. في مثل هذه الحالات، توجد ميزة “Dictionaries” ينبغي استخدامها بدلًا من JOIN. لمزيد من المعلومات، راجع قسم Dictionaries.

قيود الذاكرة

افتراضيًا، يستخدم ClickHouse خوارزمية الربط بالتجزئة. يأخذ ClickHouse الجدول الأيمن right_table وينشئ له جدول تجزئة في RAM. إذا كان join_algorithm = 'auto' مفعّلًا، فبعد تجاوز عتبة معيّنة من استهلاك الذاكرة، يعود ClickHouse إلى خوارزمية merge join. للاطلاع على وصف خوارزميات JOIN، راجع الإعداد join_algorithm. إذا كنت بحاجة إلى تقييد استهلاك الذاكرة لعملية JOIN، فاستخدم الإعدادات التالية:

max_rows_in_join — يحدّ من عدد الصفوف في جدول التجزئة.
max_bytes_in_join — يحدّ من حجم جدول التجزئة.

عند بلوغ أيٍّ من هذه الحدود، يتصرّف ClickHouse وفقًا لما يحدّده الإعداد join_overflow_mode. وهذان حدّان صارمان لا يؤديان أبدًا إلى كتابة الربط على القرص، لذا فإن ضبطهما عند عتبة الكتابة أو دونها yوقف الاستعلام عادةً قبل أن يتمكن من الكتابة على القرص. وهناك إعدادان يغيّران ذلك: enable_adaptive_memory_spill_scheduler الذي لا يزال بإمكانه كتابة ربطٍ جعلته العتبة أدناه قابلًا للكتابة، و legacy_join_size_limits_trigger_spilling الذي يعيد الحدّين الصارمين إلى كونهما محفّزَين للكتابة على القرص. للسماح للربط بالاستمرار عبر كتابة الجانب الأيمن على القرص بدلًا من الفشل، استخدم:

max_bytes_before_external_join — عتبة كتابة مطلقة.
max_bytes_ratio_before_external_join — عتبة الكتابة كنسبة من الذاكرة المتاحة.

وهذان هما محفّز الكتابة المستند إلى العتبة لكل خوارزمية قائمة على التجزئة، بما في ذلك grace_hash؛ وتحت ضغط الذاكرة يمكن لـ enable_adaptive_memory_spill_scheduler أن يكتب على القرص أبكر مما يطلبانه — لكن فقط متى كان أحدهما غير صفري، إذ إن الربط الذي لا عتبة له إطلاقًا لا يكتب على القرص أبدًا. وخوارزمية الربط join_algorithm التي تختارها تحدّد كيفية كتابة الربط على القرص — إذ يقسّم grace_hash الجدول الأيمن ابتداءً من الكتلة الأولى، بينما يجمعه hash وparallel_hash في الذاكرة ثم ينتقلان عند تجاوز العتبة — لا ما إذا كانت هذه الإعدادات تنطبق. والاستثناء الوحيد هو legacy_join_size_limits_trigger_spilling: فعند تفعيله، يتجاهل grace_hash المستقل كلتا العتبتين ويكتب على القرص بناءً على الحدّين الصارمين بدلًا من ذلك.

أمثلة

مثال:

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

مرجع SQL

أنواع البيانات

المحركات

الدوال

الصيغ

الإعدادات

جداول النظام

بحيرات البيانات

أنواع JOIN المدعومة

LATERAL JOIN

الإعدادات

شروط قسم `ON`

JOIN مع شروط عدم المساواة للأعمدة من جداول مختلفة

JOIN مع شرط ON عشوائي

قيم `NULL` و`NaN` في مفاتيح الربط

استخدام ASOF JOIN

استخدام PASTE JOIN

JOIN الموزّع

تحويل النوع الضمني

توصيات الاستخدام

معالجة الخلايا الفارغة أو NULL

البنية

قيود الصياغة

الأداء

قيود الذاكرة

أمثلة

​أنواع JOIN المدعومة

​LATERAL JOIN

​الإعدادات

​شروط قسم ON

​JOIN مع شروط عدم المساواة للأعمدة من جداول مختلفة

​JOIN مع شرط ON عشوائي

​قيم NULL وNaN في مفاتيح الربط

​استخدام ASOF JOIN

​استخدام PASTE JOIN

​JOIN الموزّع

​تحويل النوع الضمني

​توصيات الاستخدام

​معالجة الخلايا الفارغة أو NULL

​البنية

​قيود الصياغة

​الأداء

​قيود الذاكرة

​أمثلة

​محتوى ذو صلة

أنواع JOIN المدعومة

LATERAL JOIN

الإعدادات

شروط قسم `ON`

JOIN مع شروط عدم المساواة للأعمدة من جداول مختلفة

JOIN مع شرط ON عشوائي

قيم `NULL` و`NaN` في مفاتيح الربط

استخدام ASOF JOIN

استخدام PASTE JOIN

JOIN الموزّع

تحويل النوع الضمني

توصيات الاستخدام

معالجة الخلايا الفارغة أو NULL

البنية

قيود الصياغة

الأداء

قيود الذاكرة

أمثلة

محتوى ذو صلة