JOIN в MySQL: INNER, LEFT, RIGHT, FULL, CROSS. Что это и как использовать. SQL Ключевое слово JOIN

Что такое JOIN?

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

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

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

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

id | name | email | age —-+—————+———————+—— 1 | John Smith | johnsmith@gmail.com | 25 2 | Jane Doe | janedoe@Gmail.com | 28 3 | Xavier Wills | xavier@wills.io | 3 … (7 rows)

А таблица с адресами может быть такой:

id | street | city | state | user_id —-+——————-+—————+——-+——— 1 | 1234 Main Street | Oklahoma City | OK | 1 2 | 4444 Broadway Ave | Oklahoma City | OK | 2 3 | 5678 Party Ln | Tulsa | OK | 3 (3 rows)

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

Именно для этого, собственно, и нужны операции объединения!

Чуть позже мы рассмотрим, как составлять подобные запросы, а пока взгляните, как может вы глядеть результат объединения таблиц:

id | name | email | age | id | street | city | state | user_id —-+—————+———————+——+—-+——————-+—————+——-+——— 1 | John Smith | johnsmith@gmail.com | 25 | 1 | 1234 Main Street | Oklahoma City | OK | 1 2 | Jane Doe | janedoe@Gmail.com | 28 | 2 | 4444 Broadway Ave | Oklahoma City | OK | 2 3 | Xavier Wills | xavier@wills.io | 35 | 3 | 5678 Party Ln | Tulsa | OK | 3 (3 rows)

Мы видим всех наших пользователей сразу с их адресами.

Но операции объединения позволяют не просто выводить такие вот комбинированные сведения. У них есть еще одна важная функция: с их помощью можно получать отфильтрованные результаты.

Например, если мы хотим послать настоящие бумажные письма всем пользователям, живущим в Оклахоме, мы можем объединить таблицы и отфильтровать результаты по столбцу city.

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

Основные принципы соединения

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

Соединение определяет способ связывания двух таблиц в запросе следующим образом:

• для каждой таблицы указываются столбцы, используемые в соединении. В типичном условии соединения указывается внешний ключ из одной таблицы и связанный с ним ключ из другой таблицы;
• указывается логический оператор (например, = или <>,) для сравнения значений столбцов.

Соединения выражаются логически с помощью следующего синтаксиса Transact-SQL:

• INNER JOIN
• LEFT [ OUTER ] JOIN
• RIGHT [ OUTER ] JOIN
• FULL [ OUTER ] JOIN
• CROSS JOIN

Внутренние соединения можно задавать в предложениях FROM и WHERE. Внешние соединения и перекрестные соединения можно задавать только в предложении FROM. Условия соединения сочетаются с условиями поиска WHERE и HAVING для управления строками, выбранными из базовых таблиц, на которые ссылается предложение FROM.

То, что условия соединения задаются в предложении FROM, помогает отделить их от условий поиска, которые могут быть заданы в предложении WHERE. Объединение рекомендуется задавать именно таким способом. Ниже приведен упрощенный синтаксис соединения с использованием предложения FROM стандарта ISO:

FROM first_table < join_type > second_table [ ON ( join_condition ) ]

join_type указывает на выполняемый тип соединения: внутреннее, внешнее или перекрестное. join_condition определяет предикат, который будет вычисляться для каждой пары соединяемых строк. Ниже приведен пример предложения FROM с заданным соединением:

FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON ( ProductVendor.BusinessEntityID = Vendor.BusinessEntityID )

Ниже приведена простая инструкция SELECT, использующая это соединение:

SELECT ProductID, Purchasing.Vendor.BusinessEntityID, Name FROM Purchasing.ProductVendor INNER JOIN Purchasing.Vendor ON (Purchasing.ProductVendor.BusinessEntityID = Purchasing.Vendor.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N’F%’ GO

Инструкция SELECT возвращает наименование продукта и сведения о поставщике для всех сочетаний запчастей, поставляемых компаниями с названиями на букву F и стоимостью продукта более 10 долларов.

Если один запрос содержит ссылки на несколько таблиц, то все ссылки столбцов должны быть однозначными. В предыдущем примере как таблица ProductVendor, так и таблица Vendor содержат столбец с именем BusinessEntityID. Имена столбцов, совпадающие в двух или более таблицах, на которые ссылается запрос, должны уточняться именем таблицы. Все ссылки на столбец Vendor в этом примере являются полными.

Если имя столбца не дублируется в двух или более таблицах, указанных в запросе, то ссылки на него уточнять именем таблицы не обязательно. Это показано в предыдущем примере. Подобное предложение SELECT иногда трудно понять, поскольку в нем нет ничего, что указывало бы на таблицы, из которых берутся столбцы. Запрос гораздо легче читать, если все столбцы указаны с именами соответствующих таблиц. Запрос будет читаться еще легче, если используются псевдонимы таблиц, особенно когда имена таблиц сами должны уточняться именами базы данных и владельца. Ниже приведен тот же пример, но чтобы упростить чтение, используются псевдонимы таблиц, уточняющие названия столбцов.

SELECT pv.ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv INNER JOIN Purchasing.Vendor AS v ON (pv.BusinessEntityID = v.BusinessEntityID) WHERE StandardPrice > $10 AND Name LIKE N’F%’;

В предыдущем примере условие соединения задается в предложении FROM, что является рекомендуемым способом. В следующем запросе это же условие соединения указывается в предложении WHERE:

SELECT pv.ProductID, v.BusinessEntityID, v.Name FROM Purchasing.ProductVendor AS pv, Purchasing.Vendor AS v WHERE pv.BusinessEntityID=v.BusinessEntityID AND StandardPrice > $10 AND Name LIKE N’F%’;

Список SELECT для соединения может ссылаться на все столбцы в соединяемых таблицах или на любое подмножество этих столбцов. Список SELECT не обязательно должен содержать столбцы из каждой таблицы в соединении. Например, в соединении из трех таблиц связующим звеном между одной из таблиц и третьей таблицей может быть только одна таблица, при этом список выборки не обязательно должен ссылаться на столбцы средней таблицы. Это так называемое антиполусоединение.

Хотя обычно в условиях соединения для сравнения используется оператор равенства (=), можно указать другие операторы сравнения или реляционные операторы, равно как другие предикаты. Дополнительные сведения см. в разделах Операторы сравнения (Transact-SQL) и WHERE (Transact-SQL).

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

Столбцы, используемые в условии соединения, не обязательно должны иметь одинаковые имена или одинаковый тип данных. Однако если типы данных не совпадают, то они должны быть совместимыми или допускать в SQL Server неявное преобразование. Если типы данных не допускают неявное преобразование, то условия соединения должны явно преобразовывать эти типы данных с помощью функции CAST. Дополнительные сведения о подразумеваемых и явных преобразованиях см. в статье Преобразование типов данных (ядро СУБД).

Большинство запросов, использующих соединение, можно переписать с помощью вложенных запросов и наоборот. Дополнительные сведения о вложенных запросах см. в разделе Вложенные запросы.

Примечание

Таблицы невозможно соединять непосредственно по столбцам ntext, text или image. Однако соединить таблицы по столбцам ntext, text или image можно косвенно, с помощью SUBSTRING.
Например, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) выполняет внутреннее соединение двух таблиц по первым 20 символам текстовых столбцов в таблицах t1 и t2.
Другая возможность сравнения столбцов ntext и text из двух таблиц заключается в сравнении длины столбцов с предложением WHERE, например: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

Основные сведения о соединениях вложенных циклов

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

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

В простейшем случае во время поиска целиком просматривается таблица или индекс; это называется упрощенным соединением вложенных циклов. Если при поиске используется индекс, то такой поиск называется индексным соединением вложенных циклов. Если индекс создается в качестве части плана запроса (и уничтожается после завершения запроса), то он называется временным индексным соединением вложенных циклов. Все эти варианты учитываются оптимизатором запросов.

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

Если для атрибута OPTIMIZED оператора соединения вложенными циклами задано значение True, это означает, что оптимизированные соединения вложенными циклами (или пакетная сортировка) используются для уменьшения количества операций ввода-вывода, когда внутренняя таблица имеет большой размер, независимо от того, выполняется ли ее параллельная обработка. Такая оптимизация в этом плане выполнения может быть не слишком очевидна при анализе плана, если сама сортировка выполняется как скрытая операция. Но изучив XML-код плана для атрибута OPTIMIZED, можно обнаружить, что соединение вложенными циклами, возможно, попытается изменить порядок входных строк, чтобы повысить производительность операций ввода-вывода.

Основные сведения о соединениях слиянием

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

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

