Процедурно преобразовать подзапрос в соединение

Ответ 1

Преобразование подзапроса в JOIN может быть довольно простым:

IN

 FROM TABLE_X x
WHERE x.col IN (SELECT y.col FROM TABLE_Y y)

... можно преобразовать в:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

Ваши критерии JOIN заключаются в прямом сравнении.

EXISTS предложение

Но есть сложности, когда вы смотрите предложение EXISTS. EXISTS обычно коррелируются, где подзапрос фильтруется по критериям из таблицы (таблиц) вне подзапроса. Но EXISTS предназначен только для возврата логического значения на основе критериев.

 FROM TABLE_X x
WHERE EXISTS (SELECT NULL
                FROM TABLE_Y y
               WHERE y.col = x.col)

... конвертирована:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

Из-за логического, существует риск увеличения количества строк в наборе результатов.

SELECT в предложении SELECT

Они всегда должны быть изменены с предубеждением:

SELECT x.*,
       (SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col)
  FROM TABLE_X x

Теперь вы, вероятно, заметили patter, но я сделал это немного по-другому для примера в виде строки:

SELECT x.*,
       z.mc
  FROM TABLE_X x
  JOIN (SELECT y.col, --inline view within the brackets
               MAX(y.example_col) 'mc'
          FROM TABLE_Y y
      GROUP BY y.col) z ON z.col = x.col

Ключ должен убедиться, что набор результатов встроенного представления включает в себя столбец (столбцы), необходимые для соединения с столбцами.

LEFT JOIN s

Возможно, вы заметили, что у меня не было примеров LEFT JOIN - это было бы необходимо, только если столбцы из подзапроса используют NULL-тестирование (COALESCE почти для любого db в наши дни, Oracle NVL или NVL2, MySQL IFNULL, SQL Server ISNULL и т.д.):

SELECT x.*,
       COALESCE((SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col), 0)
  FROM TABLE_X x

Converted:

   SELECT x.*,
          COALESCE(z.mc, 0)
     FROM TABLE_X x
LEFT JOIN (SELECT y.col,
                  MAX(y.example_col) 'mc'
             FROM TABLE_Y y
         GROUP BY y.col) z ON z.col = x.col

Заключение

Я не уверен, что это удовлетворит ваши типографские потребности, но надеюсь, что я продемонстрировал, что ключ определяет, что такое критерии JOIN. Как только вы узнаете о столбце (столбцах), вы знаете таблицу (ы).

Ответ 2

Этот вопрос основан на базовом знании реляционной алгебры. Вам нужно спросить себя, какое соединение выполняется. Например, LEFT ANTI SEMI JOIN похож на предложение WHERE NOT EXISTS.

Некоторые объединения не позволяют дублировать данные, некоторые не позволяют исключать данные. Другие позволяют добавлять дополнительные поля. Я обсуждаю это в своем блоге на http://msmvps.com/blogs/robfarley/archive/2008/11/09/join-simplification-in-sql-server.aspx

Кроме того, пожалуйста, не чувствуйте, что вам нужно делать все в JOIN. Оптимизатор запросов должен позаботиться обо всем этом для вас, и вы можете часто делать ваши запросы намного сложнее, чтобы поддерживать этот путь. Вы можете найти обширное предложение GROUP BY и иметь интересные WHERE.. IS NULL фильтры, которые будут служить только для отключения бизнес-логики от дизайна запроса.

Подзапрос в предложении SELECT (по существу, поиск) предоставляет только дополнительное поле, а не дублирование или исключение. Поэтому вам нужно убедиться, что вы выполняете значения GROUP BY или DISTINCT в своем JOIN и используете OUTER JOIN, чтобы гарантировать, что поведение одинаковое.

Подзапрос в предложении WHERE никогда не может дублировать данные или предоставлять дополнительные столбцы в предложение SELECT, поэтому вы должны использовать GROUP BY/DISTINCT для проверки этого. ГДЕ СУЩЕСТВУЕТСЯ. (Это ЛЕВОЕ ПОЛУЧЕНИЕ ПРИЕМА)

ГДЕ НЕ СУЩЕСТВУЕТ (LEFT ANTI SEMI JOIN) не предоставляет данные и не дублирует строки, но может устранить... для этого вам нужно делать LEFT JOINs и искать NULL.

