INNER JOIN в SQL: две таблици в една заявка

Проблем

Поръчките с имената

Куриерската фирма иска списък: номер на поръчката, дата и името на клиента. Номерът и датата са в orders, името е в customers.

В миналия урок го правихме с две заявки и числото между тях го пренасяхме на ръка. За 60 поръчки това са 60 търсения. Трябва ни една заявка, която сама намира клиента на всяка поръчка.

Идеята

JOIN … ON

JOIN слага до всеки ред на едната таблица реда от другата, който му отговаря. ON казва кой ред на кой отговаря:

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

SELECT orders.id, orders.ordered_at, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

За всяка поръчка базата търси клиента, чието customers.id е равно на orders.customer_id, и ги слепва в един ред. Затова колоните се пишат с името на таблицата отпред: orders.id, customers.name.

Излизат 60 реда, колкото са поръчките: всяка поръчка има точно един клиент. Това се казва JOIN (или INNER JOIN): остават само двойките, които се намират. Теодора и Явор ги няма в списъка, защото нямат поръчки. Към тях ще се върнем в следващия урок.

На малки таблици се вижда как става: четири поръчки и трима клиенти, от които Теодора е без поръчки.

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Стъпка по стъпка, с думи

4 поръчки и 3 клиента. customer_id на поръчката е id на нейния клиент.
Поръчка 1 има customer_id 2. Базата търси клиента с id 2: Георги Петров. Двойката става ред.
Поръчка 2: customer_id 1, това е Мария Иванова.
Поръчка 3 е пак на клиент 1: Мария Иванова е в резултата втори път.
Поръчка 4 е пак на клиент 2: Георги Петров е в резултата втори път.
Теодора Генова (id 3) не е customer_id на нито една поръчка. Няма двойка, затова я няма в резултата.
4 реда, колкото са поръчките. JOIN оставя само двойките, които се намират.

Същата заявка върху тези малки таблици. Пусни я и сравни с последната стъпка:

SQLпразна база

SELECT orders.id, orders.customer_id, customers.name
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Идеята

Кратки имена

orders.ordered_at и customers.name стават дълги. На таблицата може да се даде кратко име за заявката: пишеш го след името ѝ във FROM и JOIN.

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

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Същата заявка, същият резултат. o е orders, c е customers. Краткото име важи само в тази заявка. Може да пишеш и FROM orders AS o.

Пробвай

Новите поръчки

Покажи номера на новите поръчки (status е new) с името и имейла на клиента, за да им се изпрати потвърждение.

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

SELECT id, customer_id FROM orders WHERE status = 'new';

Решение

SELECT o.id, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'new';

Виж го

„ambiguous column name“

Ето заявка без имената на таблиците. Пусни я:

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

SELECT id, name, ordered_at
FROM orders
JOIN customers ON customer_id = id;

И orders, и customers имат колона id. След JOIN колоните на двете са заедно и базата не знае за коя id говориш. „Ambiguous“ значи „двусмислено“. Колоната name я има само в customers, затова за нея не се оплаква.

Решението е да кажеш от коя таблица е колоната:

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

SELECT o.id, c.name, o.ordered_at
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Добрият навик е да пишеш краткото име пред всяка колона в заявка с JOIN, дори когато не е задължително. Който я чете, веднага вижда откъде е всяка колона.

Идеята

JOIN, WHERE и ORDER BY

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

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

SELECT o.id, o.ordered_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Варна'
ORDER BY o.ordered_at;

Градът е в customers, датата е в orders. Редът на частите е: SELECT … FROM … JOIN … ON … WHERE … ORDER BY.

Пробвай

Какво има в поръчка 60

Покажи името на продукта, броя и цената за всеки ред на поръчка 60.

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

SELECT product_id, quantity, unit_price FROM order_items WHERE order_id = 60;

Решение

SELECT p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = 60;

Упражнение

Две задачи

1. Магазинът проверява защо се отказват поръчки. Покажи номера на отказаните поръчки (cancelled) с името и града на клиента.

Отказаните поръчки с клиента и града му:

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

Решение

SELECT o.id, c.name, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'cancelled';

2. Отделът за качество иска отзивите с оценка 3 или по-ниска заедно с името на продукта, за да знае какво да провери.

Ниските оценки: продуктът, оценката и текстът на отзива:

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

Решение

SELECT p.name, r.rating, r.body
FROM reviews r
JOIN products p ON p.id = r.product_id
WHERE r.rating <= 3;

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

Ще срещнеш и по-стария запис: таблиците във FROM със запетая, а условието за двойките в WHERE.

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

SELECT o.id, c.name
FROM orders o, customers c
WHERE c.id = o.customer_id;

Резултатът е същият. Записът с JOIN … ON държи условието за свързване отделно от условията за филтриране. Затова в курса пишем JOIN.

Тест

Провери се

1. Какво казва ON c.id = o.customer_id?Кой ред от customers върви с кой ред от ordersКои колони да се покажатКак да се подредят редовете

2. Защо SELECT id FROM orders JOIN customers … дава „ambiguous column name: id“?Защото id не може да се показва след JOINЗащото id е запазена думаЗащото и двете таблици имат колона id и базата не знае коя искаш

3. В заявката FROM orders o JOIN customers c какво е o?Кратко име на orders за тази заявкаКолонаНово копие на таблицата

4. Къде отива WHERE в заявка с JOIN?Преди JOINСлед JOIN … ON и преди ORDER BYНакрая, след ORDER BY

JOIN: две таблици в една заявка

Бисквитки