Комментарии 14
SELECT T_1.a1, T_2.a1 FROM T AS T_1 INNER JOIN T AS T_2 ON T_1.a1 = T_2.a1;
Или
SELECT a1, a1 FROM T;
А вот нет, этот переход уже не работает (практически во всех SQL диалектах, с реляционной алгеброй зависит от определений). Для LEFT работал бы, а для INNER не годится без дополнительного условия, оставлю вопрос - какого?
Проверка на NULL
Согласен, забыл в том случае дополнить, что a1 в T даже должно быть уникальным, ненулевым, в общем, ключом, иначе совсем не получится JOIN по ON T_1.a1 = T_2.a1;
Ничего себе забыл, это в корне меняет все дело.
Без ограничений по a1 в T никак, без ограничений по a1 даже INNER JOIN наоборот превратится в CROSS JOIN:
SELECT T_1.a1, T_2.a1 FROM T AS T_1 INNER JOIN T AS T_2 ON T_1.a1 = T_2.a1
Ограничения по a1 в T могут быть разные (уникальность, первичный ключ, уникальность на основе данных в таблице T, но без формальных ограничений уникальности на уровне ClickHouse), и связь T_1.a1 и T_2.a1 может быть условно через третьи таблицы, каждый случай нужно рассматривать отдельно, но условие T_1.a1 = T_2.a1 по крайней мере должно работать (обеспечивать связь один к одному) на основе какого-то набора данных, чтобы дальше исследовать проблему.
Однако приведенные примеры это какая то оптимизация сферического запроса в вакууме. То есть вы консультант, смотрите чужой продукт и видите такой запрос из трёх cross join таблицы самой с собой. Но в моем опыте только один раз я видел столь безумный ляп, и он не был связан с cross join
Если же внутри одной фирмы вы видите такой запрос, то просто ищите автора, и если он не на колесах то просто говорите: дружище, без обид, но SQL - это не твое
Согласен, но и на практике при ручном написании запросов такие случаи тоже встречаются (хоть и в виде диких ляпов).
Эти случаи могут быть актуальными, когда SQL генерируется кодом (.NET, Java, Go и т.д.) для конкретного UI или отчета, т.е. не вручную. Во всех трех случаях для таблицы с 1 млн записей условно UI корректный (например, сводная таблица), или отчет условно корректный, т.е. их можно получить не за 1 млн × 1 млн операций, а за разумное число операций, условно 1 млн, но из-за CROSS JOIN получить отчет невозможно. Т.е. это больше для случаев, когда SQL генерируется не вручную и чинится тоже не вручную.
Рассмотренные случаи помогают что-то исправить в CROSS JOIN (удалить его) без значительного вмешательства в проект, такого, как:
ограничить число записей в запросе для одной или обеих таблиц в CROSS JOIN (например, LIMIT, LIMIT BY, topK в ClickHouse, или обычный GROUP BY - если возможно)
добавление паджинации для одной или обеих таблиц из запроса (если возможно)
условное упрощение сводной таблицы до таблицы размером 4 × 4 и выполнение 16 отдельных запросов для каждой ячейки (может быть актуально, например, для таблиц по кварталам или месяцам, при возможности можно выполнять запросы параллельно)
добавление или изменение фильтров для сводной таблицы
отказ от сводной таблицы как условно последнее средство, замена одного отчета несколькими и т.д., т.е. пересмотр бизнес-логики
Оптимизация CROSS JOIN? Никогда не думал, что увижу такое.
Лучшая оптимизация CROSS JOIN - его отсутствие.
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2;... если поля T_2 не используются, то можно избавиться от T_2 и CROSS JOIN соответственно
SELECT a1, a1 FROM T;
В общем случае - НЕЛЬЗЯ. Первый запрос возвращает несколько копий каждой записи. А второй - только одну копию. То есть запросы неэквивалентны. Более того, из результата первого запроса можно воспроизвести результат второго, но не наоборот. То есть предлагаемое преобразование приводит к необратимому повреждению результата и безвозвратной утрате части содержащихся в нём (мета)данных.
два перечисленные выше SQL запроса возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY).
Да нельзя их удалять, эти дубликаты! НЕЛЬЗЯ! Ничто не гарантирует, что само количество дубликатов в наборе записей на самом деле не является важной и значимой характеристикой результата. Которая будет использоваться в дальнейшем.
PS. И как по мне, вообще всё написанное - это совершенно не оптимизация. В случае алгебраических выражений это бы называли приведением, упрощением, или ещё каким термином, но уж точно не оптимизацией. Какой термин применять в случае СУБД правильно - как-то не припоминается.
Согласен, что разные результаты для SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2; и SELECT a1, a1 FROM T;, пояснение "возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)" может выглядеть неоднозначно, в playground приведён пример
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;
SELECT a1, a1 FROM T;
Он корректен, если a1 - это ключевое уникальное поле, для произвольного a1 корректно только такое:
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;
SELECT a1, a1 FROM T GROUP BY a1, a1;
Согласен, что с точки зрения SQL это можно назвать иначе, "оптимизация CROSS JOIN" - для упрощения, чтобы не писать условно "оптимизация производительности SQL запроса путём удаления CROSS JOIN и замены на эквивалентный SQL запрос с другими типами JOIN (или без них) и дополнительными ограничениями, который возвращает те же результаты запроса"
"возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)"
Все случаи применения CROSS JOIN, которые я встречал, именно для того и нужны, чтобы произвести дубликаты. А в статье предлагается их просто выкинуть, потеряв их основной функционал. Это не оптимизация, это ерунда какая-то.
Я бы понял, если бы предложили замену без потери функционала. LATERAL VIEW хотя бы...
Согласен, что для произвольного запроса убрать CROSS JOIN невозможно, но в статье рассматриваются три частных случая и есть условия их применимости, не предлагается выкинуть любой CROSS JOIN. Такое впечатление, что в комментарии не дубликаты имеются в виду, а декартово произведение, полный перебор. Три случая в статье относятся к особым видам запросов, где действительно можно убрать CROSS JOIN. Здесь я проиллюстрировал два запроса: первый попадает под один из описанных случаев, второй нет, и так и остаётся CROSS JOIN.
Кроме того, в реляционной алгебре нет дубликатов (т.к. идёт работа со множествами), но декартово произведение всё равно используется, т.е. декартово произведение без дубликатов (другого декартового произведения условно и нет в реляционной алгебре) и CROSS JOIN без дубликатов как минимум не лишены смысла, поэтому с учетом того, что проектов и кейсов много, и здесь наверно тоже какие-то частные случаи имеются в виду, и этот кейс не противоречит кейсам из статьи
CROSS JOIN, которые я встречал, именно для того и нужны, чтобы произвести дубликаты
Оптимизация CROSS JOIN — первые шаги