Нередко возникает необходимость объединенить в одном запросе данные сразу из нескольких таблиц. Для объединения данных из разных таблиц можно применять разные способы. В данной статье рассмотрим не самый распространный, однако довольно простой способ, который представляет неявное соединение таблиц.
Допустим, у нас есть следующие таблицы, которые связаны между собой связями:
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;