Какие типы соединений (JOIN) существуют в PostgreSQL и для чего используется каждый из них?

В PostgreSQL, как и в большинстве реляционных СУБД, JOIN используется для объединения строк из двух или более таблиц на основе связанных столбцов. Вот основные типы:

1. INNER JOIN (или просто JOIN)

   • Что делает: Возвращает только те строки, для которых найдено совпадение в обеих таблицах.
   • Когда использовать: Когда нужно получить данные, которые присутствуют в обеих таблицах. Например, получить список пользователей и их заказов (пользователи без заказов не попадут в выборку).

     SELECT users.name, orders.id
     FROM users
     INNER JOIN orders ON users.id = orders.user_id;

2. LEFT JOIN (или LEFT OUTER JOIN)

   • Что делает: Возвращает все строки из левой таблицы и совпадающие строки из правой. Если совпадения в правой таблице нет, ее столбцы будут заполнены NULL.
   • Когда использовать: Когда нужно получить все записи из одной таблицы и дополнить их данными из другой, если они есть. Например, получить всех пользователей и их заказы (пользователи без заказов тоже попадут в выборку, но поля заказа будут NULL).

     SELECT users.name, orders.id
     FROM users
     LEFT JOIN orders ON users.id = orders.user_id;

3. RIGHT JOIN (или RIGHT OUTER JOIN)

   • Что делает: Зеркальное отражение LEFT JOIN. Возвращает все строки из правой таблицы и совпадающие из левой. Если совпадения нет, столбцы левой таблицы будут NULL.
   • Когда использовать: Реже используется на практике, так как почти всегда можно переписать запрос через LEFT JOIN, поменяв таблицы местами, что часто делает его более читаемым.

     SELECT users.name, orders.id
     FROM users
     RIGHT JOIN orders ON users.id = orders.user_id;

4. FULL OUTER JOIN

   • Что делает: Возвращает все строки из обеих таблиц. Если для строки из одной таблицы нет совпадения в другой, недостающие столбцы заполняются NULL.
   • Когда использовать: Когда нужно получить полный набор данных из двух таблиц, независимо от наличия совпадений. Например, для поиска расхождений в данных.

     SELECT * FROM table1
     FULL OUTER JOIN table2 ON table1.id = table2.id;

5. CROSS JOIN

   • Что делает: Возвращает декартово произведение двух таблиц — каждая строка первой таблицы соединяется с каждой строкой второй. Условие ON не используется.
   • Когда использовать: Редко применяется на реальных данных. Может быть полезно для генерации тестовых данных или комбинаторных наборов.

     SELECT * FROM table1 CROSS JOIN table2;

6. SELF JOIN (Техника, а не отдельный тип)

   • Что делает: Это не отдельный вид JOIN, а техника соединения таблицы с самой собой. Для этого таблице присваиваются разные псевдонимы (aliases).
   • Когда использовать: Когда в одной таблице есть иерархические или рекурсивные связи. Например, найти для каждого сотрудника его руководителя в таблице employees.

     SELECT e.name AS employee_name, m.name AS manager_name
     FROM employees e
     LEFT JOIN employees m ON e.manager_id = m.id;