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

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

Для получения информации подобного рода в SQL необходимо выполнить операцию, называемую соединением. Это означает объединение двух и более таблиц в соответствии с отношениями между данными. Если необходимо посмотреть, какие заказы сделал Владимир Сидоров, вначале потребуется просмотреть таблицу customers и найти в ней идентификатор Сидорова Владимира (customerid), а затем — таблицу orders на предмет заказов, сделанных клиентом с данным идентификатором customerid.

Хотя на первый взгляд операция соединения достаточно проста, на самом деле это один из наиболее сложных и тонких аспектов SQL. В MySQL реализовано несколько разных типов соединения, каждый из которых предназначен для определенных целей.

Простое соединение двух таблиц

 Начнем с поиска Сидорова Владимира:

mysql> select orders.orderid, orders.amount, orders.date
-> from customers, orders
-> where customers.name='Сидоров Владимир'
-> and customers. customerid = orders.customerid;

 Результат будет таким:

 Здесь следовало бы отметить несколько моментов.

 Перечисляя две таблицы, вы также указываете тип соединения, возможно, даже не зная его. Запятая между названиями таблиц эквивалентна конструкциям INNER JOIN (внутреннее соединение) или CROSS JOIN (перекрестное соединение). Такой тип соединения еще называют полным соединением или декартовым произведением таблиц. Это означает следующее: "Взять указанные таблицы и сделать из них одну большую. Большая таблица должна содержать строку для любой возможной комбинации строк из каждой указанной в списке таблицы, независимо от того имеют они смысл или нет". Другими словами, мы получаем таблицу, в которой каждая строка таблицы customer сопостовляется каждой строке таблицы orders независимо от того, какие заказы были сделаны конкретными клиентами.

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

 Это достигается путем помещения в конструкцию WHERE условия соединения. Это особый тип условного оператора, который поясняет, какие атрибуты отражают отношения между двумя таблицами. В данном случае наше условие соединения было таким:

and customers. customerid = orders.customerid

что предписывает MySQL помещать в результирующую таблицу только те строки, для которых customerid из таблицы customer совпадает с customerid из таблицы orders.

Внеся это условие в запрос, мы, по сути, получили соединение другого типа —соединение по равенству.

Обратите внимание на точечную нотацию, которой мы воспользовались для уточнения конкретного столбца конкретной таблицы. Так, customers.customerid относится к столбцу customerid из таблицы Customers, a orders.customerid — к столбцу customerid из таблицы оrders.

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

Как расширение его можно использовать для различения имен столбцов из разных баз данных. В нашем примере обозначение выглядит как table.column (таблица.столбец). Можно указать и иначе — database.table.column (база_данных.таблица.столбец), например, для проверки условия наподобие:

books.orders.customerid = other_db.orders.customerid

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

customers. name = 'Сидоров Владимир'

Столбец name присутствует только в таблице customers, поэтому его необязатель но указывать, но так, в общем-то, понятнее.

Соединение трех и более таблиц

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

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

Необходимо будет найти клиентов, разместивших, по крайней мере, один заказ, который содержит order_items соответствующий книге по Разработке веб-сайта с помощью РНР и Mysql. Из таблицы сustomers перебираемся в таблицу оrders, используя customerid, как и в предыдущих случаях. Из таблицы оrders в таблицу оrder_items, используя orderid. Из order_items — в таблицу books за нужной книгой, руководствуясь номером ISBN. После того как все связи установлены, можем запросить книги со словом "Разработка" в названии и получить в результате имена клиентов, которые купили какую-либо из этих книг.

Давайте посмотрим на запрос, выполняющий описанные действия:

mysql> select customers.name
-> from customers, orders, order_items, books
-> where customers.customerid = orders.customerid
-> and orders.orderid = order_items.orderid
-> and order_items.isbn = books.isbn
-> and books.title like '%Разработка%';

Этот запрос возвратит следующий результат:

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

Поиск несовпадающих строк

Другой распространенный в Mysql тип соединения — левостороннее соединение.

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

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

Рассмотрим пример:

mysql> select customers. customerid, customers.name, orders. orderid
-> from customers left join orders
-> on customers.customerid = orders. customerid;

Данный запрос SQL использует левостороннее соединение для таблиц customers и orders. Его синтаксис в отношении условий соединения несколько иной. Условие соединения указывается в специальной конструкции ON.

Вот так выглядит результат запроса:

Из результата видно, что для Алексеева Ивана нет соответствующего идентификатора закза, поскольку его поле orderid имеет значение NULL.

Если необходимо найти только тех клиентов, которые ничего не заказывали, этого можно достичь, выполнив проверку на значение NULL в поле первичного ключа правой таблицы ( в данном случае orderid)? так как строки с реальными значениями не могут содержать значение NULL:

mysql> select customers. customerid, customers. name
-> from customers left join orders
-> using (customerid)
-> where orders. orderid is NULL;

Результат запроса выглядит следующим образом:

Вероятно, вы обратили внимание на то, что в этом примере мы использовали несколько иной синтаксис условия соединения. Левостороннее соединение поддерживает синтаксис ON, который был использован в первом примере, так и синтаксис USING, который применялся во втором. Синтаксис USING не предполагает указания таблицы, из которой поступает атрибут соединения, поэтому, чтобы его можно было использовать, столбцы в обеих таблицах должны называться одинаково.

Использование других имен таблиц: псевдонимы

Часто бывает очень удобно, а порой и необходимо обращаться к таблицам под другими именами. Такие имена называются псевдонимами (aliase). Их можно создать в самом начале запроса, а потом пользоваться по мере необходимости. Псевдонимы очень удобны, все равно, что ярлык на рабочем столе. Взгляните, как выглядит достаточно объемный запрос, рассмотренный нами ранее, переписанный с использованием псевдонимов:

mysql> select c.name
-> from customers as c, orders as o, order_items as oi, books as b
-> where c.customerid = o.customerid
-> and o.orderid = oi.orderid
-> and oi. isbn = b.isbn
-> and b.title like '%Разработка%';

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

Табличные псевдонимы необходимы в случае объединения таблицы с самой собой. Это звучит гораздо более угрожающе и мистически, чем есть на самом деле. А такой подход очень удобен для поиска строк в той же таблице, в которой есть и другие нужные нам значения. Если требуется найти клиентов, живущих в одном городе, скажем, с целью создания читательского клуба, можно присвоить одной и той же таблице(сustomers) два разных псевдонима:

mysql> select c1.name, c2. name, c1. city
->from customers as c1, customers as c2
->where c1. city = c2. city
->and c1.name != c2.name;

Мы делаем вид, что таблица customers —это две разные таблицы с1 и с2, и выполняем их соединение по столбцу city. Второе условие, c1.name != c2.name, необходимо для предотвращения сопоставления клиента самому себе.

Резюме по типам соединений

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