Глава 5. Запросы к нескольким таблицам

from sqlalchemy import create_engine
from sqlalchemy.engine import URL


connection_url = URL.create(
    drivername="mysql+pymysql",
    host="localhost",
    port=3306,
    database="sakila",
    username="root",
    password="********",
)

engine = create_engine(connection_url)

%load_ext sql

%config SqlMagic.displaylimit = 20
%config SqlMagic.displaycon = False
# %config SqlMagic.feedback = False

%sql engine

print("SQLAlchemy - подключение создано")
print("JupySQL - успешно подключен через SQLAlchemy Engine!")

SQLAlchemy - подключение создано
JupySQL - успешно подключен через SQLAlchemy Engine!

Еще в главе 2 было продемонстрировано, как связанные концепции разбиваются на отдельные части с помощью процесса, известного как нормализация. Конечным результатом этого упражнения были две таблицы: person и favorite_food.

Если вы хотите создать единый отчет с указанием имени, адреса и любимой еды человека, вам понадобится механизм, который снова соберет данные из этих двух таблиц. Этот механизм известен как соединение (join), и в этой главе основное внимание уделяется простейшему и наиболее распространенному соединению – внутреннему (inner join).

В главе 10 демонстрируются все типы соединений.

Что такое соединение¶

Запросы к одной таблице – это, конечно, не редкость. Но вы обраружите, что для большинства ваших запросов потребуется две, три или даже больше таблиц.

Для иллюстрации рассмотрим определения таблиц customer и address, а затем определим запрос, который извлекает данные из обеих таблиц:

%%sql
DESC customer;

%%sql
DESC address;

Допустим, вы хотите получить имя и фамилию каждого клиента, а также его почтовый адрес. Таким образом, ваш запрос должен будет получить столбцы customer.first_name, customer.last_name и address.address.

Но как получить данные из обеих таблиц в одном запросе? Ответ кроется в столбце customer.address_id, содержащем идентификатор записи клиента в таблице address (более формально – столбец customer.adderss_id является внешним ключом к таблице address). Запрос, который вы вскоре увидите, предписывает серверу использовать столбец customer.adderss_id в качестве транспорта между таблицами customer и address, что позволяет включать в результирующий набор запроса столбцы из обеих таблиц. Этот тип операции известе как соединение (join).

import pandas as pd
print(pd.__version__)

3.0.5

%config SqlMagic.autopandas = True

Декартово произведение¶

Самый простой способ начать решать поставленную задачу – поместить таблицы customer и address в предложение from запроса и посмотреть что получится.

Вот запрос, который извлекает имена и фамилии клиентов вместе с почтовым адресом; с предложением from, указывающим обе таблицы разделенные ключевым словом JOIN:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c
    JOIN address a;

Гм... Всего имеется 599 клиентов, а в таблице address 603 строки. Так откуда же в результирующем набора 361 197 строк? Присмотревшись, можно увидеть, что многие клиенты имеют один и тот же адрес. Поскольку в запросе не указано как именно должны быть соединены две таблицы, сервер базы данных сгенерировал декартово произведение, которое представляет собой все возможные сочетания записей из двух таблиц (599 клиентов * 603 адреса = 361 197 сочетаний).

Этот тип соединения известен как перекрестное соединение (cross join) и используется крайне редко (по крайней мере, преднамеренно). Перекрестные соединения – один из типов соединений, которые рассмотрим в главе 10.

Внутренние соединения¶

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

Ранее я сообщил, что столбец customer.address_id служит связующим звеном между двумя таблицами. Поэтому необходимо добавить эту информацию в подпредложение on предложения from:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c
    JOIN address a
        ON c.address_id = a.address_id;

Теперь вместо 361 197 строк у нас имеются ожидаемые 599 строк – благодаря добавлению подпредложения on, которое указывает серверу необходимость соединения таблиц customer и address, используя столбец address_id для перехода от одной таблицы к другой.

