SQL стейтмент JOIN

Материал из Энциклопедия о программировании
Перейти к: навигация, поиск

SQL стейтмент JOIN (соединить) сопоставляет (проверяет на равенство) поля одной табл. с полями др. Входит в раздел FROM операторов SELECT, UPDATE или DELETE. Явл. реализацией операции соединения реляционной алгебры.

SQL стейтмент JOIN возвращает указанную (внутреннюю, внешнюю и пр.) результирующую табл., полученную по условиям соответствия знач. равенства полей соединённых таблиц. SQL стейтмент JOIN определяет область 2-х табл., т.е. по условиям из 2-х реальных табл. создаёт 3-ю виртуальн. табл., из которой выбирает данные.

Несмотря на то, что, теоретически, объединять можно по любым условиям, в большинстве СУБД JOIN это только присоединение по равенству и никак иначе.

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

  • В схему табл.-результата входят столбцы обеих исходных табл. (табл.-операндов), т.е. схема результата явл. «сцеплением» схем операндов;
  • Каждая строка табл.-результата явл. «сцеплением» строки из 1-ой табл.-операнда со строкой 2-ой табл.-операнда.

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

Арность

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

Наглядный пример использования оператора JOIN

Есть 2 табл.: tags(id, name) и tag_links(tag_id, post_id). Делается запрос:

SELECT tags.name 
FROM tags
LEFT JOIN tag_links ON tag_links.tag_id = tags.id
WHERE tag_links.post_id = 1

Так выберутся названия тегов для записи с post_id = 1.

Расшифровать запрос можно так:
Берём name из tags, присоеденяем табл. tag_links со знач., удовлетворяющими tag_links.tag_id = tags.id, где tag_links.post_id = 1

Общий синтаксис оператора JOIN

SELECT <столбцы> FROM <Table1> [<условия присоединения>] JOIN <Table2> ON <condition>

Пример выше словами можно описать следующим образом: «Выбрать <столбцы> из таблицы <Table1> объединённой по равенству с таблицей <Table2> согласно следующему условию <condition>». В условии пишется столбец из первой таблицы равный столбцу из второй. Значения из этих столбцов и будут сопоставляться, если они равны то результат равных строк будет возвращён.

Присоединяем таблицу <Table1> к таблице <Table2>. Какие именно столбцы одной таблицы соединять со столбцами другой и по какому условию описывается в блоке <condition> (в большинстве СУБД поддерживается только условие равенства, поэтому принято считать, что оператор JOIN сливает указанные столбцы двух таблиц только по условию равенства).

Условия присоединения для таблиц

  • LEFT — выбирает все записи из первой (левой) таблицы
  • RIGHT — выбирает все записи из втрой (правой) таблицы
  • FULL — выбирает все записи из обеих таблиц (левой и правой)

LEFT и RIGHT выбирают все записи из заданной таблицы и соответствующие ей записи из другой таблицы.

Описание оператора

select 
  field [,... n]
from 
  Table1
  {inner | {left | right | full} outer | cross } join 
  Table2 
    on <condition>

В большинстве СУБД при указании слов LEFT, RIGHT и FULL, слово OUTER можно опустить. Слово INNER также в большинстве СУБД можно опустить.

В общем случае СУБД при выполнении соединения проверяет условие (предикат) condition. Для CROSS JOIN условие не указывается.

Для перекрёстного соединения (декартова произведения) CROSS JOIN в некоторых реализациях SQL используется оператор «запятая» (,):

select 
  field [,... n]
from 
  Table1, 
  Table2

Виды оператора JOIN

Для дальнейших пояснений будут использоваться след. табл.:

City
Id Name
1 London
2 Paris
3 Prague
Person
Name CityId
Alex 1
John 2
Michael 1
Brad 4

INNER JOIN

Оператор внутреннего соединения INNER JOIN соединяет 2 табл. Порядок табл. для оператора неважен, поскольку оператор явл. симметричным.

Заголовок таблицы-результата явл. объединением (конкатенацией) заголовков соединяемых табл.

Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.

Описанный алгоритм действий является строго логическим, то есть он лишь объясняет результат, который должен получиться при выполнении операции, но не предписывает, чтобы конкретная СУБД выполняла соединение именно указанным образом. Существует множество способов реализации операции соединения, например соединение вложенными циклами (англ. inner loops join), соединение хэшированием (англ. hash join), соединение слиянием (англ. merge join). Единственное требование состоит в том, чтобы любая реализация логически давала такой же результат, как при применении описанного алгоритма.

select * 
from
  Person
  inner join 
  City 
    on Person.CityId = City.Id

Результат:

Person.Name Person.CityId City.Id City.Name
Alex 1 1 London
John 2 2 Paris
Michael 1 1 London

OUTER JOIN

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

LEFT OUTER JOIN

Оператор левого внешнего соединения LEFT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.

  1. В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p.
  2. Затем в результат добавляются те записи левой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие правой таблице, заполняются значениями NULL.
select * 
from 
  Person
  left outer join 
  City 
    on Person.CityId = City.Id

Результат:

