Skip to content

Оператор соединения JOIN SQL

Доброго времени суток! В этой статье по языку SQL мы познакомимся с оператором соединения двух таблиц — JOIN. Как и всегда, разберем практические примеры и посмотрим на различные варианты применения оператора JOIN в SQL.

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

Введение

В прошлых статьях мы уже работали с запросами, которые используют данные из двух и более таблиц: многотабличные запросы и вложенные запросы в SQL. Но те способы имели свои минусы либо по скорости, либо по избыточности данных. Оператор соединения JOIN наиболее выгоден среди этих способов, но его использование подразумевает операции только с двумя таблицами. Тем не менее, запросы выполняются гораздо быстрее, чем вложенные запросы.

В этой статье мы разберем несколько вариантов применения оператора JOIN:

  • INNER JOIN
  • OUTER JOIN
    • RIGHT OUTER JOIN
    • LEFT OUTER JOIN

Про эти варианты использования мы и поговорим подробнее.

Оператор INNER JOIN

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

Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов. Используйте оператор INNER JOIN.

SELECT amt, odate
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum and sname = 'Колованов'

В этом запросе четко видно, что мы берем два поля из таблицы orders, а затем присоединяем таблицу salespeople с нужными нам условиями, которые задаются после ключевого слова ON. В данном случае проверка идет по snum и фамилии продавца. Вывод запроса аналогичен предыдущей статье:

amtodate
3482017-04-08
802017-09-02

Рассмотрим еще один пример на оператор INNER JOIN, уже сложнее:

Вывести среднюю суму заказов для каждого продавца.

SELECT AVG(amt) as 'Средняя цена', salespeople.sname
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.sname

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

Средняя ценаsname
214Колованов
315.667Кучеров
1180Мозякин
640Плотников
900Проворов

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

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

Оператор OUTER JOIN

В языке SQL оператор OUTER JOIN используется гораздо реже, но иногда является очень полезным. Сейчас мы рассмотрим два варианта использования этого оператора. Оператор осуществляет несимметричное внешнее соединение двух таблиц — то есть порядок таблиц важен.

Оператор RIGHT OUTER JOIN

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

SELECT AVG(amt) as 'Средняя цена', salespeople.sname
FROM orders
RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.sname

И вот, что поменялось в выводе:

Средняя ценаsname
214Колованов
315.667Кучеров
NULLМалкин
1180Мозякин
NULLПетров
640Плотников
900Проворов
NULLШипачев

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

Оператор LEFT OUTER JOIN

Аналогичным образом работает и оператор левого внешнего соединения в SQL. При его использовании покажутся все значения для левой таблицы, даже если в правой им соответствуют нулевые значения. Рассмотрим еще один ознакомительный пример:

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

Итак, нам нужны все покупатели — значит в качестве первой (левой) таблицы возьмем таблицу customers, а затем будем присоединять таблицу orders.

SELECT customers.cname, odate
FROM customers
LEFT OUTER JOIN orders on orders.cnum = customers.cnum

Вывод:

cnameodate
Чудинов2016-01-01
Лосев2016-04-10
Краснов2017-04-08
Кириллов2016-06-07
Колесников2017-12-04
Колесников2016-03-03
Лермонтов2017-09-02
Деснов2016-03-07
Кириллов2017-10-07
Пушкин2016-01-08
ЕрмолаевNULL
БелыйNULL

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

Примеры на соединение таблиц в SQL

1.Напишите запрос, который бы использовал оператор INNER JOIN для получения всех Заказов для покупателя с фамилией Краснов.

SELECT onum, amt, odate, cname
FROM orders
INNER JOIN customers on orders.cnum = customers.cnum and cname = 'Краснов'

2.Напишите запрос, который бы вывел фамилии и города продавцов, комиссия которых составляет более 20%.

SELECT DISTINCT(sname), city, comm
FROM salespeople
INNER JOIN orders on orders.snum = salespeople.snum and comm > 20

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

SELECT SUM(amt), salespeople.city
FROM orders
INNER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.city

4.Повторите предыдущий запрос, но выведите все города, даже если в них не совершалась сделка.

SELECT SUM(amt), salespeople.city
FROM orders
RIGHT OUTER JOIN salespeople on orders.snum = salespeople.snum 
GROUP BY salespeople.city

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

SELECT customers.city, MAX(amt)
FROM customers
LEFT OUTER JOIN orders on orders.cnum = customers.cnum 
GROUP BY customers.city

Заключение

После ознакомления с этой статьей у вас должно появиться понимание как работает оператор JOIN в SQL. Его особенности и преимущества над вложенными запросами мы отметили и рекомендуем в своих практических задачах пользоваться именно такими конструкциями. На этом все. Не забывайте оставлять ваши комментарии. До следующей статьи.

Опубликовано вSQL

Один комментарий

  1. Ната Ната

    Компактно и доходчиво, находка для чайников, спасибо!

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *