���������� ������-������ 2009'3
	Home ����� ������� �������� k-press.ru

������������ �������� �� ���� �������! �� �� ������ �����������?

SQL и оператор 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).

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

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

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

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

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

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

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

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

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

===============================
--создание и заполнение таблиц
===============================
/*
DROP TABLE Table1 ;
DROP TABLE Table2 ;
DROP TABLE Table3 ;
*/

CREATE TABLE Table1 (
  key1 INT NOT NULL,
  field1 VARCHAR(50),
  field2 INT,
  key2 INT NOT NULL,
  key3 INT NOT NULL,
  PRIMARY KEY (key1));
  
  
CREATE TABLE Table2 (
  key2 INT NOT NULL,
  field1 VARCHAR(50),
  field2 VARCHAR(1),
  field3 VARCHAR(1),
  PRIMARY KEY (key2));
  
    
  
CREATE TABLE Table3 (
  key3 INT NOT NULL,
  field1 VARCHAR(50),
  PRIMARY KEY (key3));
  

INSERT INTO Table1 VALUES (3, 'Erik', 8, 1, 6) ;
INSERT INTO Table1 VALUES (5, 'John', 3, 4, 4) ;
INSERT INTO Table1 VALUES (6, 'Mark', 3, 7, 1) ;
INSERT INTO Table1 VALUES (7, 'Peter', 6, 8, 5) ;
INSERT INTO Table1 VALUES (8, 'Harry', 0, 9, 2) ;

INSERT INTO Table2 VALUES (1, 'New York', 'A', 'N') ;
INSERT INTO Table2 VALUES (2, 'Sao Paulo', 'B', 'N') ;
INSERT INTO Table2 VALUES (4, 'Paris', 'C', 'Y') ;
INSERT INTO Table2 VALUES (5, 'London', 'C', 'Y') ;
INSERT INTO Table2 VALUES (6, 'Rome', 'C', 'Y') ;
INSERT INTO Table2 VALUES (9, 'Madrid', 'C', 'Y') ;
INSERT INTO Table2 VALUES (0, 'Bangalore', 'D', 'N') ;

INSERT INTO Table3 VALUES (1, 'Engineer') ;
INSERT INTO Table3 VALUES (2, 'Surgeon') ;
INSERT INTO Table3 VALUES (3, 'DBA') ;
INSERT INTO Table3 VALUES (4, 'Lawyer') ;
INSERT INTO Table3 VALUES (5, 'Teacher') ;
INSERT INTO Table3 VALUES (6, 'Actor') ;

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

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

INNER JOIN

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

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

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

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

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.key2 as T1Key, 
       t2.key2 as T2Key, 
       t2.field1 as City
  FROM Table1 t1 INNER JOIN Table2 t2 ON t1.key2 = t2.key2;

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

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

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

FULL JOIN

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

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

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

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, 
    t2.key2 as T2Key, t2.field1 as City
  FROM Table1 t1 FULL JOIN Table2 t2 ON t1.key2 = t2.key2 ;

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

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

LEFT JOIN

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

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

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

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.key2 as T1Key, 
       t2.key2 as T2Key, 
       t2.field1 as City
  FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 ;

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

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

RIGHT JOIN

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

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

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

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.key2 as T1Key, 
       t2.key2 as T2Key, 
       t2.field1 as City
  FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.key2 = t2.key2 ;

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

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

CROSS JOIN

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

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

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

SELECT t1.key1, 
    t1.field1 as Name, 
    t1.key2 as T1Key, 
    t2.key2 as T2Key, 
    t2.field1 as City
  FROM Table1 t1 CROSS JOIN Table2 t2 ;

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

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

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

SELF JOIN

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

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

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.field2, 
       mirror.field1 as Boss
FROM Table1 t1
   LEFT JOIN Table1 mirror ON t1.field2 = mirror.key1;

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

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

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

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

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

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

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

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.key2 as T1Key, 
       t2.key2 as T2Key, 
       t2.field1 as City
FROM Table1 t1
  LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 
  WHERE t2.key2 IS NULL;

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

При выполнении таких запросов нужно правильно выбирать поле для оператора 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, мы обычно сравниваем, равно ли одно поле одной таблицы другому полю другой таблицы. Но это не обязательный синтаксис. Можно использовать любой логический оператор, например, «не равно» (<>), «больше» (>), «меньше» (<) и так далее

Это производит впечатление огромной мощи SQL, но я считаю это во многом косметической возможностью. Рассмотрим пример. В таблице 1 у нас 5 записей. Напишем вот такой запрос:

SELECT t1.key1, 
       t1.field1 as Name, 
       t1.key2 as T1Key, 
       t2.key2 as T2Key, 
       t2.field1 as City
  FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.key2 <= t2.key2
  WHERE t1.key1 = 3 ;

Здесь используется INNER JOIN, и мы выбираем единственную запись из Table1, ту, у которой key1 равен 3. Проблема в том, что есть 6 записей и Table2, удовлетворяющая условиям соединения. Посмотрите на результат запроса:

Проблема с неравенствами в соединениях – они обычно дублируют записи. А это не то, что обычно требуется. Впрочем, теперь вы знаете, как это делается.

Множественные JOIN

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

Важно, что SQL позволяет использовать в одном выражении разные типы соединений.

Но администраторы и разработчики должны быть внимательными, соединяя слишком много таблиц. Несколько раз я встречал ситуации, когда запросам требовалось 10, 20 и даже более таблиц. По причинам производительности объединять все данные в одном запросе – не самая хорошая идея. Оптимизатор запросов будет работать лучше, если разбить запрос на несколько меньших и менее сложных запросов.

Представим, что у нас есть третья таблица, Table3:

Давайте напишем выражение, выдающее имя работника, город, в котором он живет и его профессию. Это потребует соединения всех трех таблиц. Помните, что соединения всегда пишутся попарно. Сперва мы соединяем Table1 и Table2, а затем – Table1 и Table3. Код запроса приведен ниже:

SELECT t1.key1, 
       t1.field1 as Employee, 
       t2.key2, 
       t2.field1 as City,
       t3.key3, 
       t3.field1 as Profession
  FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.key2 = t2.key2
    INNER JOIN Table3 t3 ON t1.key3 = t3.key3;

Поскольку мы исполняем только INNER JOIN, мы получим только записи, соответствующие комбинации 3 таблиц. См. результат ниже:

За пределами выражения SELECT

Использование операторов JOIN не ограничивается выражениями SELECT. В Т-SQL JOIN можно использовать также в выражениях INSERT, DELETE и UPDATE. Но надо помнить, что большинство современных РСУБД не поддерживает JOIN в выражениях DELETE и UPDATE. Поэтому я советую ограничить использование JOIN выражениями SELECT и INSERT, даже в коде, предназначенном для SQL Server. Это важно, если вы хотите поддерживать переносимость вашего кода на другие платформы.

Заключение

Соединение данных из нескольких таблиц действительно важно при работе с РСУБД. Операторы JOIN предоставляют мощный, быстрый и гибкий способ выборки нужных данных.

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

Copyright � 1994-2016 ��� "�-�����"