Соединение таблиц с помощью LEFT JOIN

Кроме внутреннего соединения таблиц с помощью INNER JOIN. Но также SQLite позволяет использовать и так называемое левостороннее внешнее соединение с помощью операторов LEFT JOIN. В отличие от INNER JOIN внешнее левостороннее соединение возвращает все строки из первой (левой) таблицы.

Outer Join имеет следующий формальный синтаксис:

SELECT столбцы
FROM таблица1
	LEFT JOIN таблица2 ON условие1
	LEFT JOIN таблица3 ON условие2...

Возьмем таблицы с данными из прошлой темы:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE products
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    company TEXT NOT NULL,
    items_count INTEGER DEFAULT 0,
    price INTEGER
);

CREATE TABLE customers
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);
CREATE TABLE orders
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
	created_at TEXT NOT NULL,
    items_count INTEGER DEFAULT 1,
    price INTEGER NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

Пусть эти таблицы будут содержать следующие данные:

INSERT INTO products (name, company, items_count, price)
VALUES
('iPhone 13', 'Apple', 3, 76000),
('iPhone 12', 'Apple', 2, 51000),
('Galaxy S21', 'Samsung', 2, 56000),
('Galaxy S20', 'Samsung', 1, 41000),
('P40 Pro', 'Huawei', 5, 36000);

INSERT INTO customers(name) VALUES ('Tom'), ('Bob'),('Sam');

INSERT INTO orders (product_id, customer_id, created_at, items_count, price)
VALUES
( 
    (SELECT id FROM products WHERE name='Galaxy S21'),
    (SELECT id FROM customers WHERE name='Tom'),
    '2021-11-30', 
    2, 
    (SELECT price FROM products WHERE name='Galaxy S21')
),
( 
    (SELECT id FROM products WHERE name='iPhone 12'),
    (SELECT id FROM customers WHERE name='Tom'),
    '2021-11-29',  
    1, 
    (SELECT price FROM products WHERE name='iPhone 12')
),
( 
    (SELECT id FROM products WHERE name='iPhone 12'),
    (SELECT id FROM customers WHERE name='Bob'),
    '2021-11-29',  
    1, 
    (SELECT price FROM products WHERE name='iPhone 12')
);

Например, соединим таблицы orders и customers:

SELECT name, created_at, items_count, price, product_id 
FROM orders LEFT JOIN customers 
ON orders.customer_id = customers.id;

Таблица orders является первой или левой таблицей, а таблица customers - правой таблицей. Поэтому, так как здесь используется выборка по левой таблице, то вначале будут выбираться все строки из orders, а затем к ним по условию orders.customer_id = customers.id будут добавляться связанные строки из customers.

По вышеприведенному результату может показаться, что левостороннее соединение аналогично INNER JOIN, но это не так. INNER JOIN объединяет строки из дух таблиц при соответствии условию. Если одна из таблиц содержит строки, которые не соответствуют этому условию, то данные строки не включаются в выходную выборку. LEFT JOIN выбирает все строки первой таблицы и затем присоединяет к ним строки правой таблицы. К примеру, возьмем таблицу customers и добавим к покупателям информацию об их заказах:

-- INNER JOIN
SELECT name, created_at, items_count, price 
FROM customers JOIN orders 
ON orders.customer_id = customers.id;

-- LEFT JOIN
SELECT name, created_at, items_count, price 
FROM customers LEFT JOIN orders 
ON orders.customer_id = customers.id;

В случае с LEFT JOIN SQLite выбирает сначала всех покупателей из таблицы customers, затем сопоставляет их с заказами из таблицы orders через условие orders.customer_id = customers.id. Однако не у всех покупателей есть заказы. В этом случае покупателю для соответствующих столбцов устанавливаются значения NULL.

Подобным образом с помощью Используем левостороннее соединение для добавления к заказам информации о пользователях и товарах:

SELECT customers.name, orders.created_at, 
       products.name, products.company
FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.id
LEFT JOIN products ON orders.product_id = products.id;

И также можно применять более комплексные условия с фильтрацией и сортировкой. Например, выберем все заказы с информацией о клиентах и товарах по тем товарам, у которых цена меньше 55000, и отсортируем по дате заказа:

SELECT customers.name, orders.created_at, 
       products.name, products.company
FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.id
LEFT JOIN products ON orders.product_id = products.id
WHERE products.price < 55000
ORDER BY orders.created_at;

Или выберем всех пользователей из customers, у которых нет заказов в таблице orders:

SELECT name FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.customer_id IS NULL;

Также можно комбинировать INNER JOIN и LEFT JOIN:

SELECT customers.name, orders.created_at, 
       products.name, products.company
FROM orders 
JOIN products ON orders.product_id = products.id AND products.price > 45000
LEFT JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.created_at;

Вначале по условию к таблице orders через Inner Join присоединяется связанная информация из products, затем через LEFT Join добавляется информация из таблицы customers.