Так как каждый набор входных данных сортируется, оператор Merge Join получает строку из каждого набора входных данных и сравнивает их. Например, для операций внутреннего соединения строки возвращаются в том случае, если они равны. Если они не равны, строка с меньшим значением не учитывается, и из этого набора входных данных берется другая строка. Этот процесс повторяется, пока не будет выполнена обработка всех строк.

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

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

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

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

Есть две таблицы 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 [<условия присоединения=»»>] JOIN ON
Пример выше словами можно описать следующим образом: «Выбрать <столбцы> из таблицы объединённой по равенству с таблицей согласно следующему условию ». В условии пишется столбец из первой таблицы равный столбцу из второй. Значения из этих столбцов и будут сопоставляться, если они равны то результат равных строк будет возвращён.

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

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

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

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

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

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

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

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

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

select field [,… n] from Table1, Table2

SQL INNER JOIN

Команда INNER JOIN возвращает строки, имеющие совпадающие значения в обеих таблицах.

Следующий SQL выбирает все заказы с информацией о клиенте:

Пример

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; Попробуйте сами »

Примечание: Ключевое слово INNER JOIN выбирает все строки из обеих таблиц до тех пор, пока существует соответствие между столбцами. Если в таблице «Заказы» есть записи, которые не имеют совпадений в разделе «Клиенты», то эти заказы не будут показаны!

Следующая инструкция SQL выбирает все заказы с информацией о клиенте и грузоотправителе:

Пример

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

SQL LEFT JOIN

Команда LEFT JOIN возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Результат будет NULL с правой стороны, если нет совпадения.

В следующем SQL будут выбраны все клиенты и любые заказы, которые они могут иметь:

Пример

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Примечание: Ключевое слово LEFT JOIN возвращает все записи из левой таблицы (Клиенты), даже если в правой таблице (Заказы) нет совпадений.

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

Пример

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID; Попробуйте сами »

Примечание: Ключевое слово RIGHT JOIN ключевое слово возвращает все записи из правой таблицы (Сотрудники), даже если в левой таблице (Заказы) нет совпадений.

Оператор OUTER JOIN

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

Примеры на соединение таблиц в SQL

1.Напишите запрос, который бы использовал оператор INNER JOIN для получения всех Заказов для покупателя с фамилией Краснов.

SELECT onum, amt, odate, cname FROM orders INNERJOIN customers on orders.cnum = customers.cnum and cname = ‘Краснов’

2.Напишите запрос, который бы вывел фамилии и города продавцов, комиссия которых составляет более 20%.

SELECTDISTINCT(sname), city, comm FROM salespeople INNERJOIN orders on orders.snum = salespeople.snum and comm > 20

3.Напишите запрос, который бы вывел суммарную сумму заказов для городов в которых работают продавцы.

SELECTSUM(amt), salespeople.city FROM orders INNERJOIN salespeople on orders.snum = salespeople.snum GROUPBY salespeople.city

4.Повторите предыдущий запрос, но выведите все города, даже если в них не совершалась сделка.

SELECTSUM(amt), salespeople.city FROM orders RIGHTOUTERJOIN salespeople on orders.snum = salespeople.snum GROUPBY salespeople.city

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

SELECT customers.city, MAX(amt) FROM customers LEFTOUTERJOIN orders on orders.cnum = customers.cnum GROUPBY customers.city

Настройка базы данных

Прежде чем писать какие-либо запросы, нужно настроить базу данных.

Для примеров в этой статье будет использоваться PostgreSQL, но запросы и концепции, показанные здесь, легко применимы в любой другой современной СУБД (MySQL, SQL Server и т. д.).

Для работы с нашей базой данных PostgreSQL мы будем пользоваться интерактивной cli-программой psql. Если у вас установлен другой клиент, вы прекрасно можете работать с ним!

Для начала давайте создадим нашу базу данных. Поскольку у нас уже установлена PostgreSQL, для создания новой базы данных мы можем запустить в терминале psql-команду createdb <имя-базы-данных>. Свою базу я назвал fcc:
$ createdb fcc
Теперь давайте воспользуемся интерактивной консолью (запустив команду psql) и подключимся к только что созданной базе данных при помощи команды c <имя-базы-данных>:
$ psql psql (11.5) Type «help» for help. john=# c fcc You are now connected to database «fcc» as user «john». fcc=#

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

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