Но оптимизатор запросов должен обрабатывать все это для вас. Мне действительно нравится иметь случайные подзапросы в предложении SELECT, потому что он очень ясно показывает, что я не дублирую или не удаляю строки. QO может привести его в порядок, но если я использую функцию представления или встроенной таблицы, я хочу дать понять тем, кто приходит за мной, что QO может упростить ее. Посмотрите на планы выполнения вашего исходного запроса, и вы увидите, что система предоставляет соединения INNER/OUTER/SEMI для вас.

То, что вам действительно нужно избегать (по крайней мере, в SQL Server), - это функции, которые используют BEGIN и END (например, Scalar Functions). Они могут почувствовать, что они упрощают ваш код, но они будут фактически выполняться в отдельном контексте, поскольку система считает их процедурной (не упрощаемой).

Я сделал сеанс такого рода на недавней конференции SQLBits V. Это было записано, поэтому вы должны иметь возможность смотреть его в какой-то момент (если вы можете мириться с моими шуточками!)

Ответ 3

Это часто возможно, и что хорошего в том, что оптимизатор запросов может делать это автоматически, поэтому вам не нужно заботиться об этом.

Ответ 4

На действительно высоком уровне. для преобразования подзапроса в JOIN:

• FROM: Имена таблиц идут в FROM
  • JOIN. Части предложения WHERE с именами таблиц с обеих сторон определяют (a) тип JOIN (b) условие соединения
  • WHERE Части предложения where без имен таблиц с обеих сторон входят в предложение WHERE
  • SELECT Имена столбцов из Sub-Query перейдите в SELECT

Преобразование JOIN в Sub-Query влечет за собой обратную сторону указанной выше логики

Ответ 5

В SQL Server, по крайней мере, оптимизатор может это сделать по своему усмотрению, но я уверен, что есть ограничения, когда он это делает. Я уверен, что, вероятно, кто-то кандидатскую диссертацию мог сделать это на компьютере.

Когда я делаю это старомодный человеческий путь, он довольно прост - особенно если подзапрос уже сглажен - его можно перенести в выражение Common Table.

Ответ 6

Это говорит о сильной "зависимости".

На одном уровне, если вы говорите о запросах, совместимых с ANSI SQL 89 или 92 *, я бы предположил, что это возможно. Если у вас есть простые (или даже не очень простые) запросы, состоящие из "основных" предложений select, from и where, то да, я бы хотел подумать, что математически возможно определить процессы и процедуры для создания и "несотворения" подзапросов (хотя, как вы могли бы определить, когда алгоритмически сформировать подзапрос, это вне меня). Я думаю, что это "обоснование" может быть применено к внешним соединениям и коррелированным подзапросам.

На другом уровне я бы сказал "нет". Большую часть времени я пишу подзапрос, потому что я не могу придумать способ вставить его в "основной" запрос. Очень редко это связано с коррелированными подзапросами, но чаще всего вовлекает в себя то, что я, я довольно уверен, проприетарные расширения стандартов. Как вы могли бы рассчитать свопы, неавторизованные, рейтинговые функции, предложения TOP N (которые вполне могут быть стандартами ANSI, я признаю, что никогда не прочитал их на обложке), ПОЛНОЕ или ВНЕШНИЕ ПРИМЕНЕНИЯ и т.п.? И это только части SQL Server, я уверен, что Oracle, DB2, MYSQL и большинство других игроков имеют свои собственные расширения, которые ломают "пуристскую" реляционную модель.

Конечно, говорят, что доказать отрицание невозможно. Я бы подвел итог: "не может быть сделано до тех пор, пока не будет доказано обратное", оставьте доказательство для ученых и теоретиков, и отметьте, что даже тогда любая система, которую вы покупаете, не поддержит ее, если она не имеет финансового смысла для производителя (в какой-либо системе еще есть поддержка OUTER UNION?)

** Немного поисковой системы не удалось получить ссылки на третий стандарт ANSI SQL. Я знаю, что слышал об этом много лет назад, это когда-нибудь случилось? *

Ответ 7

Полностью автоматическая система для преобразования запросов из подзапросов в соединения будет относительно сложной для построения. Вам нужно будет выполнить входной запрос, проанализировать его в дереве разбора, а затем выполните некоторые довольно сложные совпадения шаблонов в дереве разбора - заменяя разделы дерева на новые разделы дерева синтаксического анализа. В конце вы выполните обход дерева для вывода нового запроса.

Там могут быть некоторые удивительно хорошие или плохие результаты. Иногда подзапрос намного быстрее, чем соединение. Иногда это обратное.