Пошук уроків, статей та іншого контенту
Застосуєте LEFT JOIN, щоб зберігати всі записи лівої таблиці навіть без відповідностей у правій.
LEFT JOINLEFT JOIN об’єднує дві таблиці та гарантує, що в результаті залишаться всі рядки з лівої таблиці.
Якщо для рядка лівої таблиці не знайдено відповідного рядка у правій таблиці, PostgreSQL заповнює стовпці правої таблиці значеннями NULL.
Загальний синтаксис:
SELECT
left_table.column,
right_table.column
FROM left_table
LEFT JOIN right_table
ON left_table.key = right_table.foreign_key;Таблиця після FROM є лівою, а таблиця після LEFT JOIN — правою.
Нехай є дві таблиці:
customers — усі клієнти;
orders — замовлення клієнтів.
Деякі клієнти ще не зробили жодного замовлення.
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer REFERENCES customers(id),
total numeric(10, 2) NOT NULL
);
INSERT INTO customers (id, name)
VALUES
(1, 'Олена'),
(2, 'Андрій'),
(3, 'Марія');
INSERT INTO orders (id, customer_id, total)
VALUES
(101, 1, 1500.00),
(102, 1, 800.00),
(103, 2, 320.00);Клієнтка Марія не має замовлень. Щоб отримати всіх клієнтів разом із їхніми замовленнями, використаємо LEFT JOIN:
SELECT
customers.name,
orders.id AS order_id,
orders.total
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.id
ORDER BY customers.id, orders.id;Результат:
name | order_id | total
-------+----------+---------
Олена | 101 | 1500.00
Олена | 102 | 800.00
Андрій| 103 | 320.00
Марія | NULL | NULLДля Марії значення order_id і total дорівнюють NULL, але сам рядок Марії зберігся.
ONУмова після ON визначає, які рядки правої таблиці відповідають рядкам лівої:
ON orders.customer_id = customers.idДля кожного клієнта PostgreSQL шукає замовлення, у яких customer_id збігається з customers.id.
Якщо замовлень немає, PostgreSQL усе одно додає рядок клієнта, а колонки orders заповнює NULL.
Псевдорезультат роботи:
лівий рядок + знайдений правий рядок
лівий рядок + знайдений правий рядок
лівий рядок + NULLЯкщо одному клієнту відповідає кілька замовлень, клієнт з’явиться в результаті кілька разів — по одному разу для кожного замовлення.
Для коротшого та зрозумілішого запиту можна використовувати псевдоніми:
SELECT
c.name,
o.id AS order_id,
o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
ORDER BY c.id, o.id;Псевдоніми особливо корисні, коли таблиці мають довгі назви або містять стовпці з однаковими іменами.
Звернення до стовпця у такому разі має вигляд:
alias.columnНаприклад:
c.name
o.totalОскільки для клієнтів без замовлень значення orders.id буде NULL, можна знайти таких клієнтів за допомогою перевірки IS NULL:
SELECT
c.id,
c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL;Результат:
id | name
----+-------
3 | МаріяДля перевірки NULL не використовують оператор =:
-- Неправильно
WHERE o.id = NULL
-- Правильно
WHERE o.id IS NULLLEFT JOIN і INNER JOININNER JOIN повертає лише рядки, для яких відповідність існує в обох таблицях:
SELECT
c.name,
o.id AS order_id
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.id;У результаті не буде Марії, тому що для неї немає відповідного замовлення.
LEFT JOIN повертає:
усі рядки лівої таблиці;
відповідні рядки правої таблиці;
NULL замість даних правої таблиці, якщо відповідності немає.
Вибір залежить від задачі:
потрібні лише клієнти із замовленнями — INNER JOIN;
потрібні всі клієнти, навіть без замовлень — LEFT JOIN.
ON і умова в WHEREРозташування умови може змінити результат запиту.
Наприклад, потрібно отримати всіх клієнтів і лише замовлення дорожчі за 500:
SELECT
c.name,
o.id AS order_id,
o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.total > 500
ORDER BY c.id, o.id;Умова o.total > 500 перебуває в ON. Тому клієнти без замовлень або без замовлень дорожчих за 500 залишаються в результаті:
name | order_id | total
--------+----------+---------
Олена | 101 | 1500.00
Андрій | NULL | NULL
Марія | NULL | NULLЯкщо перенести цю умову до WHERE:
SELECT
c.name,
o.id AS order_id,
o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.total > 500
ORDER BY c.id, o.id;рядки з NULL будуть відфільтровані. Фактично такий запит поводиться подібно до INNER JOIN для цієї умови:
name | order_id | total
-------+----------+---------
Олена | 101 | 1500.00Правило:
умова в ON обмежує рядки, які приєднуються з правої таблиці;
умова в WHERE фільтрує вже готовий результат, зокрема може вилучити рядки з NULL.
LEFT JOIN часто використовують, щоб отримати кількість пов’язаних записів для кожного рядка лівої таблиці.
SELECT
c.id,
c.name,
COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;Результат:
id | name | orders_count
----+--------+--------------
1 | Олена | 2
2 | Андрій | 1
3 | Марія | 0Важливо використовувати COUNT(o.id), а не COUNT(*).
Для Марії після LEFT JOIN існує один результівний рядок, але o.id у ньому має значення NULL:
COUNT(*) порахував би цей рядок як 1;
COUNT(o.id) не рахує NULL, тому поверне 0.
Нижче наведено самодостатній приклад, який можна виконати в PostgreSQL:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
total numeric(10, 2) NOT NULL CHECK (total >= 0)
);
INSERT INTO customers (id, name)
VALUES
(1, 'Олена'),
(2, 'Андрій'),
(3, 'Марія');
INSERT INTO orders (id, customer_id, total)
VALUES
(101, 1, 1500.00),
(102, 1, 800.00),
(103, 2, 320.00);
-- Усі клієнти та їхні замовлення
SELECT
c.name,
o.id AS order_id,
o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
ORDER BY c.id, o.id;
-- Усі клієнти та кількість їхніх замовлень
SELECT
c.id,
c.name,
COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
-- Клієнти без замовлень
SELECT
c.id,
c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;WHERE right_table.column = ...Такий фільтр може прибрати рядки без відповідності:
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.total > 500;Якщо потрібно зберегти всіх клієнтів, умову краще додати до ON:
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.total > 500;NULL через =Неправильно:
WHERE o.id = NULLПравильно:
WHERE o.id IS NULLДля перевірки відсутності значення також можна використовувати:
WHERE o.id IS NOT NULLCOUNT(*)Для підрахунку пов’язаних записів використовуйте стовпець правої таблиці:
COUNT(o.id)а не:
COUNT(*)Інакше клієнт без замовлень може бути помилково порахований як клієнт з одним замовленням.
Якщо обидві таблиці мають стовпець id, запис:
SELECT idможе спричинити помилку неоднозначності.
Потрібно вказати таблицю або її псевдонім:
SELECT c.id, o.idLEFT JOIN зберігає всі рядки лівої таблиці.
Якщо відповідності у правій таблиці немає, її стовпці мають значення NULL.
Таблиця після FROM є лівою таблицею.
Умова ON визначає, які записи правої таблиці приєднуються.
Для пошуку рядків без відповідності використовують IS NULL.
Умова в WHERE може прибрати рядки з NULL і змінити поведінку LEFT JOIN.
Для підрахунку пов’язаних записів зазвичай використовують COUNT(right_table.id).
LEFT JOIN підходить, коли потрібно показати всі сутності, зокрема ті, що ще не мають пов’язаних записів.