No Image

Inner join sql server

СОДЕРЖАНИЕ
6 просмотров
10 марта 2020

В прошлой теме было рассмотрено неявное соединение таблиц. Оно производилось на основе простой выборки неявно путем сведения данных. Для явного соединения данных из двух таблиц применяется оператор JOIN . Общий формальный синтаксис применения оператора INNER JOIN:

После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER . Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.

Возьмем таблицы с данными из прошлой темы:

Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:

Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, "Orders.ProductCount".

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

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

Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:

Условия после ключевого слова ON могут быть более сложными по составу:

В данном случае выбираем все заказы на товары, производителем которых является Apple.

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

Автор: Wagner Crivelini
Опубликовано: 09.07.2010
Версия текста: 1.1

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

Но настоящие запросы зачастую гораздо сложнее, чем простые выражения SELECT.

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

В прошлом администраторы БД и разработчики помещали все нужные таблицы и/или представления в оператор FROM, а затем использовали оператор WHERE, чтобы определить, как должны комбинироваться записи из одной таблицы с записями из другой (чтобы сделать этот текст чуть-чуть более читаемым, я в дальнейшем буду писать просто «таблица», а не «таблица и/или представление»).

Однако, чтобы стандартизовать объединение данных, понадобилось довольно много времени. Это было сделано с помощью оператора JOIN (ANSI-SQL 92). К сожалению, некоторые детали использования оператора JOIN так и остаются неизвестными очень многим.

Прежде чем показать различный синтаксис JOIN, поддерживаемый T-SQL (в SQL Server 2008), я опишу несколько концепций, которые не следует забывать при любом соединении данных из двух или нескольких таблиц.

Начало: одна таблица, никакого JOIN

Если запрос обращается только к одному объекту, синтаксис будет очень простым, и никакое соединение не потребуется. Выражение будет старым добрым " SELECT fields FROM object " с другими необязательными операторами (то есть WHERE, GROUP BY, HAVING или ORDER BY).

Однако конечные пользователи не знают, что администраторы БД обычно прячут множество сложных соединений за одним красивым и простым в использовании представлением. Это делается по разным причинам, от безопасности данных до производительности БД. Например, администратор может дать конечному пользователю разрешение на доступ к одному представлению вместо нескольких рабочих таблиц, что, очевидно, повышает сохранность данных. А если говорить о производительности, можно создать представление, используя правильные параметры для соединения записей из разных таблиц, правильно использовать индексы и тем самым повысит производительность запроса.

Читайте также:  Темы дизайна для powerpoint 2007

Как бы то ни было, соединения в БД всегда есть, даже если конечный пользователь их и не видит.

Логика, стоящая за соединением таблиц

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

Рисунок 1 очень похож на картинки из учебника для первого класса. Идея в том, чтобы найти в разных множествах соответствующие объекты. Это как раз то, чем занимается JOIN в SQL!


Рисунок 1. Комбинируем объекты из разных множеств.

Если вы поняли эту аналогию, все становится более осмысленным.

Представьте, что 2 множества на рисунке 1 – это таблицы, а цифры – это ключи, используемые для соединения таблиц. Таким образом, в каждом из множеств вместо целой записи мы видим только ключевые поля каждой таблицы. Результирующий набор комбинаций будет определяться типом используемого соединения, и это я как раз и собираюсь показать. Чтобы проиллюстрировать примеры, возьмем 2 таблицы, показанные ниже:

Таблица Table1

Таблица Table2

Скрипт для создания и заполнения таблиц приведен ниже:

Как можно заметить, этот скрипт не полностью обеспечивает ссылочную целостность. Я намеренно оставил таблицы без внешних ключей, чтобы лучше объяснить функциональность разных типов JOIN. Но я сделал это исключительно в целях обучения. Внешние ключи крайне полезны для обеспечения непротиворечивости данных, и их нельзя исключить ни из одной реальной БД.

Теперь мы готовы. Давайте рассмотрим типы JOIN, имеющиеся в T-SQL, их синтаксис и результаты, генерируемые ими.

INNER JOIN

Это наиболее часто используемое в SQL соединение. Оно возвращает пересечение двух множеств. В терминах таблиц, оно возвращает только записи из обеих таблиц, отвечающие указанному критерию.

На рисунке 2 показана диаграмма Венна, иллюстрирующая пересечение двух таблиц. Результат операции – закрашенная область.


