Базовый курс SQL. JOIN-ы, или объединения таблиц

[к оглавлению]

Один из самых мощных инструментов извлечения данных в SQL - это объединение таблиц. SQL может "на лету" объединять таблицы, и это даёт широчайший спектр возможностей для работы с данными.

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

Реляционные таблицы

Разобраться с понятием реляционных таблиц нам поможет пример.

В нашей базе данных есть таблица Students, содержащая информацию о студентах: ФИО, возраст, эл.почта, а также сведения о группе, в которой учится студент. Но очевидно, что в одной группе учатся несколько студентов, и значит, данные о группе для них будут повторяться.

Крайне нежелательно допускать такое дублирование потому что:

• это избыточные данные, а значит, и место на диске, и затраченное время разработчика
• существенное повышение опасности ошибиться при вводе данных
• при изменении данных придётся править каждую строку, что тоже повышает трудозатраты и количество опечаток

Наилучшим решением будет создать ещё одну таблицу, Groups, в строках которой будут храниться данные о группе. И в таблицу Students добавить поле group_id - внешний ключ. Значение внешнего ключа Students будет соответствовать значению первичного ключа Groups. Так СУБД поймёт, какая именно строка таблицы Groups соответствует данному студенту.

Таблицы Students и Groups и являются реляционными, т.е. имеющими relation (с англ. - связь).

И, соответственно, преимущества реляционных таблиц:

• отсутствие избыточных данных
• лёгкость в случае необходимости внесения изменений - только в одном месте
• сокращение потенциальных опечаток и ошибок по невнимательности

Также хорошо спроектированная база данных легче поддаётся масштабированию, что немаловажно в работе над реальными проектами.

Для чего используются объединения

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

Для этого и служат объединения таблиц, или JOIN-ы.

С помощью определённого синтаксиса можно "на лету" соединить две или более таблиц базы в одну и так же, применяя условия, достать все необходимые данные. Нужно понимать, что объединение - это не реально существующая таблица, а сформированная в оперативной памяти на время запроса.

Ссылочная целостность

Важным моментом при создании связей является соблюдение ссылочной целостности между таблицами. Это означает, что крайне важно, чтобы внешний ключ был указан корректно. Иначе мы свяжем неверные строки и получим данные, не соответствующие действительности, или вовсе ошибку, если в другой таблице такого ключа не существует. Зачастую, контроль этого обеспечивается интерфейсами СУБД на этапе ввода данных, но логических ошибок это избежать не поможет, так что будьте внимательными.

Создание объединений

Для того, чтобы извлечь объединённые данные двух таблиц, нужно указать обе таблицы после оператора FROM, а также условие, по которому будут соотноситься строки, после оператора WHERE:

SELECT student_surname, group_name
FROM Students, Groups
WHERE Students.group_id = Groups.group_id;

Получим список студентов и названия групп, в которых они обучаются:

student_name  |  group_name
-------------------------------
Шульгина      |   304F      
Колобков      |   304F      
Распопов      |   304F      
Адамченко     |   304F      
Сейдинай      |   304F      
Петрашевский  |   304F      
Римский       |   580Z      
Грошев        |   117F      
Легран        |   117F      
Егорова       |   117F      

Теперь мы имеем возможность извлечь столбцы из двух таблиц одновременно: student_surname и group_name находятся в разных таблицах. Оператор FROM указывает БД на то, какие таблицы объединять: Students и Groups. WHERE подсказывает, какая строка из Groups соответствует данному студенту. Для каждой уникальной пары будет выведена строка в результирующей таблице.

Обратите внимание на то, что мы использовали полное название столбцов group_id для обоих таблиц: Students.group_id и Groups.group_id. Это необходимо, если имена столбцов совпадают. Всегда указывайте полные имена столбцов, если возможна неоднозначность, иначе СУБД выдаст ошибку.

Важность условия WHERE

При объединении таблиц отношения между ними формируются "на лету", и делается это следующим образом: к каждой строке первой таблицы добавляются все возможные варианты строк из второй таблицы, не зависимо ни от какой логики. Получается одна большая таблица, количество столбцов в которой равно сумме столбцов двух таблиц, а количество строк - произведению их строк. Такой результат называется декартово произведение, или перекрёстное объединение.

На нашем примере это бы выглядело так:

SELECT student_surname, group_name
FROM Students, Groups;

Как мы видим, данные некорректны, как будто каждый студент обучается сразу в 3х группах:

student_name  |  group_name
-------------------------------
Шульгина      |   304F      
Колобков      |   304F      
Распопов      |   304F      
Адамченко     |   304F      
Сейдинай      |   304F      
Петрашевский  |   304F      
Римский       |   304F      
Грошев        |   304F      
Легран        |   304F      
Егорова       |   304F      
Шульгина      |   580Z      
Колобков      |   580Z      
Распопов      |   580Z      
Адамченко     |   580Z      
Сейдинай      |   580Z      
Петрашевский  |   580Z      
Римский       |   580Z      
Грошев        |   580Z      
Легран        |   580Z      
Егорова       |   580Z      
Шульгина      |   117F      
Колобков      |   117F      
Распопов      |   117F      
Адамченко     |   117F      
Сейдинай      |   117F      
Петрашевский  |   117F      
Римский       |   117F      
Грошев        |   117F      
Легран        |   117F      
Егорова       |   117F      

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

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

Объединение, которое даёт нам корректный результат студентов и их групп, называется внутренним объединением. И его также можно реализовать с помощью альтернативного синтаксиса:

SELECT student_surname, group_name
FROM Students INNER JOIN Groups
  ON Students.group_id = Groups.group_id;

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

При этом синтаксисе для указания условия используется специальный оператор ON вместо WHERE. Фактически это тоже самое, но ON является обязательным продолжением JOIN, что не позволит допустить извлечения избыточных данных.

В большинстве СУБД поддерживается оба синтаксиса создания объединений, но JOIN-ы являются предпочтительными согласно спецификации ANSI SQL.

Объединения нескольких таблиц

Мы можем объединить и больше двух таблиц, главное не забывать указывать условия связей:

SELECT student_surname, group_name, teacher_surname
FROM Students, Groups, Teachers
WHERE Students.group_id = Groups.group_id
    AND curator_id = teacher_id;

student_surname  |  group_name  |  teacher_surname
-------------------------------------------------
Шульгина      |   304F     |  	Потапова      
Колобков      |   304F     |  	Потапова      
Распопов      |   304F     |  	Потапова      
Адамченко     |   304F     |  	Потапова      
Сейдинай      |   304F     |  	Потапова      
Петрашевский  |   304F     |  	Потапова      
Римский       |   580Z     |  	Игнатьева      
Грошев        |   117F     |  	Донских                                                 
Легран        |   117F     |  	      Донских                                                 
Егорова       |   117F     |  	Донских        
 

В этом примере мы присоединили ещё одну таблицу - Teachers, - и вывели фамилии кураторов групп. Внешним ключом, связывающим Groups с таблицей Teachers, является поле curator_id, которому соответствуют первичные ключи Groups.

Язык SQL не накладывает ограничений на количество таблиц, которые вы хотите объединить, тем не менее многие СУБД такие ограничения имеют. Что, впрочем, вполне оправдано: не следует использовать JOIN-ы без особой надобности - это может повлечь существенное снижение производительности.

И теперь самое время вернуться к примеру из предыдущего урока. Напомним, с помощью 2х подзапросов мы получали список студентов, обучающихся на факультете Компьютерных технологий:

SELECT student_surname
FROM Students
WHERE group_id IN (SELECT group_id
                 FROM Groups
                 WHERE faculty_id = (SELECT faculty_id
                                   FROM Faculties
                                   WHERE faculty_name = 'Компьютерных технологий'));

Давайте попробуем переписать этот запрос с помощью объединений:

SELECT student_surname
FROM Students, Groups, Faculties
WHERE Students.group_id =  Groups.group_id
       AND Groups.faculty_id = Faculties.faculty_id
       AND faculty_name = 'Компьютерных технологий';

Выглядит нагляднее, а результат, как вы понимаете, тот же:

student_surname
-----------------------
Петрашевский                                      
Сейдинай                                          
Егорова                                           
Адамченко                                         
Легран                                            
Распопов                                          
Колобков                                          
Шульгина                                          
Грошев 

В запросе участвуют три таблицы и три условия WHERE, два из которых служат для связи таблиц, и одно - для стандартной фильтрации данных.

Экспериментируйте

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

Следующая страница. Расширенные объединения.
Оглавление.