CROSS JOIN (перекрестное объединение)

Самое простое объединение, которое мы можем сделать, это CROSS JOIN (перекрестное объединение) или «декартово произведение».

При этом объединении мы берем каждую строку одной таблицы и соединяем ее с каждой строкой другой таблицы.

Если у нас есть два списка и в одном из них содержатся цифры 1, 2, 3, а в другом — буквы А, В, С, то декартово произведение этих списков будет выглядеть так:

1A, 1B, 1C 2A, 2B, 2C 3A, 3B, 3C

Каждое значение из первого списка соединено с каждым значением второго списка.

Давайте перепишем этот пример в виде SQL-запроса.

Для начала создадим две очень похожих таблицы и внесем в них данные:

CREATE TABLE letters( letter TEXT ); INSERT INTO letters(letter) VALUES (‘A’), (‘B’), (‘C’); CREATE TABLE numbers( number TEXT ); INSERT INTO numbers(number) VALUES (1), (2), (3);

Наши таблицы letters и numbers имеют по одному столбцу с простыми текстовыми полями.

Теперь давайте объединим эти таблицы, используя CROSS JOIN:

SELECT * FROM letters CROSS JOIN numbers; letter | number ———+——— A | 1 A | 2 A | 3 B | 1 B | 2 B | 3 C | 1 C | 2 C | 3 (9 rows)

Это наипростейший вид объединения, но даже на этом примере мы можем видеть, как работает JOIN. Две разных строки (одна из таблицы letters, другая — из numbers) объединяются друг с другом, образуя одну строку.

Хотя этот пример часто упоминается как чисто учебный, и для него есть практическое применение: покрытие диапазона дат.

CROSS JOIN с диапазонами дат

Хороший вариант использования CROSS JOIN — брать каждую строку таблицы и объединять ее с каждым днем из диапазона дат.

Скажем, вы создаете приложение, которое должно отслеживать ежедневную рутину (чистка зубов, завтрак, душ).

Если вы хотите генерировать запись для каждой задачи за каждый день прошлой недели, вы можете использовать CROSS JOIN с диапазоном дат.

Чтобы создать диапазон дат, мы можем воспользоваться функцией generate_series:

SELECT generate_series( (CURRENT_DATE — INTERVAL ‘5 day’), CURRENT_DATE, INTERVAL ‘1 day’ )::DATE AS day;

Функция generate_series принимает три параметра.

Первый параметр — стартовое значение. В этом примере мы использовали CURRENT_DATE — INTERVAL ‘5 day’, то есть текущая дата минус пять дней (или «пять последних дней»).

Второй параметр — текущая дата (CURRENT_DATE).

Третий параметр — шаг. То есть, на сколько мы хотим инкрементировать значение. Поскольку это ежедневные задачи, мы устанавливаем в качестве интервала один день (INTERVAL ‘1 day’).

Все вместе генерирует серию дат, начиная с даты пятидневной давности и заканчивая сегодняшним днем, по дню за раз.

Наконец, мы удаляем часть, касающуюся времени, преобразовывая вывод значений в дату при помощи ::DATE, и назначаем этому столбцу псевдоним (при помощи AS day), чтобы сделать вывод красивее.

Результат запроса за последние пять дней плюс сегодняшний:

day ———— 2020-08-19 2020-08-20 2020-08-21 2020-08-22 2020-08-23 2020-08-24 (6 rows)

Возвращаемся к нашему примеру с ежедневными задачами. Давайте создадим простую таблицу, в которой будут содержаться наши задачи (и добавим несколько):

CREATE TABLE tasks( name TEXT ); INSERT INTO tasks(name) VALUES (‘Brush teeth’), (‘Eat breakfast’), (‘Shower’), (‘Get dressed’);

В нашей таблице tasks есть только один столбец — name — в который мы добавили несколько задач.

Теперь давайте осуществим перекрестное объединение наших задач с запросом на генерацию дат:

SELECT tasks.name, dates.day FROM tasks CROSS JOIN ( SELECT generate_series( (CURRENT_DATE — INTERVAL ‘5 day’), CURRENT_DATE, INTERVAL ‘1 day’ )::DATE AS day ) AS dates

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

В результате мы получаем название задачи и день. Выглядит это следующим образом:

name | day —————+———— Brush teeth | 2020-08-19 Brush teeth | 2020-08-20 Brush teeth | 2020-08-21 Brush teeth | 2020-08-22 Brush teeth | 2020-08-23 Brush teeth | 2020-08-24 Eat breakfast | 2020-08-19 Eat breakfast | 2020-08-20 Eat breakfast | 2020-08-21 Eat breakfast | 2020-08-22 … (24 rows)

Как и ожидалось, мы получили по строке для каждой задачи на каждый день из нашего диапазона дат.

CROSS JOIN это простейшее объединение. Дальнейшие примеры потребуют более «жизненной» настройки таблиц.

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

Создание таблиц directors и movies

Чтобы проиллюстрировать следующие виды объединений, мы воспользуемся примером с фильмами (movies) и режиссерами (movie directors).

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

В нашей таблице directors будут храниться имена всех режиссеров, а в таблице movies — названия фильмов, а также отсылка к режиссеру (если он известен).

Давайте создадим эти таблицы и внесем в них необходимые данные:

CREATE TABLE directors( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); INSERT INTO directors(name) VALUES (‘John Smith’), (‘Jane Doe’), (‘Xavier Wills’) (‘Bev Scott’), (‘Bree Jensen’); CREATE TABLE movies( id SERIAL PRIMARY KEY, name TEXT NOT NULL, director_id INTEGER REFERENCES directors ); INSERT INTO movies(name, director_id) VALUES (‘Movie 1’, 1), (‘Movie 2’, 1), (‘Movie 3’, 2), (‘Movie 4’, NULL), (‘Movie 5’, NULL);

У нас есть пять режиссеров и пять фильмов, причем для трех фильмов указаны режиссеры. У режиссера с ID 1 есть два фильма, а у режиссера с ID 2 — один фильм.

Многотабличные запросы

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

Так, если таблицу просто объединить саму на себя, то возникнет конфликт имён и запрос не выполнится.

Код — Объединение таблицы саму на себя

mysql> SELECT * FROMnomenclatureJOINnomenclature; ERROR1066 (42000): Notuniquetable/alias: ‘nomenclature’

Обойти конфликт имён позволяет использование синонимов (alias) для имён таблиц и столбцов. В следующем примере внутреннее объединение будет работать успешнее:

Код — Объединение таблицы саму на себя

mysql> SELECT * FROMnomenclatureJOINnomenclatureASt2; +—-+————+—-+————+ | id | name | id | name | +—-+————+—-+————+ | 1 | Книга | 1 | Книга | | 2 | Табуретка | 1 | Книга | | 3 | Карандаш | 1 | Книга | | 1 | Книга | 2 | Табуретка | | 2 | Табуретка | 2 | Табуретка | | 3 | Карандаш | 2 | Табуретка | | 1 | Книга | 3 | Карандаш | | 2 | Табуретка | 3 | Карандаш | | 3 | Карандаш | 3 | Карандаш | +—-+————+—-+————+9rowsinset (0.00sec)

MySQL не накладывает ограничений на использование разных типов объединений в одном запросе, поэтому можно формировать довольно сложные конструкции:

Код — Пример сложного объединения таблиц

mysql> SELECT * FROMnomenclatureASt1JOINnomenclatureASt2LEFTJOINnomenclatureASt3ONt1.id = t3.idANDt2.id = t1.id; +—-+————+—-+————+——+————+ | id | name | id | name | id | name | +—-+————+—-+————+——+————+ | 1 | Книга | 1 | Книга | 1 | Книга | | 2 | Табуретка | 1 | Книга | NULL | NULL | | 3 | Карандаш | 1 | Книга | NULL | NULL | | 1 | Книга | 2 | Табуретка | NULL | NULL | | 2 | Табуретка | 2 | Табуретка | 2 | Табуретка | | 3 | Карандаш | 2 | Табуретка | NULL | NULL | | 1 | Книга | 3 | Карандаш | NULL | NULL | | 2 | Табуретка | 3 | Карандаш | NULL | NULL | | 3 | Карандаш | 3 | Карандаш | 3 | Карандаш | +—-+————+—-+————+——+————+9rowsinset (0.00sec)

Помимо выборок использовать объединения можно также и в запросах UPDATE и DELETE

Так, следующие три запроса проделывают одинаковую работу:

Код — Многотаблицные обновления