Рисунок 2. INNER JOIN.

Теперь посмотрите на синтаксис объединения данных из таблиц Table1 и Table2 с использованием INNER JOIN.

Вот набор результатов, возвращаемый этим выражением:

Заметьте, что выдаются только данные из записей, имеющих одинаковые значения key2 в таблицах Table1 и Table2 .

Противоположностью INNER JOIN является OUTER JOIN. Существует три типа OUTER JOIN – полный, левый и правый. Рассмотрим каждый из них.

FULL JOIN

Полностью это соединение называется FULL OUTER JOIN (зарезервированное слово OUTER необязательно). FULL JOIN работает как объединение двух множеств. На рисунке 3 показана диаграмма Венна для FULL JOIN двух таблиц. Результатом операции опять же является закрашенная область.


Рисунок 3. FULL JOIN.

Синтаксис почти такой же, как показанный выше:

Набор результатов, возвращаемых этим выражением, выглядит так:

FULL JOIN возвращает все записи из таблиц Table1 и Table2 , без повторяющихся данных.

LEFT JOIN

Также известен как LEFT OUTER JOIN, и является частным случаем FULL JOIN. Дает все запрошенные данные из таблицы в левой части JOIN плюс данные из правой таблицы, пересекающиеся с первой таблицей. На рисунке 4 показана диаграмма Венна, иллюстрирующая LEFT JOIN для двух таблиц.


Рисунок 4. LEFT JOIN.

Результатом этого выражения будет:

Третья и четвертая записи ( key1 равен 6 и 7) содержат NULL-значения в последнем поле, потому что для них нет информации из второй таблицы. Это значит, что у нас есть значение в поле key2 в Table1 , но нет соответствующего ему значения в Table2 .

RIGHT JOIN

Также известен как RIGHT OUTER JOIN, и является еще одним частным случаем FULL JOIN. Он выдает все запрошенные данные из таблицы, стоящей в правой части оператора JOIN, плюс данные из левой таблицы, пересекающиеся с правой. Диаграмма Венна для RIGHT JOIN двух таблиц показана на рисунке 5.


Рисунок 5. RIGHT JOIN.

Как видите, синтаксис очень похож на показанный выше:

Результатом этого выражения будет:

Как видите, теперь записи с key1 , равным 6 и 7, отсутствуют в результатах, потому что для них нет соответствующих записей в правой таблице. Четыре записи содержат NULL в первом поле, поскольку для них нет данных в левой таблице.

CROSS JOIN

CROSS JOIN – это на самом деле Декартово произведение. При использовании CROSS JOIN генерируется точно тот же результат, что и при вызове двух таблиц (разделенных запятой) без всякого JOIN вообще. Это значит, что мы получим огромный набор результатов, где каждая запись из Table1 будет дублирована для каждой записи из Table2 . Если в Table1 содержится N1 записей, а в Table2 – N2 записей, в результате будет N1 х N2 записей.

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

Синтаксис CROSS JOIN таков:

Поскольку в Table1 содержится 5 записей, а в Table2 – еще 7, результат этого запроса будет содержать 35 записей (5 x 7).

Совершенно честно, я не могу сейчас припомнить ни одной реальной ситуации, когда мне понадобилось бы сгенерировать декартово произведение двух таблиц. Но если оно вам понадобится, есть CROSS JOIN.

Кроме всего прочего, стоит подумать и о производительности. Допустим, что вы случайно запустили на рабочем сервере запрос, содержащий CROSS JOIN для двух таблиц по миллиону записей в каждой. Это, несомненно, добавит вам головной боли. Возможно, у вашего сервера начнутся проблемы с производительностью, поскольку это запрос будет исполняться долго, и потреблять при этом существенное количество ресурсов сервера.

SELF JOIN

Оператор JOIN можно использовать для комбинирования любой пары таблиц, включая комбинацию таблицы с самой собой. Это и есть «SELF JOIN».

Посмотрите на классический пример, возвращающий имя начальника сотрудника (по таблице 1). В этом примере мы полагаем, что значение в field2 – фактически кодовый номер босса, следовательно, он связан с key1.

А вот результат запроса:

Последняя запись в данном примере показывает, что у Гарри нет начальника, другими словами, он №1 в иерархии компании.

Исключение пересечения множеств