Например, строка ‘MARY SMITH’ в таблице customer содержит значение 5 в столбце address_id (в примере не показано). Сервер использует это значение для поиска в таблице address строки, имеющей значение 5 в столбце address_id, а затем извлекает значение ‘1913 Hanoi Way’ из столбца address в этой строке.

Important

Если значение имеется в столбце address_id одной таблицы, но отсутствует в другой, то соединение для строк, содержащих это значение, не выполняется и эти строки исключаются из результирующего набора. Этот тип соединения известен как внутреннее соединение и является наиболее часто используемым типом соединения.

В качестве примера: если строка в таблице customer имеет значение 999 в столбце address_id, а в таблице address нет строки со значением 999 в столбце address_id, то эта строка клиента не будет включена в результирующий набор.

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

В предыдущем примере я не указывал в предложении from какой тип соединения использовать. Однако, чтобы соединить две таблицы с использованием внутреннего соединения, необходимо явно указать это в своем предложении from. Вот тот же пример с добавлением типа соединения – обратите внимание на ключевое слово inner:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c
    INNER JOIN address a
        ON c.address_id = a.address_id;

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

Если имена столбцов, используемых для соединения двух таблиц, идентичны (как это было в предыдущем запросе), вместо подпредложения on можно использовать подпредложение using:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c 
    INNER JOIN address a
        USING (address_id);

Синтаксис соединения ANSI¶

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c, address a
WHERE c.address_id = a.address_id;

Преимущество синтаксиса соединения SQL92 проще увидеть для сложных запросов, которые включают как условия соединения, так и условия фильтрации.

Старый синтаксис соединения ANSI:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c, address a
WHERE c.address_id = a.address_id
  AND a.postal_code = '52137';

Запрос с использованием синтаксиса соединения SQL92:

%%sql
SELECT c.first_name, c.last_name, a.address
FROM customer c
    INNER JOIN address a ON c.address_id = a.address_id
WHERE a.postal_code = '52137';

Соединение трех и более таблиц¶

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

При соединении трех таблиц, в предложении from есть три таблицы, два типа соединения и два подпредложения on.

Чтобы проиллюстрировать это, давайте изменим предыдущий запрос, чтобы возвращать город клиента, а не его почтовый адрес. Однако название города в таблице address не хранится, а доступно через внешний ключ к таблице city. Вот так выглядят определения таблиц:

%%sql
DESC address;

%%sql
DESC city;

Чтобы показать город каждого клиента, необходимо перейти от таблицы customer к таблице address (используя столбец address_id), а затем – от таблицы address к таблице city с использованием столбца city_id.

Запрос будет выглядеть следующим образом:

%%sql
SELECT c.first_name, c.last_name, ct.city
FROM customer c
    INNER JOIN address a
        ON c.address_id = a.address_id
    INNER JOIN city ct
        ON a.city_id = ct.city_id;

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

Все три приведенных ниже варианта запроса возвращают одни и те же результаты:

%%sql
SELECT c.first_name, c.last_name, ct.city
FROM customer c
    INNER JOIN address a
        ON c.address_id = a.address_id
    INNER JOIN city ct
        ON a.city_id = ct.city_id;

%%sql
SELECT c.first_name, c.last_name, ct.city
FROM city ct
    INNER JOIN address a
        ON a.city_id = ct.city_id
    INNER JOIN customer c
        ON c.address_id = a.address_id;

%%sql
SELECT c.first_name, c.last_name, ct.city
FROM address a
    INNER JOIN city ct
        ON a.city_id = ct.city_id
    INNER JOIN customer c
        ON c.address_id = a.address_id;

Единственное различие, которое можно увидеть – это порядок, в котором возвращаются строки, поскольку в запросах нет предложения order by, указывающего как должны быть упорядочены результаты.

Имеет ли значение порядок соединения

Если вы не понимаете, почему все три версии запроса customer/address/city дают одни и те же результаты, вспомните, что SQL не является процедурным языком.

