Базовый курс SQL. Расширенные объединения

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. Давайте ещё раз вспомним правила их создания:

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

Следующая страница. Комбинированные запросы.
Оглавление.