Self Join. Лекция 6 презентация

Self Join
PostgreSQL имеет специальный тип соединения, называемый SELF JOIN, который используется для соединения таблицы с самой собой. Это удобно при сравнении столбцов строк в одной таблице.
Также важно отметить, что такого ключевого слова, как SELF JOIN, не существует, но это достигается с помощью LEFT JOIN, RIGHT JOIN или INNER JOIN с использованием псевдонимов.

Пример
Давайте рассмотрим несколько примеров использования самосоединений.
В этой таблице сотрудников столбец manager_id ссылается на столбец employee_id. Значение в столбце «manager_id» показывает руководителя, которому непосредственно подчиняется сотрудник. Если значение в столбце Manager_id равно нулю, этот сотрудник никому не отчитывается. Другими словами, он или она является топ-менеджером.

Пример
Следующий запрос использует самосоединение, чтобы определить, кто кому подчиняется:
Этот запрос дважды ссылается на таблицу сотрудников: один как сотрудник, а другой как менеджер. Он использует псевдонимы таблиц e для сотрудника и m для менеджера. Предикат соединения находит пару «сотрудник/менеджер», сопоставляя значения в столбцах «employee_id» и «manager_id». Обратите внимание, что топ-менеджер не появляется в выводе.

Чтобы включить топ-менеджера в набор результатов, вы используете предложение LEFT JOIN вместо предложения INNER JOIN, как показано в следующем запросе:

Предикат соединения соответствует двум разным фильмам (f1.film_id <> f2.film_id), имеющим одинаковую длину (f1.length = f2.length).

Cross Join
Cross Join создаст перекрестное или декартово произведение двух таблиц, если не указано иное условие. Результирующая таблица будет содержать все атрибуты обеих таблиц, включая повторяющиеся или общие столбцы.
CROSS JOIN сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если входные таблицы имеют столбцы x и y соответственно, результирующая таблица будет иметь столбцы x+y. Поскольку CROSS JOIN потенциально может создавать очень большие таблицы, необходимо позаботиться о том, чтобы использовать их только при необходимости.

Пример
Следующий оператор использует оператор CROSS JOIN для соединения таблицы T1 с таблицей T2.
Таблица Т1: Таблица Т2:

На следующем рисунке показан результат CROSS JOIN при соединении таблицы T1 с таблицей T2:

Natural Join
Natural Join объединяет две таблицы на основе одного и того же имени атрибута и типов данных. Результирующая таблица будет содержать все атрибуты обеих таблиц, но только одну копию каждого общего столбца.
Естественным соединением может быть внутреннее соединение, левое соединение или правое соединение. Если вы не укажете соединение явно, например INNER JOIN, LEFT JOIN, RIGHT JOIN, PostgreSQL будет использовать INNER JOIN по умолчанию.
Если вы используете звездочку (*) в списке выбора, результат будет содержать следующие столбцы:
Все общие столбцы, то есть столбцы из обеих таблиц, имеющие одинаковые имена.
Каждый столбец из обеих таблиц, который не является общим столбцом.

Пример
Чтобы продемонстрировать естественное соединение PostgreSQL, мы создадим две таблицы: категории и продукты. В каждой категории имеется ноль или несколько товаров, и каждый товар принадлежит одной и только одной категории.
Столбец Category_id в таблице продуктов — это внешний ключ, который ссылается на первичный ключ таблицы категорий. Category_id — это общий столбец, который мы будем использовать для выполнения естественного соединения.

Следующий оператор использует предложение NATURAL JOIN для соединения таблицы продуктов с таблицей категорий:

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

Difference between Natural JOIN and CROSS JOIN

Difference between Natural JOIN and INNER JOIN

UPDATE Join
Иногда вам необходимо обновить данные в таблице на основе значений в другой таблице. В этом случае вы можете использовать синтаксис соединения PostgreSQL UPDATE следующим образом:
Чтобы присоединиться к другой таблице в инструкции UPDATE, вы указываете присоединенную таблицу в предложении FROM и указываете условие соединения в предложении WHERE. Предложение FROM должно располагаться сразу после предложения SET.
Для каждой строки таблицы t1 оператор UPDATE проверяет каждую строку таблицы t2. Если значение в столбце c2 таблицы t1 равно значению в столбце c2 таблицы t2, оператор UPDATE обновляет значение в столбце c1 таблицы t1 новым значением (new_value).

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

DELETE JOIN
PostgreSQL не поддерживает оператор DELETE JOIN. Однако он поддерживает предложение USING в инструкции DELETE, которое обеспечивает функциональность, аналогичную DELETE JOIN.
Ниже показан синтаксис оператора DELETE с предложением USING:
Сначала укажите табличное выражение после ключевого слова USING. Это может быть одна или несколько таблиц.
Затем используйте столбцы из таблиц, которые появляются в предложении USING в предложении WHERE для объединения данных.

Во-вторых, удалите контакты в таблице контактов, номер телефона которых находится в таблице черного списка: