Как сделать многотабличный запрос sql
Обновлено: 02.07.2024
Важнейшей особенностью запросов SQL является их способность определять связи между несколькими таблицами и выводить информацию из них в терминах этих связей. Такие операции называются объединением, которое является одним из видов операций в реляционных БД - ведь это является основой реляционного подхода к хранению данных в таблицах. Используя объединения, происходит непосредственное связывание информации с любым номером таблицы, и, таким образом, создаются связи между сравнимыми частями данных.
При многотабличном запросе, таблицы, представленные в виде списка в предложении FROM, отделяются друг от друга запятыми. Предикат запроса может ссылаться к любому столбцу любой связанной таблицы и, следовательно, может использоваться для связи между ними. Обычно предикат сравнивает значения в столбцах различных таблиц, чтобы определить, удовлетворяет ли WHERE установленному условию. До этого имена таблиц в запросах опускались, потому запрашивалась только одна таблица одновременно Даже при запросе из нескольких таблиц допускается опускать их имена, если, конечно, они различны. Теперь же возникает необходимость использования имен столбцов и таблиц, поскольку в многотабличном запросе могут возникать неоднозначности.
Предположим необходимо поставить в соответствие преподавателю учебные предметы, которые он ведет. Фактически SQL придется выбирать из таблицы преподавателей соответствующий ему код и, просматривая таблицу предметов, осуществлять поиск соответствующего кода.
При выполнении многотабличного запроса, SQL исследует каждую комбинацию строк двух или более возможных таблиц и проверяет эти комбинации по их предикатам. Если комбинация производит такое значение, которое делает предикат верным, то значение будет выбрано для вывода.
В предыдущем примере была установлена связь между двумя таблицами в объединении, но, вообще говоря, эти таблицы, уже были соединены через поле TNUM. Эта связь называется состоянием справочной целостности, и, используя такое объединение в многотабличном запросе, можно извлекать данные в терминах этой связи. Значит в рассмотренном примере объединения столбцы используются для определения предиката запроса, при этом TNUM столбцы удалены из вывода для обеих таблиц, т.к. и без этого понятно, какие преподаватели ведут тот или иной учебный предмет.
В свою очередь, объединения в многотабличных запросах, которые используют предикаты, основанные на равенствах, называются объединениями по равенству. Объединения по равенству это наиболее общий вид объединения, однако, существуют и другие виды объединений - фактически можно использовать любой из реляционных операторов.
В данном случае запрос вряд ли будет иметь практическое значение - с его помощью выводится информация о преподавателях, фамилия которых по алфавиту находится раньше, чем наименование учебного предмета из таблицы PREDMET, при этом выбираются преподаватели с фамилией, которая заключена между буквами К и С.
Допускается также создавать запросы, объединяющие более двух таблиц. Например, необходимо вывести список оценок, выставленных тем или иным преподавателем. Тогда запрос будет объединять сразу три таблицы:
SELECT TEACHERS.TFAM, USP.OCENKA FROM TEACHERS, PREDMET, USP WHERE TEACHERS . TNUM = PREDMET. TNUM AND PREDMET. PNUM = USP.PNUM;
Таким образом, при объединении таблиц, очевидно, хорошо просматривается логика связи между данными и можно больше не ограничиваться просмотром одной таблицы в каждый момент времени. Кроме того, объединение позволяет делать сложные сравнения между любыми полями любого числа таблиц и использовать полученные результаты для того, чтобы решать - какую информацию хотелось бы видеть.
Объединение таблиц
В многотабличном запросе можно объединять данные таблиц, однако интересно и то, что та же самая методика может использоваться для объединения вместе двух копий одиночной таблицы. Для объединения таблицы с собой можно сделать каждую строку таблицы одновременно и комбинацией ее с собой и комбинацией с каждой другой строкой таблицы, а затем оценить каждую комбинацию в терминах предиката. Это позволяет легко создавать определенные виды связей между различными элементами внутри одиночной таблицы. Например, допускается изобразить объединение таблицы с собой, как объединение двух копий одной и той же таблицы, причем она на самом деле не копируется, но SQL выполняет команду так, как если бы это было сделано.
Использование команды для объединения таблицы с собой аналогично тому приему, который используется для объединения нескольких таблиц. Когда объединяется таблица с собой, все повторяемые имена столбца заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, необходимо иметь два различных имени для этой таблицы. Это можно сделать с помощью определения временных имен, называемых псевдонимами, которые определяются в предложении FROM запроса. Синтаксис в этом случае следующий: после имени таблицы оставляют пробел, а затем должен следовать псевдоним для нее.
Например, для поиска студентов, имеющих одинаковый размер стипендии, можно воспользоваться следующим запросом:
SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = SECOND.STIP;
В данном примере SQL ведет себя так, как если бы он соединял две различные таблицы, называемые FIRST и SECOND, т.е. псевдонимы разрешают одной и той же таблице быть обработанной независимо. Обратите внимание на то, что псевдонимы могут использоваться в предложении SELECT до их объявления в предложении FROM, однако SQL будет сначала допускать любые псевдонимы и может отклонить команду, если они не будут определены далее в запросе. Кроме того, необходимо помнить, что псевдоним существует только тогда, когда команда выполняется, а после завершения запроса псевдонимы, используемые в нем, больше не имеют никакого значения.
Вывод последнего примера имеет два значения для каждой комбинации фамилий, причем второй раз в обратном порядке. Это связано с тем, что каждое значение показано первый раз в каждом псевдониме и второй раз в предикате, т.е. текущее значение в первом псевдониме сначала выбирается в комбинации со значением во втором псевдониме, а затем наоборот. Например, в нашем случае Поляков выбрался вместе с Нагорным, а затем Нагорный выбрался вместе с Поляковым и т. д. Кроме того, каждая строка была сравнена сама с собой, например Поляков с Поляковым.
Лучший способ избежать этого состоит в наложении порядка на два значения так, чтобы один мог быть меньше, чем другой или предшествовал ему в алфавитном порядке. Это делает предикат асимметричным относительно связи, поэтому те же самые значения в обратном порядке не будут выбраны снова. Следовательно, пример можно модифицировать таким образом:
SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP /-FROM STUDENTS FIRST, STUDENTS SECOND WHERE FIRST.STIP = SECOND.STIP AND FIRST. SFAM USP.SNUM;
Данный запрос выводит информацию о студентах и о сданных учебных предметах, при этом данные о предметах выбираются для студентов, имеющих номер меньше, чем текущий. В случае, если таковых не обнаруживается, SQL формирует строку из первой таблицы, заполняя NULL значениями данные, которые должны были быть получены из второй.
Хотя система записи внешнего объединения достаточно удобна, она имеет свои недостатки. Дело в том, что проблемы с записью внешних объединений возникают при расширении объединения до трех или более таблиц. Например, при записи
TABLE1 *=* TABLE2 *=* TABLE3
фактически выполняется внешнее объединение
(TABLE1 *=* TABLE2) *=* TABLE3
что, вообще говоря, приведет к различным результатам. Для создателей стандарта SQL2 внешние объединения были серьезной проблемой, т.к. внешние объединения являются порой единственным способом предоставления результатов ряда крайне необходимых запросов. Поэтому в стандарте SQL2 был определен совершенно новый метод поддержки внешних объединений: в спецификации стандарта SQL2 поддержка внешних объединений осуществляется в предложении FROM со специальным синтаксисом, позволяющим пользователю точно определить, как исходные таблицы должны быть объединены в запросе.
Например, уже рассмотренный нами запрос внутреннего объединения в стандарте SQL1 выглядит так:
SELECT STUDENTS.SFAM, STUDENTS.SNUM,
USP.PNUM, USP.SNUM FROM STUDENTS, USP ∙WHERE STUDENTS.SNUM = USP.SNUM;
а в стандарте SQL2 - так (хотя использование первого варианта допускается):
SELECT STUDENTS.SFAM, STUDENTS.SNUM,
USP.PNUM, USP.SNUM FROM STUDENTS INNER JOIN USP ON STUDENTS.SNUM = USP.SNUM;
В последнем случае две объединяемые таблицы соединяются явно посредством операции JOIN. а условие поиска, описывающее объединение, находится теперь в предложении ON внутри предложения FROM. В условии поиска. следующем за ключевым словом ON, могут быть заданы любые критерии сравнения строк двух объединяемых таблиц, в том числе с использованием булевских операторов
Таким образом, в этих двухтабличных объединениях все содержимое предложения WHERE просто перешло в предложение ОN, следовательно, ничего принципиально нового ON не добавляет в язык SQL. Однако такая структура позволяет более точно определить условие объединения.
Системы управления БД поддерживают три варианта запросов к множеству таблиц с помощью одного SQL-запроса на выборку: соединения, подзапросы и объединения [13, 24]. Эти три метода поиска данных во множестве таблиц существенно отличаются друг от друга и, как правило, решают разные виды поисковых задач.
Соединение — одна из наиболее востребованных операций в запросе SELECT. Поскольку большинство БД являются нормализованными, часто возникает необходимость получения данных из нескольких таблиц. Соединения дают возможность формировать результирующий набор из столбцов одной или более таблиц, которые хранят связанные данные. Множества столбцов, выбранных из каждой таблицы, называются потоками. Процесс соединения объединяет выбранные столбцы в единый выходной набор данных. Стандарты SQL поддерживают два варианта синтаксиса соединения — неявное и явное.
Неявное соединение соответствует более старому SQL-стандарту. Таблицы, участвующие в соединении, задаются списком с разделяющими запятыми в секции FROM запроса SELECT. Условия для связи таблиц задаются среди условий поиска секции WHERE. Не существует специального синтаксиса для указания, какие условия используются для поиска, а какие — для соединения.
В стандарте SQL:92 введен более универсальный синтаксис явного соединения, которое осуществляется с помощью конструкции JOIN. ON. Структура конструкции JOIN. ON дает возможность отличать условие соединения от условий поиска. Следует отметить, что неявное соединение может быть всегда заменено эквивалентным явным, обратное же возможно не всегда.
Подзапросом является запрос, заключенный в круглые скобки и вложенный в секцию SELECT, FROM, WHERE или HAVING основного (внешнего) запроса SELECT или других запросов, использующих эти секции. Правила построения подзапросов изменяются в соответствии с целью запроса.
Оба метода — и соединения, и подзапросы — используют слияние потоков данных из строк различных таблиц. В связи с этим при некоторых условиях их роли могут совпадать. Тем не менее, рациональнее использовать разные методы для решения разных задач, а именно: для сравнения значений агрегатных функций с другими значениями — подзапросы, для выборки информации из нескольких таблиц — соединения [25]. В этом заключается основное различие рассматриваемых видов запросов ко множеству таблиц. Кроме того, соединения позволяют формировать набор данных из столбцов таблицы или таблиц, хранящих связанные данные, в то время, как в подзапросах результаты внешнего и внутреннего запросов могут быть и не связаны.
Запросы объединения, реализуемые с помощью оператора UNION, дают возможность выбрать строки из различных наборов данных в объединенный набор данных, причем подмножества не обязательно должны быть связаны друг с другом — просто они должны соответствовать друг другу структурно.
Соединения, подзапросы и объединения не являются взаимоисключающими. Соединения и объединения могут включать подзапросы, и некоторые подзапросы могут содержать соединения.
Мне необходимо создать многотабличный запрос, который определяет, какая книга была взята больше всего раз в каждой возрастной категории. Я написал запрос, который создает таблицу из необходимых нам полей и подсчитывает, сколько раз брали каждую книгу. Каким образом можно дополнить его так, чтобы он сначала делал выборку по возрастным категориям, а после -выбирал бы самую востребованную книгу? Стоит отметить, что моя проблема заключается в том, что я не могу понять, как работать с таблицей, собранной из нескольких разных, ведь у нее нет конкретного имени.
Вам нужно выбрать самую востребованную книгу в каждой категории? Чтобы работать с запросом, как с таблицей - Вы можете обернуть его еще в один select типа: select q.* from ( ваш запрос ) q документация, простой пример
2 ответа 2
моя проблема заключается в том, что я не могу понять, как работать с таблицей, собранной из нескольких разных, ведь у нее нет конкретного имени.
Вам нужно понять, что результат запроса, это НЕ таблица, а набор БД записей, выведенные в табличной форме. В табличной форме лишь для Вашего удобства, как пользователя. Эти записи где-то там на сервере временно хранятся, пока выполняется Ваш запрос. Как только выполнится и выдастся Вам на экран (или куда надо), сервер их сотрет из памяти.
Ну и в качестве ответа на Ваш вопрос: эта "таблица" (AKA набор записей) может быть использована также, как и реальные таблицы БД.
28 сентября 2009 г. 17:19, спустя 16 минут 32 секунды
Часто нужно получить данные сразу из нескольких таблиц. В SQL, для этого применяются объединения таблиц.
[size=5pt]База данных для примеров.[/size]
Все примеры в статье рассчитаны на MySQL 5.1
Переняся всё выше сказанное на SQL получим:
Записи в эти таблицы можете внести сами или взять из приложения SQL-дамп и импортировать их оттуда.
Обратите внимание, что внешние ключи (section_id в таблице topics и topic_id в таблице posts) могут принимать значения NULL. Это сделано сознательно, что бы показать некоторые особенности объединений, о которых будет рассказано далее.
Ниже представлена ER-диаграмма этой БД.
Обозначения PK и FK обозначают первичный ключ (Primary Key) и внешний ключ (Foreign Key) соответственно.
Т.е. результат запроса должен представлять собой таблицу из двух столбцов — title из таблицы topics и content из таблицы posts.
Что будет, если просто выберем данные сразу из двух таблиц?
и т. д., всевозможные пары строк из двух таблиц. Их количество = число строк в первой таблице * число строк во второй
(См. Декартово произведение).
Как видите, получили совсем не то, что нам нужно. Следовательно, выборку нужно чем-то ограничить, а именно добавить условие равенства внешнего ключа в таблице post первичному ключу таблицы topics:
Полностью запрос будет выглядеть так:
Такой вид объединений называется объединением по равенству.
[size=5pt]Объединения в SQL2[/size]
В начале статьи мы оговорились, что внешние ключи таблиц topics и posts могут принимать значения NULL, которое как известно, не является значением как таковым, а является признаком отсутствия значения. Любая операция сравнения, в которой участвует NULL вернет NULL в качестве результата.
Допустим, что в таблице topics есть записи о темах, не относящихся ни к какому разделу, т.е. имеющие NULL в качестве значения поля section_id.
[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:
эти записи будут пропущены:
[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
Такой запрос аналогичен запросу:
Левое внешнее объединение — LEFT OUTER JOIN
Включим в результаты запроса темы, не относящиеся ни к какому разделу:
Результат:
[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
Включим в результаты запроса разделы, в которых нет ни одной темы:
Результат
[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
Вернет в результате все возможные пары строк обеих таблиц, т. е. по сути аналогичен запросу:
Кроме вышеперечисленных в стандарте описаны другие виды объединений (например, FULL OUTER JOIN и UNION JOIN), однако MySQL их не поддерживает, поэтому пропустим их описание.
Указание связанных полей.
Вместо предложения ON в запросе на объединение можно использовать предложение USING:
В этом случае в скобках после USING перечисляются через запятую имена связанных столбцов, при этом их имена в обеих таблицах должны быть одинаковыми.
Этот же запрос можно записать ещё проще:
В этом случае связанными будут считаться все поля с одинаковыми именами в обеих таблицах (т.е. поле section_id). Такое объединение называется естественным.
Аналогично строятся объединения нескольких таблиц, при этом можно комбинировать различные виды объединений:
[size=5pt]Немного о производительности.[/size]
При увеличении количества связываемых таблиц резко возрастает объем работы, которую нужно проделать СУБД для обработки запроса и его выполнение может занять непозволительно много времени. Если связей между таблицами становится слишком много, то возможно стоит задуматься о денормализации структуры БД.
Читайте также: