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

В прошлой теме было рассмотрено неявное соединение таблиц с помощью простой выборки путем сведения данных. Однако более распространенным подходом свдения данных из разных таблиц является применение оператора JOIN. SQLite поддерживает несколько вариантов оператора JOIN. В этой статье рассмотрим INNER JOIN. Общий формальный синтаксис этой версии оператора :

SELECT столбцы
FROM таблица1
	[INNER] JOIN таблица2
	ON условие1
	[[INNER] JOIN таблица3
	ON условие2]

После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER. Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.

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

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

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

SELECT orders.created_at, orders.items_count, products.name
FROM orders
JOIN products ON products.id = orders.product_id;

Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, "orders.items_count".

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

SELECT O.created_at, O.items_count, P.name
FROM orders AS O
JOIN products AS P
ON P.id = O.product_id;

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

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

Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:

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

Условия после ключевого слова ON могут быть более сложными по составу:

SELECT orders.created_at, customers.name, products.name
FROM orders
JOIN products ON products.id = orders.product_id AND products.company='Apple'
JOIN customers ON customers.id=orders.customer_id
ORDER BY customers.name;

В данном случае выбираем все заказы на товары, производителем которых является Apple.