Это означает, что вы описываете что хотите получить и какие объекты базы данных должны быть вовлечены в запрос. Но как лучше всего выполнить ваш запрос – определяет сервер базы данных.

Используя статистику, собранную из объектов вашей базы данных, сервер должен выбрать одну из трех таблиц в качестве отправной точки (выбранная таблица в дальнейшем называется ведущей таблицей (driving table)), а затем решить в каком порядке соединять с ней оставшиеся таблицы. Следовательно порядок, в котором таблицы появляются в вашем предложении from значения не имеет.

Однако, если вы считаете, что таблицы в вашем запросе всегда следует соединять в опредененном порядке, можете разместить таблицы в желаемом порядке, а затем указать ключевое слово stright_join.

Например, чтобы указать серверу MySQL использовать в качестве ведущей таблицы city, а затем присоединить таблицы address и customer, можно сделать следующее:

%%sql
SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city
FROM city ct
    INNER JOIN address a
        ON a.city_id = ct.city_id
    INNER JOIN customer c
        ON c.address_id = a.address_id;

Использование подзапросов в качестве таблиц¶

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

Следующий запрос соединяет таблицу customer с подзапросом к таблицам address и city:

%%sql
SELECT c.first_name, c.last_name, addr.address, addr.city
FROM customer c
    INNER JOIN (
        SELECT a.address_id, a.address, ct.city
        FROM address a
            INNER JOIN city ct
                ON a.city_id = ct.city_id
        WHERE a.district = 'California'
    ) AS addr
        ON c.address_id = addr.address_id;

Подзапрос, который начинается в строке 4 и имеет псевдоним addr, находит все адреса в Калифорнии. Внешний запрос соединяет результаты подзапроса с таблицей customer, чтобы вернуть имя, фамилию, почтовый адрес и город для всех клиентов живущих в Калифорнии.

Примечание

Хотя этот запрос можно было бы написать без использования подзапроса, просто соединив три таблицы, иногда применение подзапроса может быть выгодным с точки зрения производительности и/или удобочитаемости.

SELECT c.first_name, c.last_name, a.address, ct.city
FROM customer c
    INNER JOIN address a
        ON c.address_id = a.address_id
    INNER JOIN city ct
        ON a.city_id = ct.city_id
WHERE a.district = 'California';

Один из способов визуализировать происходящее – выполнить подзапрос сам по себе и посмотреть на полученные результаты. Вот результаты подзапроса из предыдущего примера:

%%sql
SELECT a.address_id, a.address, ct.city
FROM address a
    INNER JOIN city ct
        ON a.city_id = ct.city_id
WHERE a.district = 'California';

Этот результирующий набор состоит из всех девяти адресов в Калифорнии. При соединении с таблицей customer через столбец address_id результирующий набор будет содержать информацию о клиентах, которым принадлежат эти адреса.

pd.set_option('display.max_rows', 20)

Использование одной таблицы дважды¶

Соединяя несколько таблиц, можем обнаружить, что нужно соединиться с одной и той же таблицей более одного раза. В рассматриваемом нами примере базы данных, например, актеры связаны с фильмами в которых они появлялись через таблицу film_actor. Если хотим найти все фильмы, в которых фигурирует два конкретных актера, можем написать запрос, который соединяет таблицу film с таблицей film_actor и с таблицей actor:

Таблицы film, actor и film_actor

SELECT film_id, title, release_year
FROM film;

| film_id | title            | release_year |
| ------- | ---------------- | ------------ |
| 1       | ACADEMY DINOSAUR | 2006         |
| 2       | ACE GOLDFINGER   | 2006         |
| 3       | ADAPTATION HOLES | 2006         |
| 4       | AFFAIR PREJUDICE | 2006         |
| 5       | AFRICAN EGG      | 2006         |
| ...     | ...              | ...          |
1000 rows × 3 columns

SELECT * FROM actor;

