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

Часто нужно получить данные сразу из нескольких таблиц. В SQL, для этого применяются объединения таблиц.


[size=5pt]База данных для примеров.[/size]

Все примеры в статье рассчитаны на MySQL 5.1

Что бы наглядно описать принципы построения многотабличных запросов создадим простейшую базу данных. Для примера возьмем структуру форума:
- форум состоит из разделов;
- разделы включают в себя темы;
- темы представляют собой список сообщений.

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

Переняся всё выше сказанное на SQL получим:

–
– Структура таблицы `sections` (разделы)
–

CREATE TABLE IF NOT EXISTS `sections` (
  `section_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID раздела',
  `name` varchar(255) NOT NULL COMMENT 'Название раздела',
  PRIMARY KEY (`section_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Разделы';

– ——————————————————–

–
– Структура таблицы `topics` (темы)
–

CREATE TABLE IF NOT EXISTS `topics` (
  `topic_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID темы',
  `section_id` int(10) unsigned DEFAULT NULL COMMENT 'ID раздела',
  `title` varchar(255) NOT NULL COMMENT 'Заголовок темы',
  PRIMARY KEY (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Темы';

– ——————————————————–

–
– Структура таблицы `posts` (сообщения)
–

CREATE TABLE IF NOT EXISTS `posts` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID сообщения',
  `topic_id` int(10) unsigned DEFAULT NULL COMMENT 'ID темы',
  `content` text NOT NULL COMMENT 'Текст сообщения',
  PRIMARY KEY (`post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Сообщения';

Записи в эти таблицы можете внести сами или взять из приложения SQL-дамп и импортировать их оттуда.

Обратите внимание, что внешние ключи (section_id в таблице topics и topic_id в таблице posts) могут принимать значения NULL. Это сделано сознательно, что бы показать некоторые особенности объединений, о которых будет рассказано далее.

Ниже представлена ER-диаграмма этой БД.



Обозначения PK и FK обозначают первичный ключ (Primary Key) и внешний ключ (Foreign  Key) соответственно.



[size=5pt]Основы[/size]

Начнем с простого запроса:

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

Т.е. результат запроса должен представлять собой таблицу из двух столбцов — title из таблицы topics и content из таблицы posts.

Что будет, если просто выберем данные сразу из двух таблиц?

SELECT `topics`.`title`, `posts`.`content`
FROM `topics`, `posts`

Результат будет примерно следующим:

---

[table]
[tr][td]title (Заголовок темы)[/td][td]content (Текст сообщения)[/td][/tr]
[tr][td]…[/td][td]… [/td][/tr]
[tr][td]Использование удаленного php файла [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Добавление нескольких строк в БД [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]прoблeмa c тaймeрoм [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Методология программирования Канбан [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Использование spl для обхода содержимого всех файл… [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Размер сесии [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Название класса в переменной и статический метод… [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Замена switch-case классом [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]N кол-во наследников [/td][td]Мне нужно использовать php скрипт с другого сайта…. [/td][/tr]
[tr][td]Использование удаленного php файла [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Добавление нескольких строк в БД [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]прoблeмa c тaймeрoм [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Методология программирования Канбан [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Использование spl для обхода содержимого всех файл… [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Размер сесии [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Название класса в переменной и статический метод… [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Замена switch-case классом [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]N кол-во наследников [/td][td]Если нет ftp то никак [/td][/tr]
[tr][td]Использование удаленного php файла [/td][td]Теоритически это возможно (если ты конечно владеле… [/td][/tr]
[tr][td]Добавление нескольких строк в БД [/td][td]Теоритически это возможно (если ты конечно владеле…[/td][/tr]
[tr][td]…[/td][td]… [/td][/tr]
[/table]

---

и т. д., всевозможные пары строк из двух таблиц. Их количество = число строк в первой таблице * число строк во второй
(См. Декартово произведение).

Как видите, получили совсем не то, что нам нужно. Следовательно, выборку нужно чем-то ограничить, а именно добавить условие равенства внешнего ключа в таблице post первичному ключу таблицы topics:

posts.topic_id = topics.topic_id

Полностью запрос будет выглядеть так:

SELECT topics.title, posts.content
FROM topics, posts
WHERE posts.topic_id = topics.topic_id

Получим:

---

[table]
[tr][td]title[/td][td]content[/td][/tr]
[tr][td]…[/td][td]… [/td][/tr]
[tr][td]Использование удаленного php файла[/td][td]Теоритически это возможно (если ты.. [/td][/tr]
[tr][td]Добавление нескольких строк в БД[/td][td]Уважаемые товарищи программисты… [/td][/tr]
[tr][td]Добавление нескольких строк в БД[/td][td]возьми код в соответствующие теги, .. [/td][/tr]
[tr][td]Добавление нескольких строк в БД[/td][td]@lexaka, попробуй jquery [/td][/tr]
[tr][td]прoблeмa c тaймeрoм[/td][td]в oбщeм прoблeмa зakлючaeтcя вoт в чeм [/td][/tr]
[tr][td]прoблeмa c тaймeрoм[/td][td]на пхп? оО вы извращенец? оО [/td][/tr]
[tr][td]прoблeмa c тaймeрoм[/td][td]Dakilla, на пхп это сделать нельзя [/td][/tr]
[tr][td]Методология программирования Канбан[/td][td]Я сегодня обещал написать несколько [/td][/tr]
[tr][td]Методология программирования Канбан[/td][td]интересно. спасибо! [/td][/tr]
[tr][td]Методология программирования Канбан[/td][td]Пиши исчо! Буду ждать. [/td][/tr]
[tr][td]…[/td][td]…[/td][/tr]
[/table]

---

Сообщения больше не повторяются и для каждого указана именно та тема, в которой это сообщение находится. Мы как бы проделали тоже самое декартово произведение, что и предыдущем примере, но выкинули из него лишние строки, которые не соответствуют условию posts.topic_id = topics.topic_id.

Такой вид объединений называется объединением по равенству.

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

Аналогично, строятся запросы для объединения более двух таблиц:

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

SELECT sections.name, topics.title, posts.content
FROM sections, topics, posts
WHERE sections.section_id = topics.section_id
AND topics.topic_id = posts.topic_id

---

[table]
[tr][td]Name (название раздела)[/td][td]Title (заголовок темы)[/td][td]Content (текст сообщения)[/td][/tr]
[tr][td]…[/td][td]…[/td][td]… [/td][/tr]
[tr][td]PHP для начинающих[/td][td]Использование удаленного php файла[/td][td]Теоритически это возможно (если ты.. [/td][/tr]
[tr][td]PHP для начинающих[/td][td]Добавление нескольких строк в БД[/td][td]Уважаемые товарищи программисты… [/td][/tr]
[tr][td]PHP для начинающих[/td][td]Добавление нескольких строк в БД[/td][td]возьми код в соответствующие теги, .. [/td][/tr]
[tr][td]PHP для начинающих[/td][td]Добавление нескольких строк в БД[/td][td]@lexaka, попробуй jquery [/td][/tr]
[tr][td]PHP для начинающих[/td][td]прoблeмa c тaймeрoм[/td][td]в oбщeм прoблeмa зakлючaeтcя вoт в чeм [/td][/tr]
[tr][td]PHP для начинающих[/td][td]прoблeмa c тaймeрoм[/td][td]на пхп? оО вы извращенец? оО [/td][/tr]
[tr][td]PHP для начинающих[/td][td]прoблeмa c тaймeрoм[/td][td]Dakilla, на пхп это сделать нельзя [/td][/tr]
[tr][td]PHP для профи[/td][td]Методология программирования Канбан[/td][td]Я сегодня обещал написать несколько [/td][/tr]
[tr][td]PHP для профи[/td][td]Методология программирования Канбан[/td][td]интересно. спасибо! [/td][/tr]
[tr][td]PHP для профи[/td][td]Методология программирования Канбан[/td][td]Пиши исчо! Буду ждать. [/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[/table]

---

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

[size=5pt]Объединения в SQL2[/size]

В начале статьи мы оговорились, что внешние ключи таблиц topics и posts могут принимать значения NULL, которое как известно, не является значением как таковым, а является признаком отсутствия значения. Любая операция сравнения, в которой участвует NULL вернет NULL в качестве результата.

SELECT NULL=NULL

— вернет NULL, а не TRUE.

Допустим, что в таблице topics есть записи о темах, не относящихся ни к какому разделу, т.е. имеющие NULL в качестве значения поля section_id.

SELECT * FROM `sections`

---

[table]
[tr][td]topic_id (ID темы)[/td][td]section_id (ID раздела)[/td][td]Title (Заголовок темы) [/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[tr][td]1[/td][td]1[/td][td]Использование удаленного php файла [/td][/tr]
[tr][td]2[/td][td]1[/td][td]Добавление нескольких строк в БД [/td][/tr]
[tr][td]3[/td][td]1[/td][td]прoблeмa c тaймeрoм [/td][/tr]
[tr][td]4[/td][td]2[/td][td]Методология программирования Канбан [/td][/tr]
[tr][td]5[/td][td]2[/td][td]Использование spl для обхода содержимого всех файл… [/td][/tr]
[tr][td]6[/td][td]2[/td][td]Размер сесии [/td][/tr]
[tr][td]7[/td][td]3[/td][td] Название класса в переменной и статический метод… [/td][/tr]
[tr][td]8[/td][td]NULL[/td][td]Замена switch-case классом [/td][/tr]
[tr][td]9[/td][td]NULL[/td][td]N кол-во наследников[/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[/table]

---

Тогда при таком объединение таблиц topics и sections:

SELECT `sections`.`section_id`, `topics`.`topic_id`, `title`, `name` 
FROM `topics`, `sections` 
WHERE `topics`.`section_id` = `sections`.`section_id`

эти записи будут пропущены:

---

[table]
[tr][td]section_id (ID раздела) [/td][td]topic_id (ID темы) [/td][td]title (Заголовок темы)[/td][td]name (Название раздела)[/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[tr][td]1[/td][td]1[/td][td]Использование удаленного php файла[/td][td]PHP для начинающих [/td][/tr]
[tr][td]1[/td][td]2[/td][td]Добавление нескольких строк в БД[/td][td]PHP для начинающих [/td][/tr]
[tr][td]1[/td][td]3[/td][td]прoблeмa c тaймeрoм[/td][td]PHP для начинающих [/td][/tr]
[tr][td]2[/td][td]4[/td][td]Методология программирования Канбан[/td][td]PHP для профи [/td][/tr]
[tr][td]2[/td][td]5[/td][td]Использование spl для обхода содержимого всех файл…[/td][td]PHP для профи [/td][/tr]
[tr][td]2[/td][td]6[/td][td]Размер сесии[/td][td]PHP для профи [/td][/tr]
[tr][td]3[/td][td]7[/td][td]Название класса в переменной и статический метод…[/td][td]PHP и ООП[/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[/table]

---

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


Для решения этой задачи применяются внешние объединения. Ранние версии стандарта SQL не включали такого понятия, и в различных СУБД способы представления таких объедений существенно отличались.

В стандарте SQL2 (SQL-92) был введен новый способ объединения таблиц и были существенно расширены возможности этого процесса.

Внутреннее объединение — INNER JOIN

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics` 
  INNER JOIN `sections` 
    ON (`topics`.`section_id`=`sections`.`section_id`)

Такой запрос аналогичен запросу:

SELECT `topics`.`title`, `sections`.`name`
FROM `topics`, `posts`
WHERE `posts`.`topic_id`=`topics`.`topic_id`

Левое внешнее объединение — LEFT OUTER JOIN

Включим в результаты запроса темы, не относящиеся ни к какому разделу:

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics` 
  LEFT OUTER JOIN `sections` 
    ON (`topics`.`section_id`=`sections`.`section_id`)

Результат:

---

[table]
[tr][td] Title (Заголовок темы) [/td][td] name (Название раздела) [/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[tr][td]Использование удаленного php файла[/td][td]PHP для начинающих [/td][/tr]
[tr][td]Добавление нескольких строк в БД[/td][td]PHP для начинающих [/td][/tr]
[tr][td]прoблeмa c тaймeрoм[/td][td]PHP для начинающих [/td][/tr]
[tr][td]Методология программирования Канбан[/td][td]PHP для профи [/td][/tr]
[tr][td]Использование spl для обхода содержимого всех файл…[/td][td]PHP для профи [/td][/tr]
[tr][td]Размер сесии[/td][td]PHP для профи [/td][/tr]
[tr][td]Название класса в переменной и статический метод…[/td][td]PHP и ООП [/td][/tr]
[tr][td]Замена switch-case классом[/td][td]NULL [/td][/tr]
[tr][td]N кол-во наследников[/td][td]NULL[/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[/table]

---

Правое внешнее объединение — RIGHT OUTER JOIN

Включим в результаты запроса разделы, в которых нет ни одной темы:

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics` 
  RIGHT OUTER JOIN `sections` 
    ON (`topics`.`section_id`=`sections`.`section_id`)

Результат

---

[table]
[tr][td]title (Заголовок темы)[/td][td]name (Название раздела) [/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr] [tr][td]Использование удаленного php файла[/td][td]PHP для начинающих [/td][/tr]
[tr][td]Добавление нескольких строк в БД[/td][td]PHP для начинающих [/td][/tr]
[tr][td]прoблeмa c тaймeрoм[/td][td]PHP для начинающих [/td][/tr]
[tr][td]Методология программирования Канбан[/td][td]PHP для профи [/td][/tr]
[tr][td]Использование spl для обхода содержимого всех файл…[/td][td]PHP для профи [/td][/tr]
[tr][td]Размер сесии[/td][td]PHP для профи [/td][/tr]
[tr][td]Название класса в переменной и статический метод…[/td][td]PHP и ООП [/td][/tr]
[tr][td]NULL[/td][td]Расширения PHP[/td][/tr]
[tr][td]…[/td][td]…[/td][td]…[/td][/tr]
[/table]

---

Перекрестное объединение – CROSS JOIN

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics` 
  CROSS JOIN `sections`

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

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics`, `sections`

Кроме вышеперечисленных в стандарте описаны другие виды объединений (например, FULL OUTER JOIN и UNION JOIN), однако MySQL их не поддерживает, поэтому пропустим их описание.

Указание связанных полей.

Вместо предложения ON в запросе на объединение можно использовать предложение USING:

SELECT `topics`.`title`, `sections`.`name` 
FROM `topics` 
  JOIN `sections` USING (`section_id`)

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

Этот же запрос можно записать ещё проще:

SELECT `topics`.`title` , `sections`.`name` 
FROM `topics` 
  NATURAL JOIN `sections`

В этом случае связанными будут считаться все поля с одинаковыми именами в обеих таблицах (т.е. поле section_id). Такое объединение называется естественным.

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

SELECT `content` , `title` , `name` 
FROM `posts` 
  LEFT JOIN `topics` ON ( `posts`.`topic_id` = `topics`.`topic_id` ) 
  NATURAL JOIN `sections`

[size=5pt]Немного о производительности.[/size]

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

[size=5pt]Материалы по теме:

• Джеймс Р. Грофф, Пол Н. Вайнберг — «SQL: Полное руководство»


• Стандарт SQL-92


• MySQL Documentation

ok, попозже нарисую

хз, где-то его называют SQL-92, где-то просто SQL2, в основном, конечно, вообще не указывают версию стандарта, потому что он сейчас один во всех СУБД.
Хотел переименовать "Объединения с помощью JOIN", но оказалось что "превышен размер сообщения в 20 кб". Это из-за склейки. Хотел сделать в два сообщения сначала, но они объединились в одно, теперь хз как изменить. может напрямую, через phpmyadmin (или че там стоит на сервере)?