Какие бывают JOIN?

На мой взгляд САМЫЙ часто задаваемый вопрос на собеседованиях тестировщику (его спросят 99,99%). Поэтому лучше в нем разобраться. В SQL существует несколько типов объединений (JOIN), которые позволяют объединять строки из двух или более таблиц на основе определенного условия. Основные типы JOIN включают:

1. INNER JOIN (Внутреннее объединение):

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

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

2. LEFT JOIN (Левое внешнее объединение):

• Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет совпадений, то будут возвращены значения NULL.
• Пример:

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

3. RIGHT JOIN (Правое внешнее объединение):

• Возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет совпадений, то будут возвращены значения NULL.
• Пример:

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

4. FULL OUTER JOIN (Полное внешнее объединение):

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

В этом случае будут возвращены все сотрудники и все отделы, даже если у них нет соответствий в другой таблице.

5. CROSS JOIN (Перекрестное объединение):

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

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

другими словами CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц.

Рассмотрим такой пример, есть две таблицы, в одной из них 3 строки, в другой — 2:

select * from t1;

результат будет

id
----
1
2
3

а вторая таблица

select * from t2;

результат будет

id
----
4
5

Тогда CROSS JOIN будет порождать 6 строк.

select *
from t1
cross join t2;

результат будет 6 строк (не 5)

id | id
----+----
1 | 4
1 | 5
2 | 4
2 | 5
3 | 4
3 | 5

6. SELF JOIN (Самообъединение):

• Это особый случай JOIN, когда таблица соединяется сама с собой. Используется для сравнения строк внутри одной и той же таблицы.
• Пример:

Обратите, внимание, тут нет ошибки. Верно указано INNER JOIN. Действительно отдельной команды нет. Просто мы джойним таблицу саму с собой.

В этом случае мы получаем пары сотрудников, где первый сотрудник подчиняется второму.

7. Ключевое слово LATERAL

Стоит упомянуть это ключевое слово, так как оно используется в таких конструкциях как JOIN LATERAL.

Давайте разбираться.

LATERAL — это ключевое слово в SQL, которое используется для создания подзапросов в блоке FROM, где подзапрос может ссылаться на таблицы, уже упомянутые ранее в этом блоке. Это полезно, когда вам нужно выполнить подзапрос, который зависит от значений из строк внешней таблицы.

Пример использования LATERAL:

Предположим, у вас есть две таблицы: customers (покупатели) и orders (заказы). Вы хотите получить информацию о каждом покупателе и его последнем заказе.

SELECT c.customer_id, c.customer_name, o.order_id, o.order_date

FROM customers c

LEFT JOIN LATERAL (

SELECT o.order_id, o.order_date

FROM orders o

WHERE o.customer_id = c.customer_id

ORDER BY o.order_date DESC

LIMIT 1

) o ON true;

это же картинкой:

Пояснение:

• В этом примере LATERAL позволяет подзапросу внутри блока FROM (в данном случае это SELECT o.order_id, o.order_date ...) ссылаться на внешнюю таблицу customers (сокращенную до c).
• Подзапрос ищет последний заказ для каждого покупателя (сортировка по дате заказа и ограничение на одну строку с помощью LIMIT 1).
• LEFT JOIN LATERAL гарантирует, что даже если покупатель не имеет заказов, он всё равно будет включён в результирующий набор с пустыми значениями для заказов (аналогично обычному LEFT JOIN).

Таким образом, LATERAL даёт возможность делать подзапросы, которые зависят от уже обработанных строк в основном запросе, что делает его мощным инструментом для написания сложных SQL-запросов.

Поддержка:

LATERAL поддерживается такими СУБД, как PostgreSQL, SQL Server (начиная с версии 2016), а также другими современными реляционными базами данных.

Заключение:

JOIN'ы в SQL позволяют проводить сложные запросы, объединяя данные из нескольких таблиц. Каждый тип JOIN имеет свое предназначение, и выбор конкретного типа зависит от задачи, которую нужно решить. Понимание этих типов JOIN и их правильное использование — важный навык в работе с базами данных.

Посмотрите эту же статью в моем канале в Телеграмм (зачем? там можно скопировать код).

Вместо оглавления. Что вы найдете на канале QA Helper - справочник тестировщика?

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

Пишите в комментариях какой пункт было бы интересно рассмотреть более подробно.

Обязательно прочитайте: Что должен знать и уметь тестировщик

Также будет интересно почитать: Вопросы которые задают на собеседовании тестировщикам