Посмотрев на последнюю диаграмму Венна из приведенных выше, можно задаться простым вопросом: что, если мне нужны все записи из Table1, кроме тех, для которых есть соответствующие записи в Table2 . Что ж, это крайне полезно в повседневной работе, но для этого, очевидно, не нужен особый оператор JOIN.


Рисунок 6. Непересекающиеся записи в Таблице 1.

Посмотрите на предыдущие наборы результатов, и вы увидите, что нужно всего лишь добавить в SQL-запрос оператор WHERE, чтобы найти записи, содержащие NULL в ключе Table2 . Это даст нам набор результатов, соответствующий диаграмме Венна, показанной на рисунке 6.

Можно в этом запросе написать LEFT JOIN, например:

И, наконец, набор результатов будет выглядеть так:

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

Слово о планах исполнения

По ходу действия мы подошли к важному моменту. Обычно мы не задумываемся об этом, но нужно знать, что планы исполнения SQL-запросов сперва вычисляют результат операторов FROM и JOIN (если таковой имеется), а только затем исполняют оператор WHERE.

Это верно как для SQL Server, так и для любой другой РСУБД.

Базовое понимание работы SQL важно для любого администратора БД или разработчика. Это помогает в работе. Если вам интересно, посмотрите на план выполнения запроса, приведенного выше (рисунок 7).


Рисунок 7. План исполнения запроса, использующего LEFT JOIN.

JOIN и индексы

Посмотрите еще раз на план исполнения запроса. Заметьте, он использует кластерные индексы для обеих таблиц. Использование индексов – лучший способ ускорить выполнение запросов. Но нужно обращать внимание на ряд деталей.

При создании запросов мы ожидаем, что SQL Server Query Optimizer будет использовать индексы таблиц для увеличения производительности. Мы также можем помочь Query Optimizer-у выбрать индексированные поля, являющиеся частью запроса.

Например, при использовании оператора JOIN, идеальный подход состоит в том, чтобы соединение основывалось на индексированных полях. Посмотрев в план исполнения, можно заметить, что используется кластерный индекс для Table2 . Этот индекс был автоматически создан по key2 при создании таблицы, поскольку key2 – это первичный ключ этой таблицы.

С другой стороны, таблица Table1 не индексирована по полю key2 . Из-за этого оптимизатор запросов пытается быть умным и увеличить производительность запроса к key2, используя единственный доступный индекс. Это табличный кластерный индекс, основанный на key1 , первичном ключе Table1 . Как видите, оптимизатор запросов – действительно умное средство. Но вы сильно поможете ему, если создадите новый (некластерный) индекс по key2 .

Если не забывать о ссылочной целостности, поле key2 должно быть внешним ключом Table1 , поскольку оно связано с другим полем другой таблицы (то есть Table2.key2 ).

Лично я считаю, что внешние ключи должны присутствовать во всех реальных моделях БД. Причем это хорошая идея – создавать некластерные индексы для всех внешних ключей. Вы всегда будете исполнять множество запросов, а также использовать оператор JOIN, основываясь на первичных и внешних ключах.

Важно: SQL Server автоматически создает кластерный индекс для первичных ключей. Однако по умолчанию он ничего не делает с внешними ключами. Проверьте, что ваша СУБД настроена надлежащим образом.

Неравенства

При создании SQL-запросов, использующих оператор JOIN, мы обычно сравниваем, равно ли одно поле одной таблицы другому полю другой таблицы. Но это не обязательный синтаксис. Можно использовать любой логический оператор, например, «не равно» (<>), «больше» (>), «меньше» ( Table1 , ту, у которой key1 равен 3. Проблема в том, что есть 6 записей и Table2, удовлетворяющая условиям соединения. Посмотрите на результат запроса:

I have a database query:

In SQL Server I get the error near : BR WITH (NOLOCK) that :

Incorrect syntax near the keyword ‘WITH’. Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

But as per my understanding from sources like the syntax is as

So, my query looks just about right.

Also, when I remove the WITH (NOLOCK) next to BR WITH (NOLOCK) i.e. my inner join query the query runs fine. Any ideas of what I might be missing??

PS: My DB compatibility level is 110.

Комментировать
6 просмотров
Комментариев нет, будьте первым кто его оставит

Это интересно
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
No Image Компьютеры
0 комментариев
Adblock detector