mysql> UPDATEnomenclatureASt1, nomenclatureASt2SETt1.id = t2.idWHEREt1.id = t2.id; QueryOK, 0rowsaffected (0.01sec) Rowsmatched: 3Changed: 0Warnings: 0mysql> UPDATEnomenclatureASt1JOINnomenclatureASt2SETt1.id = t2.idWHEREt1.id = t2.id; QueryOK, 0rowsaffected (0.00sec) Rowsmatched: 3Changed: 0Warnings: 0mysql> UPDATEnomenclatureASt1JOINnomenclatureASt2USING(id) SETt1.id = t2.id; QueryOK, 0rowsaffected (0.00sec) Rowsmatched: 3Changed: 0Warnings: 0

Таким же образом работают и многтабличные удаления

Код — Многотабличные удаления

mysql> DELETEt1FROMnomenclatureASt1JOINnomenclatureASt2USING(id) WHEREt2.id > 10; QueryOK, 0rowsaffected (0.02sec)

Следует помнить, что при использовании многотабличных запросов на удаление или обновление данных, нельзя включать в запрос конструкции ORDER BY и LIMIT. Впрочем, это ограничение очень эффективно обходится при помощи временных таблиц, просто, надо это учитывать при модификации однотабличных запросов.

Примеры использования многотабличных запросов

Приведу несколько примеров из своей практики, которые реально используются.

Код — Пример #1 многотабличного запроса
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28	SELECTSQL_CALC_FOUND_ROWSdgs.dogovor_id, dgs.dogovor_name, dgs.abonent_name, dgs.abonent_type, dgs.address_fiz, dgs.date_conclusion, dgs.date_annulment, dgs.threshold, ubc.usumFROMbilling_dogovorsdgsLEFTJOINbilling_users_balanceubcONubc.udate = CURDATE() ANDdgs.dogovor_id = ubc.dogovor_idWHEREdgs.dogovor_nameLIKE»%123%»ORDERBYdgs.dogovor_nameASCLIMIT0, 58

В данном случае идёт выборка первых 58 клиентов из таблицы договоров с привязкой баланса на текущий день, у которых в имени договора содержится «123» и сортировкой по имени (номеру) договора. Поскольку список договоров может не совпадать со списком балансов, то используется левостороннее объединение. Помимо этого используется SQL_CALC_FOUND_ROWS для подсчёта общего количества найденных строк, чтобы организовать страничную навигацию.

Код — Пример #2 реального многотабличного запроса
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26	SELECTSQL_CALC_FOUND_ROWSpft.udate, dgs.dogovor_name, pft.usum, ptt.type_nameFROMbilling_profitpftLEFTJOINbilling_dogovorsdgsUSING( dogovor_id ) LEFTJOINbilling_profit_typespttONpft.profit_type = ptt.type_idWHEREpft.udate > CURDATE() — INTERVAL7DAYORDERBYpft.udateDESC, dgs.dogovor_nameASCLIMIT0, 30;

Данный запрос выводит список платежей с указанием типа платежа и номера договора с сортировкой по дате и номеру договора. Также предусмотрен постраничный вывод списка.

Код — Пример #3 реального моноготабличного запроса

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

SELECTSQL_CALC_FOUND_ROWSips.ip, ips.segment_id, ips.gray_ip, ips.ip_mac, ips.ip_status, ips.ip_type, ips.blocked_reason, ips.blocked_time, ips.comment, rts.router_name, dgs.dogovor_name, ipt.type_name, ubc.usumFROMbilling_ipsipsLEFTJOINbilling_routersrtsONips.segment_id = rts.router_idLEFTJOINtONips.ip = t.ipLEFTJOINbilling_ip_typesiptONips.ip_type = ipt.type_id, billing_dogovorsdgsLEFTJOINbilling_users_balanceubcONubc.udate = CURDATE() ANDdgs.dogovor_id = ubc.dogovor_idWHEREINET_NTOA(ips.ip) LIKE»%123%»ANDdgs.dogovor_nameLIKE»%123%»ANDdgs.dogovor_id = t.dogovor_idORDERBYips.ipASCLIMIT0, 80

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

Использовать более экзотические конструкции на практике приходится нечасто, поскольку с ростом количества объединяемых таблиц резко падает производительность запросов. Сложные задачи, которые требуют серьёзных вычислений, такие, как, например, подсчёт балансов всех клиентов, решаются с помощью временных таблиц и довольно простых запросов.