Много таблици и таблица със себе си

Накратко: Няколко JOIN един след друг свързват верига от таблици, а self join свързва таблица със самата нея, например категорията с родителската ѝ категория.

Проблем

Оборотът по категории

Собственичката иска да знае от кои категории идват парите. Количеството и цената са в order_items. Дали поръчката е отказана, пише в orders. Категорията на продукта е в products, а името на категорията в categories.

Четири таблици за един отчет. JOIN не спира на две.

Идеята

Верига от JOIN

Всеки следващ JOIN добавя таблица към това, което вече е събрано. Поръчка 60, с продуктите и категориите им:

SQLбаза: магазин

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.id = 60;

SELECT o.id, p.name AS product, cat.name AS category, oi.quantity FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.id = 60;

▶ ИзпълниВърни заявкатаНова база

Пътят е по външните ключове: orders → order_items (по order_id) → products (по product_id) → categories (по category_id). Всяка връзка е отделен JOIN … ON.

И products, и categories имат колона name. Затова тук им даваме имена с AS: product и category. Иначе в резултата щеше да има две колони name.

Пробвай

Кой какво е оценил

Покажи за всеки отзив името на клиента, името на продукта и оценката.

SQLбаза: магазин

SELECT r.customer_id, r.product_id, r.rating
FROM reviews r;

SELECT r.customer_id, r.product_id, r.rating FROM reviews r;

Провери▶ Изпълни

Виж го

Изгубените пари

Оборотът по категории, без отказаните поръчки:

SQLбаза: магазин

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT cat.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ ИзпълниВърни заявкатаНова база

Изглежда наред. Сега целият оборот, без категории:

SQLбаза: магазин

SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled';

SELECT SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled';

▶ ИзпълниВърни заявкатаНова база

Шестте категории дават 1 537 €, а оборотът е 1 697 €.

1. Сборът по категории е 1 537 €, а целият оборот е 1 697 €. Къде са 160 €?

В отказаните поръчкиВ продажбите на подаръчната карта: тя е без категория и JOIN categories я изхвърлиГрешка от закръгляне

Провери

Във верига от JOIN всяка връзка може тихо да изхвърли редове. За продукт без категория двойка в categories няма. С LEFT JOIN към категориите продажбите остават, а COALESCE дава име на липсващата категория:

SQLбаза: магазин

SELECT COALESCE(cat.name, 'Без категория') AS category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
LEFT JOIN categories cat ON cat.id = p.category_id
WHERE o.status <> 'cancelled'
GROUP BY cat.name
ORDER BY revenue DESC;

SELECT COALESCE(cat.name, 'Без категория') AS category, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id LEFT JOIN categories cat ON cat.id = p.category_id WHERE o.status <> 'cancelled' GROUP BY cat.name ORDER BY revenue DESC;

▶ ИзпълниВърни заявкатаНова база

Седем реда и сборът е 1 697 €. Щом отчетът брои пари, сравни сбора му с общия. Разликата показва изгубените редове.

Пробвай

Оборотът по градове

Покажи оборота по град на клиента, без отказаните поръчки.

SQLбаза: магазин

Провери▶ Изпълни

Идеята

Таблица, свързана със себе си

В категориите parent_id сочи към id в същата таблица. За да видиш категорията до родителската ѝ, свързваш categories със себе си. Таблицата влиза два пъти, с две различни кратки имена:

SQLбаза: магазин

SELECT c.name AS category, parent.name AS parent
FROM categories c
LEFT JOIN categories parent ON parent.id = c.parent_id;

SELECT c.name AS category, parent.name AS parent FROM categories c LEFT JOIN categories parent ON parent.id = c.parent_id;

▶ ИзпълниВърни заявкатаНова база

c е категорията, parent е същата таблица, видяна като родител. ON parent.id = c.parent_id казва: родителят е редът, чието id е равно на parent_id на категорията. Това се казва self join. Тук кратките имена са задължителни: без тях name от двете копия не се различава.

LEFT JOIN е, за да останат „Храни“ и „Дом“, които нямат родител.

Упражнение

Две задачи

1. За новия каталог трябват всички 25 продукта с категорията и родителската категория. Подаръчната карта също трябва да е в списъка.

Продуктът, категорията и родителската категория, за всички продукти:

SQLбаза: магазин

SELECT p.name AS product, cat.name AS category
FROM products p
JOIN categories cat ON cat.id = p.category_id;

SELECT p.name AS product, cat.name AS category FROM products p JOIN categories cat ON cat.id = p.category_id;

Провери▶ Изпълни

2. Колко е оборотът на двете главни категории, „Храни“ и „Дом“? Без отказаните поръчки.

Оборотът по главна категория:

SQLбаза: магазин

Провери▶ Изпълни

По-точно, за любопитните

Редът на JOIN в една заявка не променя резултата на обикновения JOIN: базата сама избира от коя таблица да започне. При LEFT JOIN редът е важен, защото „лява“ е всичко, събрано преди него.

Таблица, свързана със себе си, дава само едно ниво нагоре. Дърво с много нива (подкатегория на подкатегория) се обхожда с WITH RECURSIVE, в модула за подзаявките.

Тест

Провери се

1. Заявка свързва orders, order_items, products и categories. Колко JOIN … ON има в нея?

ТриЧетириДва

2. Защо в FROM categories c LEFT JOIN categories parent … кратките имена са задължителни?

Защото една и съща таблица участва два пъти и без тях c.name и parent.name не се различаватЗащото LEFT JOIN ги изискваНе са задължителни

3. Отчет по категории с JOIN categories губи продуктите без категория. Как се пазят?

С ORDER BYС WHERE category_id IS NOT NULLС LEFT JOIN categories и COALESCE за името

4. В резултата има две колони name (на клиента и на продукта). Какво правиш?

Даваш им имена с AS: customer, productНищо, базата ще ги различиМахаш едната

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: GROUP BY: отчет по групи · Първичен и външен ключ · JOIN: две таблици в една заявка · LEFT JOIN: и редовете без двойка
• Продължава в: UNION, INTERSECT, EXCEPT и CROSS JOIN · Отчет на стъпки: WITH · Дървета и редици: WITH RECURSIVE · Изгледи
• Задачи по темата: Свързване на таблици