Соединение таблиц

Неявное соединение таблиц

Нередко возникает необходимость объединенить в одном запросе данные сразу из нескольких таблиц. Для объединения данных из разных таблиц можно применять разные способы. В данной статье рассмотрим не самый распространный, однако довольно простой способ, который представляет неявное соединение таблиц.

Допустим, у нас есть следующие таблицы, которые связаны между собой связями:

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
);

Здесь таблицы products и customers связаны с таблицей orders связью один ко многим. Таблица orders в виде внешних ключей product_id и customer_id содержит ссылки на столбцы id из соответственно таблиц products и customers. Также она хранит количество купленного товара (items_count) и и по какой цене он был куплен (price). И кроме того, таблицы также хранит в виде столбца created_at дату покупки.

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

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 * FROM orders, customers;

При такой выборке каждая строка из таблицы orders будет соединяться с каждой строкой из таблицы customers. То есть, получится перекрестное соединение. Например, в orders три строки, а в customers тоже три строки, значит мы получим 3 * 3 = 9 строк:

Но вряд ли это тот результат, который хотелось бы видеть. Тем более каждый заказ из orders связан с конкретным покупателем из customers, а не со всеми возможными покупателями.

Чтобы решить задачу, необходимо использовать выражение WHERE и фильтровать строки при условии, что поле customer_id из orders соответствует полю Id из customers:

SELECT * FROM orders, customers
WHERE orders.customer_id = customers.id;

Теперь объединим данные по трем таблицам orders, customers и products. То есть получим все заказы и добавим информацию по клиенту и связанному товару:

SELECT customers.name, products.name, orders.created_at 
FROM orders, customers, products
WHERE orders.customer_id = customers.id AND orders.product_id=products.id;

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

В данном случае названия таблиц сильно увеличивают код, но мы его можем сократить за счет использования псевдонимов таблиц:

SELECT C.name, P.name, O.created_at 
FROM orders AS O, customers AS C, products AS P
WHERE O.customer_id = C.id AND O.product_id=P.id;

Если необходимо при использовании псевдонима выбрать все столбцы из определенной таблицы, то можно использовать звездочку:

SELECT C.name, P.name, O.*
FROM orders AS O, customers AS C, products AS P
WHERE O.customer_id = C.id AND O.product_id=P.id;