Self Join. Лекция 6

1.

Лекция 6

2.

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

3.

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

4.

5.

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

6.

7.

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

8.

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

9.

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

10.

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

11.

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

12.

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

13.

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

14.

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

15.

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

16.

Difference between Natural JOIN and CROSS
JOIN
NATURAL JOIN
CROSS JOIN
1
Natural Join объединяет две таблицы на основе
одного и того же имени атрибута и типов
данных.
Cross Join создаст перекрестное или декартово
произведение двух таблиц.
2
В естественном соединении результирующая
таблица будет содержать все атрибуты обеих
таблиц, но сохранит только одну копию каждого
общего столбца.
В перекрестном соединении результирующая
таблица будет содержать все атрибуты обеих
таблиц, включая повторяющиеся столбцы.
3
В естественном соединении, если условие не
указано, оно возвращает строки на основе
общего столбца.
В перекрестном соединении, если не указано
условие, оно возвращает все возможные пары
строк из обеих таблиц, независимо от того,
совпадают они или нет.
5
SYNTAX:
SELECT * FROM table1 NATURAL
JOIN table2;
SYNTAX:
SELECT * FROM table1
CROSS JOIN table2;

17.

Difference between Natural JOIN and INNER
JOIN
NATURAL JOIN
INNER JOIN
1
Natural Join объединяет две таблицы на
основе одного и того же имени атрибута
и типов данных.
Внутреннее соединение объединяет две таблицы на
основе столбца, который явно указан в
предложении ON.
2
В естественном соединении
результирующая таблица будет
содержать все атрибуты обеих таблиц,
но сохранит только одну копию каждого
общего столбца.
При внутреннем объединении результирующая
таблица будет содержать все атрибуты обеих
таблиц, включая повторяющиеся столбцы.
3
В естественном соединении, если
условие не указано, оно возвращает
строки на основе общего столбца.
При внутреннем соединении возвращаются только
те записи, которые существуют в обеих таблицах.
4
SyntaxSELECT *
FROM table1 NATURAL JOIN table2;
SyntaxSELECT *
FROM table1 INNER JOIN table2 ON
table1.Column_Name= table2.Column_Name;

18.

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

19.

20.

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

21.

22.

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

23.

24.

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

25.

Удалить объединение с помощью
подзапроса
• Предложение USING не является частью стандарта SQL. Это означает, что
предложение USING может быть недоступно в других системах баз данных.
• Если вы планируете сделать свое приложение совместимым с другими продуктами
баз данных, вам не следует использовать предложение USING в операторе DELETE.
Вместо этого вы можете использовать подзапрос.
• Следующий оператор использует оператор DELETE для удаления всех контактов,
телефоны которых находятся в таблице черного списка:
• В этом примере подзапрос возвращает список телефонов из таблицы черного
списка, а инструкция DELETE удаляет контакты, телефоны которых совпадают с
телефонами, возвращенными подзапросом.