Связи между таблицами

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

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

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

Базы данных решают такие задачи размещая информацию в разных таблицах и объединяя их в одном запросе. Собственно, слово "реляционная" и характеризует базу как инструмент выстраивания связей от relation («отношение, зависимость, связь»).

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

Объединение нескольких таблиц в одном запросе

Запрос может объединять несколько таблиц и выводить информацию объединяя их. Для этого есть несколько механизмов. Первый это перечислить необходимые таблицы в поле FROM и указать условия их объединения:

SELECT
    <поля выборки>
FROM
    <таблица 1> [AS <имя1>],
    <таблица 2> [AS <имя2>]
[WHERE
    <условия>]

Рассмотрим базу данных со следующей структурой:

CREATE TABLE author(
  id INTEGER PRIMARY KEY, 
  name  TEXT,
  birthday_year INT
);

CREATE TABLE book(
  id INTEGER PRIMARY KEY,
  title   TEXT, 
  publish_year INT
  author_id INTEGER
);

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

Запрос, который выведет информацию о книге будет выглядеть так:

SELECT 
    author.name as name,
    book.title as title,
    book.publish_year as year
FROM 
    author,
    book
WHERE
    book.year = 1886 AND book.author_id = author.id;

name               title                                                year      
----------------  ---------------------------------------------------   ---------
Лев Толстой        Власть тьмы, или Коготок увяз, всей птичке пропасть  1886      
Антон Чехов        О вреде табака                                       1886
Софья Ковалевская  Воспоминания о Джордже Эллиоте                       1886

Для удобства можно давать более удобные имена таблицам в поле FROM с помощью уже знакомого синтаксиса <имя> AS <новое имя>:

SELECT 
    A.name as name,
    B.title as title,
    B.publish_year as year
FROM 
    author as A,
    book as B
WHERE
    B.year = 1886 AND B.author_id = A.id;

Объединять можно две и более таблиц.

JOIN запросы

Более явной альтернативой синтаксису поиска по нескольким таблицам является использование JOIN запросов. Они легче читаются и ими легче управлять. Синтаксис:

SELECT
    <поля выборки>
FROM
    <основная таблица> [AS <имя>]
JOIN <таблица объединения 1> [AS <имя1>]
    -- Условия объединения
    ON <основная таблица>.<имя столбца> = <таблица объединения 1>.<имя столбца>
WHERE
    -- Дополнительные условия

Условий объединения может быть несколько, тут может находиться условие такое же, как и в поле WHERE. Использование JOIN делает запросы более читаемыми и удобными для работы. В остальном это не влияет на обработку запроса базой.

Предыдущий запрос можно переписать с использованием нового синтаксиса:

SELECT 
    A.name as name,
    B.title as title,
    B.publish_year as year
FROM 
    author as A
JOIN
    book as B ON B.author_id = A.id
WHERE
    B.year = 1886;