SQL-запросы к нескольким таблицам

  1. SQL-запросы к нескольким таблицам
  2. ЭТАП 1
  3. ЭТАП 2
  4. ЭТАП 3
  5. Использование скобок в объединениях таблиц и фильтрации FROM
  6. Логический и физический порядок объединения множеств
  7. Порядок и производительность подключения

В этой главе вы найдете описание процесса обработки SQL-запросов, сфокусированное на предложении FROM. Это продолжение вопросов, поднятых в статье - логическая обработка запросов , Я разделил эту тему на две части, из-за объема и необходимости обсуждать ранее правила объединения таблиц в SQL. Я рекомендую эти две статьи, в которых представлены основные правила написания SQL-запросов.

В этой части вы найдете информацию о том, как выполнять запросы к нескольким таблицам. Я представлю влияние порядка соединений на производительность и типичные логические ошибки.

SQL-запросы к нескольким таблицам

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

Для запросов, которые относятся к одному набору, случай очень прост. Результатом обработки шага FROM является виртуальная таблица VT1 . Он содержит все записи из исходного файла. То же самое и с теми же столбцами и записями. Файл VT1 также является источником следующего шага - ГДЕ обработки. Как видно из запросов к одной таблице, особой философии нет.

В реальных средах баз данных запросы обычно ссылаются на несколько таблиц.

Например, рассмотрим сценарий SQL-запроса в тестовой базе данных Northwind. Нас интересует информация о клиентах из определенного города - Мадрида. Мы хотим узнать подробности их заказов, представленных в сентябре 1996 года - названия и количество продуктов, которые они купили.

Структура этой базы данных нормализована. Интересующая нас информация требует комбинации из 4 таблиц.
Структура этой базы данных нормализована

Пример запроса, который выполняет эту задачу, может выглядеть следующим образом:

SELECT c. CompanyName, o. OrderID, от. Количество, стр. ProductName ОТ ДБО. Клиенты могли покинуть внешнюю сеть dbo. Заказы примерно на c. CustomerID = o. CustomerID INNER JOIN dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID ВНУТРЕННЕЕ СОЕДИНЕНИЕ Продукты р он с тех пор. ProductID = p. ProductID ГДЕ c. Город = 'Мадрид' И о. Дата заказа между '1996-09-01' и '1996-09-30'

Чтобы сделать его более интересным (немного агрессивным), я использовал первое соединение - LEFT OUTER JOIN. Я хочу показать одну из самых частых логических ошибок при анализе соединений.

В результате оказывается, что существует только один клиент, который дополнительно отправил только один заказ в пределах указанного диапазона дат.
В результате оказывается, что существует только один клиент, который дополнительно отправил только один заказ в пределах указанного диапазона дат

Прежде чем перейти к подробному анализу нашего запроса, мы можем разделить предложение FROM на этапы. Мы учитываем количество соединений и связанных с ними операций, происходящих здесь. У нас есть три объединения - так будет три этапа.

Давайте сохраним ОТ ОТ более ярко:

ОТ - ЭТАП 1 - объединение первых двух таблиц dbo. Клиенты могли покинуть внешнюю сеть dbo. Заказы для ON c. CustomerID = o. CustomerID - ЭТАП 2 - к результату ЭТАПА 1 мы объединяем таблицу dbo. [Детали заказа] INNER JOIN dbo. [Детали заказа] с ON с. OrderID = o. OrderID - ЭТАП 3 - к результату ЭТАПА 2 объединяем таблицу dbo.Products INNER JOIN dbo. Продукты п ПО от. ProductID = стр. ProductID

Правила подключения, которые применяются к запросам к нескольким таблицам SQL, идентичны правилам, описанным для примера объединения двух таблиц.

Мы всегда начинаем с объединения первых двух, стоящих сразу после ОТ. Мы также можем использовать скобки (об этом в следующем примере). Они имеют приоритет - таблица объединяется с результатом операций в скобках.

При объединении трех или более таблиц каждая последующая прикрепляется к результату предыдущего шага. Алгоритм работает итеративно слева направо, пока не будет обработан последний файл. Затем давайте следуем точным шагам обработки FROM нашего запроса.

ЭТАП 1

Внешнее объединение (LEFT OUTER JOIN) первых двух таблиц:

