Доброго времени суток! В этой статье по языку 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 и фамилии продавца. Вывод запроса аналогичен предыдущей статье:
amt | odate |
---|---|
348 | 2017-04-08 |
80 | 2017-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
Вывод:
cname | odate |
---|---|
Чудинов | 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 очень просто и легко !
Спасибо, ребята, хорошие задачи и понятные обьяснения
Не когда не понимала подобные объяснения с этим join,на столько все усложнили зачем только не известно.