| actor_id | first_name | last_name    | last_update         |
| -------- | ---------- | ------------ | ------------------- |
| 1        | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
| 2        | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
| 3        | ED         | CHASE        | 2006-02-15 04:34:33 |
| 4        | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
| 5        | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
| ...      | ...        | ...          | ...                 |
200 rows × 4 columns

SELECT * FROM film_actor;

| actor_id | film_id | last_update         |
| -------- | ------- | ------------------- |
| 1        | 1       | 2006-02-15 05:05:03 |
| 1        | 23      | 2006-02-15 05:05:03 |
| 1        | 25      | 2006-02-15 05:05:03 |
| 1        | 106     | 2006-02-15 05:05:03 |
| 1        | 140     | 2006-02-15 05:05:03 |
| ...      | ...     | ...                 |
5462 rows × 3 columns

Продвинутый трюк: Кортежи (Tuple Comparison)

SELECT f.title
FROM film f
    INNER JOIN film_actor fa ON f.film_id = fa.film_id
    INNER JOIN actor a ON fa.actor_id = a.actor_id
WHERE (a.first_name, a.last_name) IN (
    ('CATE', 'MCQUEEN'),
    ('CUBA', 'BIRCH')
);

Запрос читается буквально как список людей.
Если нужно будет добавить третьего актёра, просто дописываем строчку (‘TOM’, ‘HANKS’), не раздувая дерево из скобок и операторов OR.

%%sql
SELECT f.title
FROM film f
    INNER JOIN film_actor fa ON f.film_id = fa.film_id
    INNER JOIN actor a ON fa.actor_id = a.actor_id
WHERE
    (a.first_name = 'CATE' AND a.last_name = 'MCQUEEN')
    OR (a.first_name = 'CUBA' AND a.last_name = 'BIRCH');

Этот запрос возвращает все фильмы в которых снимались Cate McQueen или Cuba Birch.

Предположим, что требуется получить только те фильмы, в которых появляются оба актера. Для этого нужно найти все строки в таблице film у которых есть две строки в таблице film_actor, одна из которых связана с Cate McQueen, а другая с Cuba Birch. Следовательно, требуется включить таблицы film_actor и actor дважды – каждый раз с иным псевдонимом, чтобы сервер знал на что именно мы ссылаемся в различных предложениях:

%%sql
SELECT f.title
FROM film f
    INNER JOIN film_actor fa1 ON f.film_id = fa1.film_id
    INNER JOIN actor a1 ON fa1.actor_id = a1.actor_id
    INNER JOIN film_actor fa2 ON f.film_id = fa2.film_id
    INNER JOIN actor a2 ON fa2.actor_id = a2.actor_id
