Базовый курс SQL. Расширенные объединения
Primary tabs
Forums:
Псевдонимы таблиц
На предыдущем уроке мы научились использовать объединения (JOIN-ы). Теперь предлагаем сделать их синтаксис ещё менее громоздким. Для этого таблицам присвоим сокращённые псевдонимы, используя ключевое слово AS:
SELECT student_surname FROM Students AS S, Groups AS G, Faculties AS F WHERE S.group_id = G.group_id AND G.faculty_id = F.faculty_id AND faculty_name = 'Компьютерных технологий';
Это
- значительно сокращает размер запроса
- снижает вероятность опечаток
- облегчает повторное использование таблицы (не только в WHERE, но и в ORDER BY, и в любом другом месте запроса)
в Oracle псевдоним указывается БЕЗ ключевого слова
Обратите внимание, что Oracle не поддерживает ключевое слово AS. В этой СУБД псевдоним указывается сразу после имени таблицы: Students S вместо Students AS S.
В отличие от псевдонимов столбцов, псевдонимы таблиц существуют только во время запроса и никогда не передаются клиенту.
Другие типы объединений
Все объединения, которые мы рассматривали до сих пор, называются простыми, или внутренними. Но есть и другие типы: самообъединения, естественные и внешние объединения.
Самообъединения
Данный тип объединения используется, когда требуется сделать несколько запросов к одной и той же таблице, и, используя результаты, получить искомые данные.
Давайте рассмотрим на примере. Предположим, нам надо отправить письма всем студентам, учащимся в одной группе с Ольгой Шульгиной. Для этого нам потребуется сначала вычислить group_id группы, в которой обучается Ольга, а затем, зная group_id, найти student_email - контакты всех её участников.
SELECT group_id, student_surname, student_email FROM Students WHERE group_id = ( SELECT group_id FROM Students WHERE student_surname = 'Шульгина');
получим:
group_id | student_surname | student_email
---------------------------------------------------------------------
31548 | Петрашевский | a_pet@mail.ru
31548 | Сейдинай | seydinay345@mail.ru
31548 | Адамченко |
31548 | Распопов | evgeniy_ras@mail.ru
31548 | Колобков | kolodm@mail.ru
31548 | Шульгина | shulginaa@mail.ru
Здесь мы воспользовались подзапросом для получения промежуточных данных из той же таблицы. Теперь сделаем то же с помощью объединения:
SELECT s1.group_id, s1.student_surname, s1.student_email FROM Students AS s1, Students AS s2 WHERE s1.group_id = s2.group_id AND s2.student_surname = 'Шульгина';
Результат, как видим, тот же:
group_id | student_surname | student_email
---------------------------------------------------------------------
31548 | Петрашевский | a_pet@mail.ru
31548 | Сейдинай | seydinay345@mail.ru
31548 | Адамченко |
31548 | Распопов | evgeniy_ras@mail.ru
31548 | Колобков | kolodm@mail.ru
31548 | Шульгина | shulginaa@mail.ru
Напоминаем, что в Oracle нет ключевого слова AS. Вам необходимо просто указывать псевдоним сразу после имени таблицы.
Здесь объединяются две копии таблицы Students. Для того, чтобы СУБД понимала, к столбцу какой из них мы обращаемся, мы ввели псевдонимы: s1 и s2. В случае самообъединения это обязательно, а также следует использовать полные имена столбцов: s1.group_id, s2.student_surname. Иначе СУБД выдаст ошибку.
Первое условие WHERE указывает, по какому полю объединить таблицы, второе условие - какие именно строки выбрать.
Объединения или подзапросы?
Часто на практике самообъединения используется вместо подзапросов по причине того, что первые выполняются быстрее. В каждом конкретном случае предлагаем вам не бояться экспериментировать и выбирать более производительный вариант.
Естественные объединения
Ранее мы уже упоминали, что в момент создания объединения таблиц, СУБД сначала просто соединяет все столбцы одной и все столбцы другой таблицы. Понятно, что среди них будет как минимум один повторяющийся столбец (по которому объединяются данные). Но их может быть и больше. Получение избыточных данных считается плохой практикой, т.к. во-первых, затрачивает лишние ресурсы производительности, во-вторых, создаёт больше поводов для утечки информации.
Естественное - это объединение, в котором извлекаются только не повторяющиеся столбцы. Чтобы указать на это СУБД обычно используется метасивол *. Это значит, что мы хотим достать все столбцы данной таблицы, кроме тех, что дублируются в остальных таблицах объединения. Для других таблиц, требующиеся столбцы указываются явно:
SELECT S.*, G.group_name, F.faculty_name FROM Students AS S, Groups AS G, Faculties AS F WHERE S.group_id = G.group_id AND G.faculty_id = F.faculty_id AND S.student_id = 92571;
Как мы видим, здесь извлекаются все столбцы первой таблицы с помощью *, а также явно указываются столбцы из других таблиц, которые мы хотим добавить в выборку. Таким образом мы не получим никаких лишних данных:
student_id | student_name | student_surname | student_age | student_country | student_email | group_id | group_name | faculty_name --------------------------------------------------------------------------------------------------------------------- 92571 | Аркадий | Петрашевский | 22 | РФ | a_pet@mail.ru | 31548 | 304F | Компьютерных технологий
На практике, запросы часто более конкретизированные, т.е. требуется получить не просто не дублирующиеся данные, а строго конкретные столбцы, или столбец. Естественно, в таком случае повторений также не будет, и все внутренние объединения такого типа будут являться одновременно и естественными.
Внешние объединения. OUTER JOIN.
До этого мы рассматривали объединения таблиц для того, чтобы получить некоторые данные одной таблицы, связанные с данными другой таблицы. Но иногда требуется получить также и строки, не имеющие соответствий во второй таблице, например:
- список предстоящих мероприятий и количества их участников, включая мероприятия, на которые ещё не записан ни один участник
- список преподавателей и групп, которые они курируют, включая преподавателей, не имеющих групп
В результате мы ожидаем получить в том числе строки, не имеющие ассоциаций в связанной таблице. В таких случаях объединения называются внешними.
Синтаксис в разных реализациях SQL
Синтаксис внешних объединений может отличаться от приведённого ниже. Обратитесь к документации вашей СУБД, чтобы убедиться, что вы применяете верные ключевые слова
Рассмотрим простое внутреннее объединение, получающее список всех кураторов групп:
SELECT Teachers.teacher_surname, Groups.group_name FROM Teachers INNER JOIN Groups ON Groups.curator_id = Teachers.teacher_id;
Но предположим, что наша задача перераспределить кураторскую нагрузку, и для этого мы хотим получить список всех преподавателей, в т.ч. и не занятых на данный момент. Нам поможет внешнее объединение:
SELECT Teachers.teacher_surname, Groups.group_name FROM Teachers LEFT OUTER JOIN Groups ON Groups.curator_id = Teachers.teacher_id;
Получим:
teacher_surname | group_name _____________________________ Потапова | 304F Игнатьева | 580Z Донских | 117F Ростовская | <null> Ростовский | <null> Колесникова | <null> Спрут | <null> Смирнов | <null>
Для создания внешнего объединения мы использовали ключевые слова LEFT OUTER JOIN. Они говорят СУБД, что необходимо включить в выборку все строки таблицы, которая указана слева от оператора, а поля из таблицы справа, не имеющие соответствующих значений, заполнить NULL. Аналогично существует и RIGHT OUTER JOIN: такое внешнее объединение выберет все поля из правой таблицы и соответствующие им значения из левой таблицы, при этом столбец из левой таблицы может содержать NULL.
Внешнее объединение в SQLite
Обратите внимание, что SQLite не поддерживает правое внешнее объединение. Поэтому используйте совет, приведённый ниже.
Типы внешних объединений
Как вы поняли, существует два типа внешних объединений: левое и правое. Но по сути отличаются они лишь порядком указания таблиц в инструкции FROM: в случае левого внешнего объединения - полный список строк будет из левой таблицы, в случае правого - из правой. Это значит, что типы легко взаимозаменяемы, достаточно только поменять местами названия таблиц. Какой из типов использовать - решать вам.
Есть и третий тип внешних объединений - полное внешнее объединение. Оно извлекает полный набор строк из обеих таблиц и связывает только те, которые возможно. Для обозначения используется ключевые слова FULL OUTER JOIN:
SELECT Teachers.teacher_surname, Groups.group_name FROM Teachers FULL OUTER JOIN Groups ON Groups.curator_id = Teachers.teacher_id;
Поддержка полного внешнего объединения
Внимание! Синтаксис полных внешних объединений не поддерживается в MySQL, MariaDB, SQLite, Access, OpenOffice Base.
Объединения и итоговые функции
В уроке 10 мы рассмотрели итоговые функции и узнали, что они служат для того, чтобы получить базовые статистические показатели по выборке из определённой таблицы. Так же мы можем применить эти функции к результатам объединения:
SELECT Students.student_surname, AVG(Grades.grade_value) AS avg_grade FROM Students INNER JOIN Grades ON Students.student_id = Grades.student_id GROUP BY Students.student_surname;
Получим:
student_surname | avg_grade ----------------------------------------------- Сейдинай | 4 Егорова | 3 Петрашевский | 4
Здесь создаётся внутреннее объединение - INNER JOIN - для таблиц Students и Grades. Ключевое слово GROUP BY позволяет СУБД понять, для каких строк из таблицы Students высчитывать среднее значение grade_value. Результат выводится в поле avg_grade.
Рассмотрим пример использования итоговой функции и с внешним объединением:
SELECT Students.student_surname, COUNT(Participants.contest_id) AS contests_count FROM Students LEFT OUTER JOIN Participants ON Students.student_id = Participants.student_id GROUP BY Students.student_id;
Данный код позволит нам получить количество конкурсов, в которых участвовал каждый студент. Т.к. мы использовали OUTER JOIN, cписок студентов будет полным, в него войдут даже те, кто ещё не принимал участия в соревнованиях:
student_surname | contests_count ______________________________ Шульгина | 1 Распопов | 1 Римский | 0 Грошев | 0 Адамченко | 1 Колобков | 0 Егорова | 1 Легран | 0 Сейдинай | 1 Петрашевский | 0
Итоги. Правила создания объединений.
Итак, объединения, или JOIN-ы - важный, часто используемый раздел SQL. Давайте ещё раз вспомним правила их создания:
- Выбирайте тип объединения, подходящий для вашего запроса: используйте внешнее объединение только если вам действительно нужен полный список строк, в остальных случаях - внутреннее
- Уточните в документации вашей СУБД синтаксис запросов, хотя большинство СУБД соответствует примерам, приведённым выше
- Внимательно проверяйте условие объединения: ошибка в условии приведёт к получению некорректных данных
- Можно создать объединение сразу трёх и более таблиц, более того, можно комбинировать объединения разного типа. Но чем сложнее конструкция запроса, тем больше вероятность допустить ошибку, и тем сложнее её обнаружить. Перед созданием сложных объединений, запустите каждое из них по отдельности, чтобы убедиться, что код написан верно.
- Log in to post comments
- 1313 reads