SQL JOINS

• 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;