- INNER JOIN: Возвращает все записи, когда есть хотя бы одно совпадение в обеих таблицах
- LEFT JOIN: Возвращает все записи из левой таблицы, и совпавшие с условием записи из правой таблицы.
- RIGHT JOIN: Возвращает все записи из правой таблицы и совпавшие с условием записи из левой таблицы.
- FULL JOIN: Возвращает все записи, когда есть совпадение с условием в одной из таблиц.
INNER JOIN
Inner join возвращает записи обеих таблиц, только если ключи в обеих таблицах удовлетворяют условия объединения.
SELECT * FROM books INNER JOIN chapters ON books.id = chapters.book_id
В данном случае books.id
является Primary Key в таблице books
, а chapters.book_id
является Foreign Key в таблице 'chapters`.
По какой-то причине Inner join работает только с утверждающими условиями, но не с отрицающими:
SELECT * FROM books INNER JOIN chapters ON books.id <> chapters.book_id
результатом будет месего ненужных данных.
Возьмем более практичный пример: предположим, что мы хотим получить список тех заказчиков, которые что-то у нас купили и увидеть некоторые детали этих заказов. Это идеальный пример, когда нужен INNER JOIN
:
SELECT first_name, last_name, order_date, order_amount
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id
только те заказчики, которые делали заказ будут представленны в результате, и если кто-то делал более одного заказа, то по каждому заказу, то есть совпадению c.id
c o.customer_id
будет сгенерирована отдельная запись, каждая с именем заказчика и деталями заказа.
OUTER JOIN
В то время, как inner join возвращает результат который подошел по условию в обеих таблицах, outer join возвращает все записи из одной таблицы, фильтруя по условию записи в другой. Таким образом чать колонок таблицы будет содержать NULL.
LEFT JOIN
Этот вид outer join возвращает каждую запись таблицы слева, которая подошла по условию выражения WHERE
, если таковое присутствует, вне зависимости от того, подходит ли данная запись под выражение ON
перед правой таблицей.
SELECT books.title, author.last_name FROM authors LEFT JOIN books ON books.author_id = authors.id
Левая таблица в данном случае - это authors потому, что это основная таблица выражения FROM
. Правая таблица - это books потому, что является частью выражения JOIN
. Посколько это пример LEFT JOIN
и условие WHERE
отсутствует, все записи из из таблицы authors будут включены в результат. Но только те записи из таблицы books, которые соответствуют условию ON
где books.author_id = authors.id
будут включены в результат запроса.
Вернемся к примеру с таблицей покупателей и заказов. Если мы просто хотим добавить информацию по заказам к таблице заказчиков, то нам нужен LEFT JOIN
. В данной ситуации будут взяты все записи из таблицы клиентов и к ним будут добавлены подходящие под запрос записи из таблицы заказов.
Вот так:
SELECT first_name, last_name, order_date, order_amount
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
там где у заказчика не было заказов в полях заказа будет зиять NULL
. И ели мы хотим получить информацию о заказчиках, которые не размещали никаких заказов, нам достатачно сделать так:
SELECT first_name, last_name, order_date, order_amount
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
WHERE order_date is NULL
RIGHT JOIN
Зеркальное отражение LEFT JOIN
. Вместо того, что бы возвращать все результаты с лева, правые джоины
возвращают все записи с правой стороны, фильтруя результаты левой таблицы так, чтобы они соответствовали условию ON
. Если в левой таблице не найдено записей, соответствующих условию ON
, то соответствующие поля в полученом результате будут заполнены NULL
.
Необходимо отметить, что если просто поменять порядок таблиц в RIGHT JOIN
, то результат будет аналогичен LEFT JOIN
. Так что порядок в джоинах очень важен.
В примере с заказами и заказчиками если мы напишем условие WHERE first_name = NULL
, то запрос выдаст все заказы у которых нет заказчика, то есть мусор базы данных, чтобы его потом можно было удалить.
SELECT first_name, last_name, order_date, order_amount
FROM customers c
RIGHT JOIN orders o
ON c.id = o.customer_id
WHERE first_name IS NULL
FULL OUTER JOIN
Эта команда возвращает все записи из левой и из правой таблицы, по сути совмещая результаты LEFT JOIN
и RIGHT JOIN
. Если есть записи в левой таблицы, которые никак не совпадают с записями из правой таблицы, они все равно будуд выведены, а колонки без совпадений будут заполненны NULL
, ну и соответственно наоборот.
SELECT customers.name, orders.order_number
FROM customers
FULL OUTER JOIN orders
ON customers.id=orders.customer_id
ORDER BY customers.name;