WHERE
    (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN')
    AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');

Эти два актера снялись в 54 разных фильмах, но есть всего два фильма, в которых снялись оба актера.

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

Самосоединение¶

Вы можете не только включать одну и ту же таблицу в один и тот же запрос более одного раза, но и соединять таблицу с самой собой. Поначалу это может показаться странным, но для этого есть веские причины.

Некоторые таблицы включают в себя самоссылающиеся внешние ключи (self-referencing foreign key). Это означает, что в таблице имеется столбец, ссылающийся на первичный ключ в той же таблице.

Хотя образец базы данных такую связь не включает, давайте представим, что в таблице film есть столбец prequel_film_id, который указывает на родительский фильм (например, фильм Потерянный скрипач 2 будет использовать этот столбец, чтобы указать на фильм Потерянный скрипач как на родительский).

Вот как выглядела бы таблица, если бы мы добавили этот дополнительный столбец:

DESC film;

Field	Type	Null	Key	Default
film_id	smallint unsigned	NO	PRI	None
title	varchar(128)	NO	MUL	None
description	text	YES		None
release_year	year	YES		None
language_id	tinyint unsigned	NO	MUL	None
original_language_id	tinyint unsigned	YES	MUL	None
rental_duration	tinyint unsigned	NO		3
rental_rate	decimal(4,2)	NO		4.99
length	smallint unsigned	YES		None
replacement_cost	decimal(5,2)	NO		19.99
rating	enum(‘G’,‘PG’,‘PG-13’,‘R’,‘NC-17’)	YES		G
special_features	set(‘Trailers’,‘Commentaries’,‘Deleted Scenes’,‘Behind the Scenes’)	YES		None
last_update	timestamp	NO		CURRENT_TIMESTAMP
prequel_film_id	smallint(5)	YES	MUL	NULL

Используя самосоединение можно написать запрос, в котором будут перечислены все фильмы с приквелами, включая название приквела:

SELECT f.title, f_prnt.title prequel
FROM film f
    INNER JOIN film f_prnt
        ON f_prnt.film_id = f.prequel_film_id
WHERE f.prequel_film_id IS NOT NULL;

| title           | prequel      |
| --------------- | ------------ |
| FIDDLER LOST II | FIDDLER LOST |

Этот запрос соединяет таблицу film с самой собой с помощью внешнего ключа prequel_film_id. Псевдонимы таблицы f и f_print используются для того, чтобы было понятно, какая таблица и для какой цели используется.

Упражнения¶

Упражнение 5.1¶

Заполните пропущенные места (обозначенные как <#>) в следующем запросе так, чтобы получить показанные результаты.

SELECT c.first_name, c.last_name, a.address, ct.city
FROM customer c
  INNER JOIN address <1>
  ON c.address_id = a.address_id
  INNER JOIN city ct
  ON a.city_id = <2>
WHERE a.district = 'California';

| first_name | last_name | address                | city           |
| ---------- | --------- | ---------------------- | -------------- |
| PATRICIA   | JOHNSON   | 1121 Loja Avenue       | San Bernardino |
| BETTY      | WHITE     | 770 Bydgoszcz Avenue   | Citrus Heights |
| ALICE      | STEWART   | 1135 Izumisano Parkway | Fontana        |
| ROSA       | REYNOLDS  | 793 Cam Ranh Avenue    | Lancaster      |
| RENEE      | LANE      | 533 al-Ayn Boulevard   | Compton        |
| KRISTIN    | JOHNSTON  | 226 Brest Manor        | Sunnyvale      |
| CASSANDRA  | WALTERS   | 920 Kumbakonam Loop    | Salinas        |
| JACOB      | LANCE     | 1866 al-Qatif Avenue   | El Monte       |
| RENE       | MCALISTER | 1895 Zhezqazghan Drive | Garden Grove   |

%%sql
SELECT c.first_name, c.last_name, a.address, ct.city
FROM customer c
  INNER JOIN address a
  ON c.address_id = a.address_id
  INNER JOIN city ct
  ON a.city_id = ct.city_id
WHERE a.district = 'California';

Упражнение 5.2¶

Напишите запрос, который выводил бы названия всех фильмов, в которых играл актер с именем JOHN.

%config SqlMagic.displaylimit = 30

%%sql
SELECT f.title
FROM film f
  INNER JOIN film_actor fa
  ON f.film_id = fa.film_id
  INNER JOIN actor a
  ON fa.actor_id = a.actor_id
WHERE a.first_name = 'JOHN';

Упражнение 5.3¶

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

%%sql
-- Решение автора через декартово произведение `Cross Join`
SELECT a1.address addr1, a2.address addr2, a1.city_id
FROM address a1
  INNER JOIN address a2
WHERE a1.city_id = a2.city_id
  AND a1.address_id <> a2.address_id;

%%sql
-- С точки зрения современного синтаксиса SQL92
-- правильнее и понятнее записать через `ON`
SELECT a1.address addr1, a2.address addr2, a1.city_id
FROM address a1
  INNER JOIN address a2
  ON a1.city_id = a2.city_id
WHERE a1.address_id <> a2.address_id
ORDER BY a1.city_id;

%%sql
SELECT a1.address addr1, a2.address addr2, a1.city_id
FROM address a1
  INNER JOIN address a2
  ON a1.city_id = a2.city_id
WHERE a1.address_id < a2.address_id
ORDER BY a1.city_id;