Person.Name Person.CityId City.Id City.Name
Alex 1 1 London
John 2 2 Paris
Michael 1 1 London
Brad 4 NULL NULL

RIGHT OUTER JOIN

Оператор правого внешнего соединения RIGHT OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение левой и правой таблиц по предикату (условию) p.

  1. В результат включается внутреннее соединение (INNER JOIN) левой и правой таблиц по предикату p.
  2. Затем в результат добавляются те записи правой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие левой таблице, заполняются значениями NULL.
select * 
from 
  Person
  right outer join 
  City 
    on Person.CityId = City.Id

Результат:

Person.Name Person.CityId City.Id City.Name
Alex 1 1 London
Michael 1 1 London
John 2 2 Paris
NULL NULL 3 Prague

FULL OUTER JOIN

Оператор полного внешнего соединения FULL OUTER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

Тело результата логически формируется следующим образом. Пусть выполняется соединение первой и второй таблиц по предикату (условию) p. Слова «первой» и «второй» здесь не обозначают порядок в записи (который неважен), а используются лишь для различения таблиц.

  1. В результат включается внутреннее соединение (INNER JOIN) первой и второй таблиц по предикату p.
  2. В результат добавляются те записи первой таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие второй таблице, заполняются значениями NULL.
  3. В результат добавляются те записи второй таблицы, которые не вошли во внутреннее соединение на шаге 1. Для таких записей поля, соответствующие первой таблице, заполняются значениями NULL.
select * 
from 
  Person
  full outer join 
  City 
    on Person.CityId = City.Id

Результат:

Person.Name Person.CityId City.Id City.Name
Alex 1 1 London
Michael 1 1 London
John 2 2 Paris
NULL NULL 3 Prague
Brad 4 NULL NULL

CROSS JOIN

Оператор перекрёстного соединения, или декартова произведения CROSS JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.

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

select * 
from 
  Person
  cross join 
  City

или

select * 
from 
  Person, 
  City

Результат:

Person.Name Person.CityId City.Id City.Name
Alex 1 1 London
Alex 1 2 Paris
Alex 1 3 Prague
John 2 1 London
John 2 2 Paris
John 2 3 Prague
Michael 1 1 London
Michael 1 2 Paris
Michael 1 3 Prague
Brad 4 1 London
Brad 4 2 Paris
Brad 4 3 Prague

Если в предложении WHERE добавить условие соединения, то есть ограничения на сочетания кортежей, то результат эквивалентен операции INNER JOIN с таким же условием:

select * 
from 
  Person, 
  City 
where 
  Person.CityId = City.Id

Таким образом, CROSS JOIN + WHERE(предикат) и INNER JOIN (предикат) синтаксически являются альтернативными формами записи одной и той же логической операции внутреннего соединения. Синтаксис CROSS JOIN + WHERE для операции соединения называют устаревшим, поскольку его не рекомендует стандарт SQL ANSI.

Примеры

SELECT * FROM articles JOIN articles_text ON articles.id = articles_text.id WHERE MATCH (text) AGAINST ('искомое слово' IN BOOLEAN MODE) LIMIT 5;

Несколько таблиц в FROM помимо общей, созданной с помощью JOIN

В операторе FROM помимо одной общей, созданной операцией объединения с помощью оператора JOIN, таблицы можно также как и обычно указывать/использовать и др. таблицы, но в их списке не должно быть таблиц, что уже были использованы в объединении, т.е. в операторе JOIN:

SELECT prd.id, prd.ttl, prd.cat, prd.sec, prd.ver, prd.dcr, prd.sts, prd.siz, sts.ttl, sec.ttl, cat.ttl FROM prd JOIN nvlt ON prd.id = nvlt.id, sts, sec, cat WHERE prd.sts = sts.id AND prd.sec = sec.id AND prd.cat = cat.id ORDER BY prd.date DESC, prd.id DESC LIMIT 10;

Несколько условий объединения в операторе JOIN

В операторе JOIN можно указывать (объединять) только 2 таблицы, но условий их объединения может быть много, а не одно, как это обычно используется и показывается в большинстве примеров. Условия равенства разных полей перечисляются через оператор AND:

SELECT pg_ns, pg_ttl, cat_timstmp FROM page INNER JOIN cat ON page.pg_id = cat.from AND cat.fruit = 'pear';

Оператор AS в операторе JOIN

В операторе JOIN можно использовать оператор AS, который будет работать точно также, как и всегда.
Использование оператора AS в операторе JOIN:

SELECT page_namespace, page_title, c1.cl_timestamp FROM page INNER JOIN category AS c1 ON page_id = c1.cl_from AND c1.cl_to = 'Algemeen';

Может показаться, что здесь мы внутренним объединением (INNER JOIN) объединяем таблицы page и category в таблицу и оператором AS называем её c1, и далее явно обращаемся к этой, созданной объединением и названной как c1, таблице, но это вовсе не так! В операторе JOIN оператор AS работает точно также, как и везде, — создаёт мнемонику/алиас для предыдущей/последней сущности, в данном случае для таблицы category. То есть в данном примере c1 это алиас лишь для таблицы category, а вовсе не для общей объединённой с помощью JOIN таблицы.

Ссылки