SELECT c. CompanyName, o. OrderID FROM dbo. Клиенты могли покинуть внешнюю сеть dbo. Заказы для ON c. CustomerID = o. CustomerID

Этот шаг будет обработан в соответствии с типичными правилами обработки логического соединения:

  • Загрузите все данные из таблицы dbo.Customers
  • Загрузить все данные из таблицы dbo.Orders
  • Сделать внешний сустав
    • Во-первых, декартово произведение этих двух таблиц
    • Затем производится фильтрация записей в соответствии с выражениями, установленными в ON.
    • Добавление «отфильтрованных» записей из таблицы, стоящей на левой стороне оператора JOIN (из таблицы dbo.Customers). Значения атрибутов таблицы dbo.Orders для этих «несопоставленных элементов» из предыдущего шага заполняются NULLami.

Orders для этих «несопоставленных элементов» из предыдущего шага заполняются NULLami

В результате мы получим всех клиентов (все содержимое таблицы dbo.Customers) вместе с информацией об их заказах. Обратите внимание на первые две записи. Это элементы, «добавленные» на этапах, характерных для внешних подключений - клиенты, которые не разместили никаких заказов.

CompanyName OrderID ---------------------------------------- -------- --- ФИССА Фабрика Интер. Salchichas SA NULL Парижские специальные предложения NULL Vins et alcools Шевалье 10248 Томс Spezialitäten 10249 ... Рихтер Супермаркет 11075 Бон приложение '11076 Гастрономическая змея Каньон Бакалея 11077 (затронуто 832 строк)

ЭТАП 2

Результирующим набором предыдущего этапа ( VT1.1 ) будет таблица, стоящая на левой стороне следующего соединения. На этот раз это будет внутренний союз. VT1.1 я поместил для лучшей иллюстрации в скобках.

SELECT c. CompanyName, o. OrderID, от. Количество FROM - в результате результат ЭТАПА 1 (dbo, Клиенты c LEFT OUTER JOIN dbo. Заказы o на c. CustomerID = o. CustomerID) INNER JOIN dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID

Итак, шаги предприняты:

  • Загрузить все данные из файла, полученного в результате первого соединения - VT1.1
  • Загрузите все данные из таблицы dbo. [Детали заказа].
  • Делать внутреннее соединение этих наборов
    • Декартово произведение
    • фильтрация записей

Делать внутреннее соединение этих наборов   Декартово произведение   фильтрация записей

CompanyName OrderID Количество ---------------------------------------- ------- ---- -------- Vins et alcools Chevalier 10248 12 Vins et alcools Chevalier 10248 10 Vins et alcools Chevalier 10248 5 Toms Spezialitäten 10249 9 ... Продуктовый магазин Каньон с гремучими змеями 11077 2 Продуктовый магазин Каньон с гремучими змеями 11077 4 Продуктовый магазин Каньон с гремучими змеями 11077 2 (затронуты 2155 строк)

Обратите внимание на то, что произошло с клиентами, которые не разместили никаких заказов. На предыдущем шаге, благодаря LEFT JOIN, они были включены (добавлены к результату). Теперь при следующем присоединении - эти элементы были отфильтрованы.

В нашем сценарии это не имело значения для конечного результата. В конце концов, мы хотим получать информацию только о клиентах, которые разместили заказы. Фактически, первое соединение должно быть ВНУТРЕННИМ.

Случается, что начинающие пользователи SQL ожидают в этом случае, что у них также будут клиенты без заказов. Это логическая ошибка, которую я часто совершал, и я хотел бы обратить на это ваше внимание. Это происходит из-за незнания порядка, в котором сделаны соединения. Возможно, также из-за чрезмерного истолкования объема LEFT OUTER JOIN , который, в конце концов, касается только первого соединения.

Давайте вернемся к дальнейшему анализу нашего примера.

ЭТАП 3

Алгоритм будет повторяться еще раз, в конце концов присоединяясь к последней таблице - dbo.Products.

SELECT c. CompanyName, o. OrderID, от. Количество, ProductName FROM - в скобках результат STEP 2 (дБ. Клиенты c ВНЕШНИЙ ВСТУПЛЕНИЕ dbo. Заказы o на c. CustomerID = o. CustomerID INNER JOIN dbo. [Детали заказа] от него из. OrderID = o. OrderID ) ВНУТРЕННИЙ ПРИСОЕДИНЯЙТЕСЬ к ДБО. Продукты р он с тех пор. ProductID = стр. ProductID

