Join / Sql / database

Это старая версия документа!

Имеем две таблицы

 
U) users               D) departments
id name       d_id     id  name
-- ----       ----     --  ----
1  Владимир    1       1  Сейлз
2  Антон       2       2  Поддержка
3  Александр   6       3  Финансы
4  Борис       2       4  Логистика
5  Юрий        4

SELECT u.id, u.name, d.name AS d_name
FROM users u
INNER JOIN departments d ON u.d_id = d.id

Запрос вернет объединенные данные, которые пересекаются по условию, указанному в INNER JOIN. В результате не присутствует: - пользователь Александр (отдел 6) - отдел Финансы (нет пользователей)

id name       d_name
-- --------   ---------
1  Владимир    Сейлз
2  Антон       Поддержка
4  Борис       Поддержка
3  Юрий        Логистика

рис. Inner join

INNER JOIN это синоним для JOIN. Выбираются только совпадающие данные из объединяемых таблиц. Чтобы получить данные, которые не подходят по условию, необходимо использовать OUTER JOIN. Такое объединение вернет данные из обеих таблиц совпадающими по одному из условий.

рис. Left join

Существует два типа внешнего объединения (OUTER JOIN) - LEFT OUTER JOIN и RIGHT OUTER JOIN. Работают они одинаково, разница заключается в том что LEFT - указывает что "внешней" таблицей будет находящаяся слева. В нашем примере это таблица users.

SELECT u.id, u.name, d.name AS d_name
FROM users u
LEFT OUTER JOIN departments d ON u.d_id = d.id

Получаем полный список пользователей и сопоставленные департаменты.

id      name          d_name
--      --------      ---------
1       Владимир      Сейлз
2       Антон         Поддержка
3       Александр     NULL
4       Борис         Поддержка
5       Юрий          Логистика

рис. Left outer join с фильтрацией по полю

RIGHT OUTER JOIN вернет полный список департаментов (правая таблица) и сопоставленных пользователей.

SELECT u.id, u.name, d.name AS d_name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id 
WHERE u.id IS NULL

id      name        d_name
--      --------    ---------
1       Владимир    Сейлз
2       Антон       Поддержка
4       Борис       Поддержка
NULL    NULL        Финансы
5       Юрий        Логистика

Дополнительно можно отфильтровать данные, проверяя их на NULL. В нашем примере указав WHERE u.id IS null, мы отбросим записи, в которых пользователи не числятся в отделах.

Информация:

Так называемая выборка с замыканием на себя (и совсем не closure). Нужна нам, если необходимо выбрать более одного значения из таблицы для нескольких условий.

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

CREATE TABLE filts_data
(
  id serial NOT NULL,
  fid integer NOT NULL,  -- product_item.id 
  value integer NOT NULL, -- значение фильтра filts_items.id
  pid integer NOT NULL -- фильтр filts.id
)

Есть таблица условных товаров product_item

CREATE TABLE product_item
(
  id serial NOT NULL,
  section_id integer,
  date timestamp,
  art text,
  title text
)

Пример: выбрать записи, добавленные после 17/01/2009 и с установленными фильтрами 3=14 и 4=15 и 6=19. Логика подскажет нам такой запрос (нерабочий):

SELECT p1.title FROM products_item p1 INNER JOIN filts_data p2 ON p1.id = p2.fid 
WHERE p1.date > '17.01.2009' AND (p2.pid = 3 AND p2.value = 14) AND (p2.pid = 4 AND p2.value = 15) AND (p2.pid = 6 AND p2.value = 19)

Этот запрос не найдет элементов в таблице. Перепишем запрос, используя join на себя:

SELECT p1.* FROM product_item p1 INNER JOIN filts_data p2 ON p1.id = p2.fid  INNER JOIN filts_data p3 ON p1.id = p3.fid  INNER JOIN filts_data p4 ON p1.id = p4.fid WHERE p1.date > '17.01.2009' AND (p2.pid = 3 AND p2.value = 14) AND (p3.pid = 4 AND p3.value = 15) AND (p4.pid = 6 AND p4.value = 19)

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

Join / Sql / database

Self joins