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

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

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

Например, если мы хотим получить информацию о тратах на покупки, мы можем её получить следующим образом:

MySQL
SELECT family_member, amount * unit_price AS price FROM Payments

В поле family_member полученной выборки отображаются идентификаторы записей из таблицы Payments, но для нас они мало что значат.

Вместо этих идентификаторов было бы гораздо нагляднее выводить имена тех, кто покупал (поле member_name из таблицы FamilyMember). Ровно для этого и существует объединение таблиц и оператор JOIN.

Общая структура многотабличного запроса

MySQL
SELECT поля_таблиц
FROM таблица_1
[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2
    ON условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n
    ON условие_соединения]

Как можно увидеть по структуре, соединение бывает:

• внутренним INNER (по умолчанию)
• внешним OUTER, при этом внешнее соединение делится на левое LEFT, правое RIGHT и полное FULL

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

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

MySQL
SELECT family_member, member_name, amount * unit_price AS price FROM Payments
INNER JOIN FamilyMembers
    ON Payments.family_member = FamilyMembers.member_id

В данном запросе мы сопоставляем записи из таблицы Payments и записи из таблицы FamilyMembers.

Чтобы сопоставление работало, мы указываем как именно записи из двух разных таблиц должны находить друг друга. Это условие указывается после ON:

MySQL
ON Payments.family_member = FamilyMembers.member_id

В нашем случае поле family_member указывает на идентификатор в таблице FamilyMembers и таким образом помогает однозначному сопоставлению.

В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие операторы сравнения.

Вывод всех столбцов из таблицы в многотабличном запросе

Ранее, когда все запросы выполнялись на одной таблице, было достаточно указать символ *, чтобы вывести все поля из этой таблицы. Теперь же, когда таблиц может быть несколько, * будет означать "вывести все столбцы из таблиц, перечисленных в выражении FROM".

В некоторых случаях нам может понадобиться вывести столбцы, принадлежащие только какой-то конкретной таблице. Например, дано соединение таблиц Payments и FamilyMembers, и в итоговую выборку необходимо вывести только поля из таблицы FamilyMembers. Как это сделать? Всё очень просто! Необходимо перед символом * добавить название таблицы:

MySQL
SELECT FamilyMembers.* FROM Payments
INNER JOIN FamilyMembers
    ON Payments.family_member = FamilyMembers.member_id

Запомните эту возможность, она вам понадобится в последующих заданиях 😉

Таким же образом можно вывести все столбцы из нескольких таблиц:

MySQL
SELECT Payments.*, FamilyMembers.* FROM Payments
INNER JOIN FamilyMembers
    ON Payments.family_member = FamilyMembers.member_id

В данном случае вместо Payments.*, FamilyMembers.* можно использовать *, т.к. в FROM перечислены только эти две таблицы. Вывод будет одинаков в обоих случаях.

А что, если нужно вывести несколько столбцов из одной таблицы и все из другой? Это тоже возможно! Выведем поля payment_id и family_member из Payments, а также все поля из FamilyMembers:

MySQL
SELECT payment_id, family_member, FamilyMembers.* FROM Payments
INNER JOIN FamilyMembers
    ON Payments.family_member = FamilyMembers.member_id

Псевдонимы для таблиц

При работе с большими многотабличными запросами рекомендуется использовать псевдонимы (алиасы) для таблиц. Это не только улучшает читаемость кода, но и помогает избежать ошибок в сложных запросах.

Псевдонимы задаются после имени таблицы с помощью ключевого слова AS:

MySQL
SELECT id, name
FROM Passenger AS pass

Теперь обращаться к столбцам таблицы можно через псевдоним:

MySQL
SELECT pass.id, pass.name
FROM Passenger AS pass

Ключевое слово AS является опциональным, как и у псевдонимов столбцов.

После того, как вы назначили таблице псевдоним, столбцы этой таблицы больше не будут доступны по её оригинальному названию. Вместо конструкции <таблица>.<поле> необходимо использовать <псевдоним_таблицы>.<поле>.

Рассмотрим пример запроса с несколькими таблицами. Допустим, нам нужно вывести идентификаторы и имена пассажиров, которые хотя бы раз совершали перелёт:

MySQL
SELECT
    pass.id,
    pass.name
FROM Passenger AS pass
INNER JOIN Pass_in_trip AS pit
    ON pit.passenger = pass.id

В этом примере таблицам Passenger и Pass_in_trip присваиваются псевдонимы pass и pit, соответственно, а дальше используются для вывода столбцов из этих таблиц. Обратите внимание, что в обеих таблицах есть поле с одинаковым названием – id. Если вы не укажете из какой таблицы его нужно брать, то СУБД сделает это за вас и отдаст значение из последней таблицы в цепочке JOIN. В данном случае это Pass_in_trip.

Сравните с вариантом без псевдонимов. Разница очевидна, не правда ли? 🔥

MySQL
SELECT
    Passenger.id,
    Passenger.name
FROM Passenger
INNER JOIN Pass_in_trip
    ON Pass_in_trip.passenger = Passenger.id

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

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