Стандартные шаги повторяются снова, как при каждом объединении двух наборов:

  • Загрузите все данные из набора, полученного на этапе 2 (помещены в скобки) - VT1.2 .
  • Загрузите все данные из таблицы dbo.Products.
  • Делать внутреннее соединение этих наборов
    • Iloczna Cartesian
    • Фильтрация записей.

Делать внутреннее соединение этих наборов   Iloczna Cartesian   Фильтрация записей

CompanyName OrderID Количество ProductName ---------------------------------------- ------ ----- -------- ------------------------------ Vins et alcools Chevalier 10248 12 Queso Cabrales Vins et alcools Шевалье 10248 10 Сингапурские хоккиенские жареные Ми Винс и спирты Шевалье 10248 5 Моцарелла ди Джованни Томс Spezialitäten 10249 9 Тофу ... Каньон гремучей змеи Каньон Бакалея 11077 2 Род Кавиар Ратлсноне Каньон Фруктов-накидок Гамбургер Гамбургер Soße (2155 пострадавших рядов)

На этом этапе предложение FROM обрабатывается. Все соединения были сделаны. Я пометил набор результатов как VT2, чтобы отличить его от простого запроса к одной таблице (VT1). Передано на следующий шаг - фильтрация записи в ГДЕ. Процесс обработки продолжается, как описано здесь логичный порядок.

Использование скобок в объединениях таблиц и фильтрации FROM

Давайте теперь рассмотрим сценарий, снова в базе данных Northwind, где мы хотим получить информацию обо ВСЕХ клиентах из Мадрида, а также информацию о продуктах, которые они заказали в декабре 1996 года. Если они не разместили никаких заказов в течение этого периода, мы также хотим их увидеть.

Запрос, выполняющий эту задачу, должен вернуть следующий набор:

Запрос, выполняющий эту задачу, должен вернуть следующий набор:

Это особый случай, когда фильтрация записей по дате заказа должна выполняться в ОТ, а не ГДЕ. Здесь вы также увидите практическое использование скобок в FROM, благодаря которым мы можем контролировать логику соединения. Я не буду использовать здесь подзапросы, только чистые, простые объединения.

Чтобы получить информацию обо всех клиентах в Мадриде с их заказами (пока без фильтрации даты), вы можете сохранить запрос следующим образом:

SELECT c. CompanyName, o. OrderID, от. Количество, стр. ProductName ОТ ДБО. Клиенты c ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ (дБ. Заказы на INNER JOIN dbo. [Детали заказа] из ON с. OrderID = o. OrderID INNER JOIN dbo. Продукты p ON с. ProductID = p. ProductID) ON c. CustomerID = o. CustomerID ГДЕ c. Город = «Мадрид»

Теперь мы получим фактическую таблицу LEFT OUTER JOIN dbo.Customers вместе со всеми деталями заказа.

Первое объединение будет представлять собой комбинацию таблицы dbo.Customers с результатом операций в скобках. Все соединения внутри кронштейна будут выполнены первыми. Первая таблица dbo.Orders с dbo. [Детали заказа]. Затем таблица dbo.Products будет прикреплена к результату.

Только в самом конце будет выполнено внешнее левостороннее соединение, таблица dbo.Customers с эффектом действий в скобках (все детали заказов).

В качестве альтернативы, мы можем сохранить соединения без скобок, используя RIGHT OUTER JOIN:

SELECT c. CompanyName, o. OrderID, от. Количество, стр. ProductName ОТ ДБО. Заказы на INNER JOIN dbo. [Детали заказа] с ON с. OrderID = o. OrderID ВНУТРЕННЕЕ СОЕДИНЕНИЕ Продукты п ПО от. ProductID = стр. ProductID ПРАВО НАРУЖНОГО РЕЙТИНГА dbo. Клиенты c ON c CustomerID = o. CustomerID ГДЕ c. City = 'Мадрид'

Результат будет идентичным. Во-первых, все ВНУТРЕННИЕ СОЕДИНЕНИЯ будут выполнены первыми. В самом конце будет включена таблица dbo.Cutomers, добавляющая всех клиентов без заказов (RIGHT OUTER JOIN) на последнем шаге.

