ФорумПрограммированиеПыхнуть хотите?F.A.Q. → SQL: Многотабличные запросы

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

  • Timur

    Сообщения: 1068 Репутация: N Группа: Джедаи

    Spritz 28 сентября 2009 г. 17:19, спустя 16 минут 32 секунды

    Часто нужно получить данные сразу из нескольких таблиц. В 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 не требует что бы связные столбцы состояли в отношении предок/потомок (первичный/внешний ключ), хотя такой вид связи встречается чаще всего. Связными могут быть любые столбцы имеющие сравнимые типы данных. Вообще говоря, даже говоря операция «=» является не единственной возможностью связать данные из нескольких таблиц.
    Спустя 22 сек.

    [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]Материалы по теме:


  • adw0rd

    Сообщения: 22959 Репутация: N Группа: в ухо

    Spritz 26 июля 2009 г. 17:57, спустя 37 минут 51 секунду

    Класс! Думаю еще стоит разбавить статью рисунками с http://www.codinghorror.com/blog/archives/000976.html
    https://smappi.org/ - платформа по созданию API на все случаи жизни
  • Timur

    Сообщения: 1068 Репутация: N Группа: Джедаи

    Spritz 26 июля 2009 г. 17:59, спустя 2 минуты 32 секунды

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

    Сообщения: 22959 Репутация: N Группа: в ухо

    Spritz 26 июля 2009 г. 18:02, спустя 2 минуты 15 секунд

    Объединения в SQL2
    Не понятно что за "SQL2"? Может стоит переименовать подзаголовок?
    https://smappi.org/ - платформа по созданию API на все случаи жизни
  • Timur

    Сообщения: 1068 Репутация: N Группа: Джедаи

    Spritz 26 июля 2009 г. 18:11, спустя 9 минут 31 секунду

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

    Сообщения: 22959 Репутация: N Группа: в ухо

    Spritz 26 июля 2009 г. 18:12, спустя 29 секунд

    Timur, да, лучше опустить, ибо вводит в заблуждение. Кажется что до того заголовка речь шла о другом SQL, не относящийся к SQL2
    https://smappi.org/ - платформа по созданию API на все случаи жизни
  • mario

    Сообщения: 6067 Репутация: N Группа: Джедаи

    Spritz 27 июля 2009 г. 13:35, спустя 19 часов 23 минуты 55 секунд

    круто! Где ты был раньше… :DDD
  • md5

    Сообщения: 11960 Репутация: N Группа: в ухо

    Spritz 28 сентября 2009 г. 17:02, спустя 63 дня 3 часа 26 минут

    вот это шрифт блять…. Оо
    все умрут, а я изумруд
  • Trej Gun

    Сообщения: 5305 Репутация: N Группа: в ухо

    Spritz 28 сентября 2009 г. 17:19, спустя 17 минут 4 секунды

    я поправил шрифт
  • phpdude

    Сообщения: 26646 Репутация: N Группа: в ухо

    Spritz 28 сентября 2009 г. 17:40, спустя 20 минут 49 секунд

    Текст подготовлен в ПЫХО-Редакторе
    Сапожник без сапог

Пожалуйста, авторизуйтесь, чтобы написать комментарий!