Теория: Типы соединения таблиц

Соединения — важная часть языка SQL. С помощью соединений мы можем связать строки одной таблицы со строками другой. Например, если в одной таблице хранится информация об авторах, а в другой — информация о книгах, то соединение свяжет книги с их авторами.

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

Пять типов соединения таблиц

В языке SQL есть пять видов инструкции JOIN — типов соединения таблиц:

1. CROSS JOIN
2. INNER JOIN
3. LEFT JOIN или LEFT OUTER JOIN
4. RIGHT JOIN или RIGHT OUTER JOIN
5. FULL JOIN или FULL OUTER JOIN

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

Разберем обе ситуации, а также каждый тип подробнее.

Соединение без условия

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

Например, есть таблица с разными цветами: красный, зеленый, белый. Также есть таблица с тканевыми материалами: хлопок, шерсть, шелк. С помощью CROSS JOIN мы получим все возможные сочетания цвета и материала, например: красный хлопок, красный шелк, зеленый хлопок и так далее.

SELECT
    color_name,
    material_name
FROM colors
CROSS JOIN materials;

View on DBfiddle

Соединения с условием

В эту группу попадают остальные типы: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN. В их случаях в результат попадают не все сочетания, а только те, которые удовлетворяют специальному условию соединения.

Рассмотрим пример. Допустим, у нас есть две таблицы:

• authors (author_id, author_name) — информация об авторах
• books (book_id, book_author_id, title) — информация о книгах

View on DBfiddle

Если мы хотим получить список книг с именами их авторов, то нам нужны не все возможные сочетания авторов и книг, а только те, в которых автор один и тот же: authors.author_id = books.book_author_id. Это равенство и будет условием соединения таблиц.

Выбор типа соединения с условием

Чтобы выбрать тип соединения с условием, нужно узнать, есть ли в таблицах строки без пары.

Допустим, что у нас могут быть авторы без книг, то есть в таблице books нет строк с такими значениями author_id. Условие соединения authors.author_id = books.book_author_id не выполнится для таких авторов.

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

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

Виды соединений

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

INNER JOIN

Это соединение отбирает только такие пары строк, которые удовлетворяют специальному условию соединения.

SELECT
    author_name,
    title
FROM books
INNER JOIN authors ON book_author_id = author_id;

View on DBFiddle

LEFT JOIN

LEFT JOIN используют, когда нужно построить соединение, в котором обязательно будут все строки из первой таблицы. Если для строки из первой таблицы нет подходящих пар во второй таблице, то вместо данных из второй таблицы будут подставлены NULL-значения.

SELECT
    dep.name AS department,
    emp.name AS employee
FROM departments AS dep
LEFT JOIN employees AS emp ON
    dep.department_id = emp.department_id;

View on DB Fiddle

RIGHT JOIN

RIGHT JOIN работает похожим образом, но только в результат попадают все строки из второй таблицы, а не из первой.

SELECT
    dep.name as department,
    emp.name as employee
FROM departments AS dep
RIGHT JOIN employees AS emp ON
    dep.department_id = emp.department_id;

View on DB Fiddle

FULL JOIN

Полное внешнее соединение используют, когда в результате должны быть все строки из обеих таблиц, даже если у них нет подходящей пары. Результат FULL JOIN включает в себя результаты INNER JOIN, LEFT JOIN и RIGHT JOIN.

SELECT
    dep.name as department,
    emp.name as employee
FROM departments AS dep
FULL JOIN employees AS emp ON
    dep.department_id = emp.department_id;

View on DB Fiddle

Выводы

Соединения позволяют связать данные из двух таблиц. В языке SQL есть несколько разновидностей соединений:

• CROSS JOIN — это соединение без условия, оно возвращает все возможные сочетания строк из двух таблиц.
• INNER JOIN — это соединение возвращает только те пары строк, которые удовлетворяют специальному условию.
• LEFT JOIN, RIGHT JOIN, FULL JOIN — это соединения, которые добавляют к результату INNER JOIN строки без пары