Теперь подумайте, где мы должны фильтровать записи, когда речь идет о датах заказа. Чего мы добьемся, используя фильтрацию WHERE?

SELECT c. CompanyName, o. OrderID, от. Количество, стр. ProductName ОТ ДБО. Клиенты c ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ (дБ. Заказы на INNER JOIN dbo. [Детали заказа] из ON с. OrderID = o. OrderID INNER JOIN dbo. Продукты p ON с. ProductID = p. ProductID) ON c. CustomerID = o. CustomerID ГДЕ c. City = 'Madrid' и (o. OrderDate МЕЖДУ '1996-09-01' И '1996-09-30' ИЛИ ​​o. OrderDate является нулевым)

OrderDate является нулевым)

ГДЕ фильтрация не вызывает сомнений. Как только записи были удалены, они не имеют права появляться больше в наборе результатов. Не существует механизма для «добавления» элементов, таких как в случае внешних подключений. Вот почему, к сожалению, вышеупомянутая запись показала нам всех клиентов из Мадрида, которые размещали заказы, но в разные даты.

Напомню, что мы тоже хотели увидеть их в конечном результате. Таким образом, мы потеряли знания о компании "Bólido Comidas preparadas" здесь.

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

SELECT c. CompanyName, o. OrderID, от. Количество, стр. ProductName ОТ ДБО. Клиенты c ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ (дБ. Заказы на INNER JOIN dbo. [Детали заказа] у него от. OrderID = o. OrderID INNER JOIN dbo. Продукты с тех пор. ProductID = p. ProductID) ON c. CustomerID = o. CustomerID и o. OrderDate МЕЖДУ «1996-09-01» И «1996-09-30» ГДЕ c. Город = «Мадрид»

Благодаря этому лечению мы получим знания обо ВСЕХ клиентах из Мадрида. Кроме того, заказы за определенный промежуток времени.

Логический и физический порядок объединения множеств

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

Обратите внимание, что иногда стоит заглянуть немного дальше, например, чтобы отфильтровать WHERE, чтобы уже на этапе FROM, не анализировать все записи, которые будут отфильтрованы позже. Кроме того, физический порядок изготовления соединений, фильтрации - тоже важен для производительности.

Выбор наиболее короткого и эффективного метода доступа к данным - это, прежде всего, задача оптимизатора запросов. Важнейшим принципом его действий всегда является предсказуемый конечный результат. Он должен на 100% соответствовать описанным теоретическим принципам.

Оптимизатор может ограничивать и эффективно комбинировать файлы. Декартовы произведения, присутствующие в теоретическом анализе или сборе всех строк таблицы, обычно не имеют места.
Давайте по-другому сохраним запрос, который реализует задачу из первого примера:

- Первая таблица, на которую мы ссылаемся, это dbo. Клиенты SELECT c. CompanyName, o. OrderID, o. OrderDate, ProductName FROM dbo. Клиенты могли присоединиться к dbo. Заказы о нем c. CustomerID = o. CustomerID внутреннее соединение dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID внутреннее соединение dbo. Продукты р он с тех пор. ProductID = p. ProductID ГДЕ c. City = 'Madrid' И o. OrderDate МЕЖДУ '1996-09-01' И '1996-09-30' ORDER BY CompanyName, OrderDate DESC - Первая таблица, на которую мы ссылаемся, это dbo. Заказы SELECT c. CompanyName, o. OrderID, o. OrderDate, ProductName FROM dbo. Заказы на внутреннее соединение dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID внутреннее соединение dbo. Продукты р он с тех пор. ProductID = стр. ProductID внутреннее соединение dbo. Клиенты c на C. CustomerID = o. CustomerID ГДЕ c. City = 'Мадрид' И О. OrderDate МЕЖДУ '1996-09-01' И '1996-09-30' ORDER BY CompanyName, OrderDate DESC - Первая таблица, к которой мы ссылаемся на dbo.Products SELECT c. CompanyName, o. OrderID, o. OrderDate, ProductName FROM dbo. Продукты p внутреннее соединение dbo. [Детали заказа] от него с тех пор. ProductID = стр. ProductID внутреннее соединение dbo. Приказы о нем от. OrderID = o. OrderID внутреннее соединение dbo. Клиенты c на C. CustomerID = o. CustomerID ГДЕ c. City = 'Мадрид' И o. Дата Заказа МЕЖДУ '1996-09-01' И '1996-09-30' ЗАКАЗАТЬ CompanyName, OrderDate DESC

