Skip to content

Объединение таблиц в реляционной базе данных

Содержание

  • Схема базы данных
  • Теория
  • Выборка CROSS JOIN
  • Выборка INNER JOIN
  • Выборка LEFT OUTER JOIN
  • Выборка RIGHT OUTER JOIN
  • Выборка FULL OUTER JOIN

  • Схема базы данных

    USE TESTDB;
    
    CREATE TABLE WAREHOUSES (
       ID INT NOT NULL AUTO_INCREMENT;
       ADDRESS VARCHAR(255),
       PRIMARY KEY (ID)
    );
    
    CREATE TABLE ITEMS (
       ID INT NOT NULL AUTO_INCREMENT,
       NAME VARCHAR(255),
       WAREHOUSE_ID NOT NULL,
       PRIMARY KEY (ID)
    );
    
    INSERT INTO WAREHOUSES (ADDRESS) VALUES ('ul. one, 5');
    INSERT INTO WAREHOUSES (ADDRESS) VALUES ('ul. two, 4');
    INSERT INTO WAREHOUSES (ADDRESS) VALUES ('ul. tree, 7');
    INSERT INTO WAREHOUSES (ADDRESS) VALUES ('ul. four. 7');
    
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('red table', 1);
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('green chair', 1);
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('red spoon', 2);
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('green fork', 2);
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('brown blinds', 3);
    INSERT INTO ITEMS (NAME, WAREHOUSE_ID) VALUES ('white table', 5);
    

    Теория

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

     items:                                    warehouses:
    ┌────┬─────────────┬───────────────┐      ┌────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │      │ id │       address         │
    ├────┼─────────────┼───────────────┤      ├────┼───────────────────────┤
    │  1 │ red table   │       1       │      │  1 │ ul. one, 5            │
    │  2 │ green chair │       1       │      │  2 │ ul. two, 4            │
    │  3 │ red spoon   │       2       │      │  3 │ ul. tree, 7           │
    │  4 │ green fork  │       2       │      │  4 │ ul. four, 7           │
    │  5 │ brown blinds│       3       │      └────┴───────────────────────┘
    │  6 │ white table │       5       │
    └────┴─────────────┴───────────────┘
    

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

  • объединение: Latex formula
  • пересечение: Latex formula
  • декартово или прямое произведение: Latex formula
  • разность: Latex formula
  • симметрическая разность: Latex formula

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

    Выборка CROSS JOIN

    Допустим выполняется такой запрос:

    SELECT * FROM warehouses
    

    В этом случае из таблицы warehouses извлекаются все записи или в терминах теории множеств извлекаются все элементы из множества.
    Теперь изменим запрос и добавим к таблице warehouses таблицу items, то есть выбрать все записи из двух таблиц или в терминах теории множеств выбрать все элементы из двух множеств:

    SELECT * FROM items, warehouses
    

    или согласно стандарту ANSI SQL:

    SELECT * FROM items CROSS JOIN warehouses
    

    В этом случае у нас произойдет построение полного декартового произведения множеств. Декартово произведение множеств это значит, что каждому элементу из множества A сопоставляется каждый элемент из множества B, а в терминах реляционных баз данных это называется CROSS JOIN когда каждой записи из таблицы items сопоставляется каждая запись из таблицы warehouses то есть для каждой записи из таблицы items пройти по каждой записи из таблицы warehouses. Вот как будет выглядеть результат декартова произведения для таблицы items и warehouses:

     items:                             warehouses:  
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         │
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            │
    │  1 │ red table   │       1       │  2 │ ul. two, 4            │
    │  1 │ red table   │       1       │  3 │ ul. tree, 7           │
    |  1 │ red table   │       1       │  4 │ ul. four, 7           │
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  2 │ green chair │       1       │  2 │ ul. two, 4            │
    │  2 │ green chair │       1       │  3 │ ul. tree, 7           │
    │  2 │ green chair │       1       │  4 │ ul. four, 7           |
    │  3 │ red spoon   │       2       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  3 │ red spoon   │       2       │  3 │ ul. tree, 7           │
    │  3 │ red spoon   │       2       │  4 │ ul. four, 7           │
    │  4 │ green fork  │       2       │  1 │ ul. one, 5            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  3 │ ul. tree, 7           │
    │  4 │ green fork  │       2       │  4 │ ul. four, 7           │
    │  5 │ brown blinds│       3       │  1 │ ul. one, 5            │
    │  5 │ brown blinds│       3       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    │  5 │ brown blinds│       3       │  4 │ ul. four, 7           │
    │  6 │ white table │       5       │  1 │ ul. one, 5            │
    │  6 │ white table │       5       │  2 │ ul. two, 4            │
    │  6 │ white table │       5       │  3 │ ul. tree, 7           │
    │  6 │ white table │       5       │  4 │ ul. four, 7           │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    То есть если у нас в первой таблице 4 записи, а во второй 6 записей, то в полном декартовом произведении будет 24 записей.
    Из этого можно сделать один важный и неутешительный вывод. Если записать запрос например такой:

    SELECT * FROM items, warehouses WHERE warehouses.id = items.warehouse_id
    

    то сначала построится полное декартово произведение таблиц (та что выше), а потом из получившегося полного декартового произведения отберутся те записи, которые соответствуют условию warehouses.id = items.warehouse_id:

     items:                             warehouses:  
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         │
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            │
    │  1 │ red table   │       1       │  2 │ ul. two, 4            │
    │  1 │ red table   │       1       │  3 │ ul. tree, 7           │
    |  1 │ red table   │       1       │  4 │ ul. four, 7           │
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  2 │ green chair │       1       │  2 │ ul. two, 4            │
    │  2 │ green chair │       1       │  3 │ ul. tree, 7           │
    │  2 │ green chair │       1       │  4 │ ul. four, 7           |
    │  3 │ red spoon   │       2       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  3 │ red spoon   │       2       │  3 │ ul. tree, 7           │
    │  3 │ red spoon   │       2       │  4 │ ul. four, 7           │
    │  4 │ green fork  │       2       │  1 │ ul. one, 5            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  3 │ ul. tree, 7           │
    │  4 │ green fork  │       2       │  4 │ ul. four, 7           │
    │  5 │ brown blinds│       3       │  1 │ ul. one, 5            │
    │  5 │ brown blinds│       3       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    │  5 │ brown blinds│       3       │  4 │ ul. four, 7           │
    │  6 │ white table │       5       │  1 │ ul. one, 5            │
    │  6 │ white table │       5       │  2 │ ul. two, 4            │
    │  6 │ white table │       5       │  3 │ ul. tree, 7           │
    │  6 │ white table │       5       │  4 │ ul. four, 7           │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    Окончательная таблица примет вид:

     items:                             warehouses:
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         |
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            |
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    Недостаток полного декартового произведения или выборка CROSS JOIN очевиден — выбирается слишком огромное число ненужных записей. В нашем случае для чтобы вернуть из базы 5 записей база данных вынуждена обработать 24 записей. Но а если бы таблица warehouses состояла из 4 тыс. записей, а таблица items из 6 тыс. то полное декартово произведение было бы 4 тыс. x 6 тыс. = 24 млн. записей и все 24 млн. записей загрузились бы в память из которой нужно было бы выбрать несколько записей по критерию который мы напишем в условии.

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

    Выборка INNER JOIN

    Для того чтобы избежать накладных расходов связанных с CROSS JOIN есть другие операции которые существенно сокращают выборку из базы данных. Одной из таких операций является операция INNER JOIN. В терминах теории множеств INNER JOIN будет обозначать операцию пересечение двух множеств или логическое И. Другими словами операция INNER JOIN выбирает из двух таблиц только те записи которые есть И в первой таблице И во второй по какому-то критерию соответствия. Таким критерием соответствия будет условия после ключевого слова ON.

    Рассмотрим такую выборку:

    SELECT * FROM items INNER JOIN warehouses ON warehouses.id = items.warehouse_id;
    

    Эта выборка означает взять таблицу warehouses и выбрать из нее только те записи, которым есть соответствие в таблице items по полю id в таблице warehouses и по полю warehouse_id в таблице items. В результате получилась такая же таблица что приведена выше. В нее не вошли записи с айдишником 4 из таблицы warehouses потому что на складе по адресу ul. four, 7 нету ни одного товара, так же как для товара с айдишником 6 нету соответствующего склада (допустим склад в процессе строительства).

    Как-то так:
    Без имени-1
    Из этой диаграммы видно, что из таблиц warehouses и items отобразятся только те записи у которых ключи из таблицы warehouses и внешние ключи из таблицы items — общие, то есть пересекаются. Например первичный ключ записи | 4 | ul. four, 7 | не пересекается с внешним ключем записи | 6 | white table | 5 | поэтому они не воюдут в результирующую таблицу вывода.

    Выборка LEFT OUTER JOIN (Несимметричное внешнее объединение)

    Выборка OUTER JOIN это та же выборка INNER JOIN только с записями которым не нашлось соответствия.

    SELECT * FROM items LEFT OUTER JOIN warehouses ON warehouses.id = items.warehouse_id;
    

    Найти все записи из warehouses которые совпадают со всеми записями из items по критерию warehouses.id = items.warehouse_id и плюс те записи из левой или ведущей таблицы, то есть из warehouses, которым не нашлось соответствия из ведомой таблицы items.
    Или более простым языком вывести все склады и соответствующие им товары, а если для каких-то складов не найдется соответствующих товаров, то все равно вывести эти склады, а вместо соответствующих товаров отобразить NULL (то есть это будут пустые склады):

     items:                             warehouses:
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         |
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            |
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    │  6 │ white table │       5       │NULL│ NULL                  │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    LEFT OUTER JOIN -> INNER JOIN + остальные записи из левой таблицы, а справа будет NULL
    Без имени-31

    Выборка RIGHT OUTER JOIN

    В выборке RIGHT OUTER JOIN ведущая и ведомая таблица меняются местами:

    SELECT * FROM items RIGHT OUTER JOIN warehouses ON warehouses.id = items.warehouse_id;
    

    Найти все товары и соответствующие им склады, а если для каких-то товаров не найдется соответствующих складов, то все равно вывести эти товары, а в место соответствующих им складов вывести NULL (это будут товары для которых менеджеры еще не значили на какой склад их отправлять):

     items:                             warehouses:
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         |
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            |
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    │NULL│ NULL        │      NULL     │  4 │ ul. four, 7           │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    RIGHT OUTER JOIN -> INNER JOIN + остальные записи из правой таблицы, а слева будет NULL
    Без имени-2

    Выборка FULL OUTER JOIN

    FULL OUTER JOIN это LEFT OUTER JOIN и RIGHT OUTER JOIN объеденены в одну операцию. Такая выборка поддерживается не во всех базах данных в частности в MySQL операции FULL OUTER JOIN нету.

    Запрос FULL OUTER JOIN выглядит так:

    SELECT * FROM items FULL OUTER JOIN warehouses ON warehouses.id = items.warehouse_id
    

    Таблица в результате операции FULL OUTER JOIN будет выглядеть так:

     items:                             warehouses:
    ┌────┬─────────────┬───────────────┬────┬───────────────────────┐
    │ id │    name     │ warehouse_id  │ id │       address         |
    ├────┼─────────────┼───────────────┼────┼───────────────────────┤
    │  1 │ red table   │       1       │  1 │ ul. one, 5            |
    │  2 │ green chair │       1       │  1 │ ul. one, 5            │
    │  3 │ red spoon   │       2       │  2 │ ul. two, 4            │
    │  4 │ green fork  │       2       │  2 │ ul. two, 4            │
    │  5 │ brown blinds│       3       │  3 │ ul. tree, 7           │
    │  6 │ white table │       5       │NULL│ NULL                  │
    │NULL│ NULL        │      NULL     │  4 │ ul. four, 7           │
    └────┴─────────────┴───────────────┴────┴───────────────────────┘
    

    SQL запросы схемотично:
    oZDzuHedV2U

    Линки на другие материалы

    Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER

    Поделиться в социальных сетях

    Опубликовать в Google Plus
    Опубликовать в LiveJournal
    Опубликовать в Мой Мир
    Опубликовать в Одноклассники
    Опубликовать в Яндекс

    Добавить комментарий

    Ваш e-mail не будет опубликован. Обязательные поля помечены *