Хотя соединения хранятся в другом порядке, ядро ​​базы данных будет реализовывать план действий по-своему. У них у всех одинаковое

У них у всех одинаковое

Вопрос, который возникает после анализа этого случая. Но может ли быть важен порядок соединений? Если это так, мы можем попытаться «оптимизировать» запрос и заставить его измениться.

Порядок и производительность подключения

Порядок важен. В итоге каждое из таких слияний может производить промежуточные объемы разных размеров. Можно предположить, что чем более избирательными будут объединения с самого начала, тем эффективнее будет обрабатываться весь запрос.

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

У меня есть возможность вмешиваться в процесс оптимизации с помощью HINT. Следующая опция используется для обеспечения порядка соединений:

Что заставляет оптимизатор установить порядок от LEFT до RIGHT. Выполните следующий скрипт, который показывает 4 запроса, которые возвращают одинаковые результаты. Используйте параметр «Включить фактический план выполнения» для сравнения планов выполнения и оценки доли загрузки каждой из этих команд в процентах.

- Запрос 1 - давайте дадим оптимизатору оптимизации выбрать c. CompanyName, o. OrderID, o. OrderDate, ProductName из dbo. Клиенты могли присоединиться к dbo. Заказы о нем c. CustomerID = o. CustomerID внутреннее соединение dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID внутреннее соединение dbo. Продукты р он с тех пор. ProductID = p. ProductID где c. City = 'Мадрид' и o. OrderDate между '1996-09-01' и '1996-09-30' заказом по CompanyName, OrderDate desc - Запрос 2 из FORCE ORDER - идентично написано как В запросе 1 выберите c. CompanyName, o. OrderID, o. OrderDate, ProductName из dbo. Клиенты могли присоединиться к dbo. Заказы о нем c. CustomerID = o. CustomerID внутреннее соединение dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID внутреннее соединение dbo. Продукты р он с тех пор. ProductID = p. ProductID где c. City = 'Мадрид' и o. OrderDate между '1996-09-01' и '1996-09-30' заказ по CompanyName, OrderDate desc ВАРИАНТ (ДЛЯ CE ORDER) - Запрос 3 из FORCE ORDER выберите c. CompanyName, o. OrderID, o. OrderDate, ProductName из dbo. Заказы на внутреннее соединение dbo. [Детали заказа] от него с тех пор. OrderID = o. OrderID внутреннее соединение dbo. Продукты р он с тех пор. ProductID = стр. ProductID внутреннее соединение dbo. Клиенты c на c. CustomerID = o. CustomerID, где City = 'Madrid' и OrderDate между '1996-09-01' и '1996-09-30' заказом по CompanyName, OrderDate desc ВАРИАНТ (ДЛЯ CE ORDER) - Запрос 4 из FORCE ORDER выберите c. CompanyName, o. OrderID, o. OrderDate, ProductName из dbo. Продукты p внутреннее соединение dbo. [Детали заказа] от него с тех пор. ProductID = стр. ProductID внутреннее соединение dbo. Приказы о нем от. OrderID = o. OrderID внутреннее соединение dbo. Клиенты c на c. CustomerID = o. CustomerID, где City = 'Madrid' и OrderDate между '1996-09-01' и '1996-09-30' заказом по CompanyName, OrderDate desc ВАРИАНТ (ДЛЯ CE ORDER)

Наилучшим показателем производительности оказался первый запрос, в котором мы позволили оптимизатору выбрать свой собственный план производительности. Любой другой, используя подсказку, принудительно применяя последовательность действий. Результаты говорят сами за себя:
Наилучшим показателем производительности оказался первый запрос, в котором мы позволили оптимизатору выбрать свой собственный план производительности

Применение подсказок может быть полезным, но оно должно использоваться осознанно. Расписания, оптимальные способы доступа к данным могут меняться. После создания план может через некоторое время стать неоптимальным, поэтому я бы не советовал использовать этот тип руководства для начинающих пользователей SQL.