Множественные условия, составные ключи, диапазоны и CASE в JOIN
Условия соединения не ограничиваются простым равенством колонок. В этой теме изучим множественные условия, составные ключи, диапазонные соединения и использование CASE в JOIN.
Можно указывать несколько условий в ON, соединяя их через AND и OR.
SELECT <колонки>
FROM таблица1
JOIN таблица2 ON условие1 AND условие2 AND ...;CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
product_id INTEGER,
warehouse_id INTEGER
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
warehouse_id INTEGER,
name VARCHAR(100)
);
-- Соединение по product_id И warehouse_id
SELECT o.id, p.name
FROM orders o
JOIN products p ON o.product_id = p.id AND o.warehouse_id = p.warehouse_id;Оба условия должны выполняться для соединения строк.
Когда связь между таблицами определяется несколькими колонками одновременно.
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id) -- Составной первичный ключ
);
CREATE TABLE shipments (
order_id INTEGER,
product_id INTEGER,
shipped_at TIMESTAMP,
PRIMARY KEY (order_id, product_id) -- Составной первичный ключ
);SELECT
oi.order_id,
oi.product_id,
oi.quantity,
s.shipped_at
FROM order_items oi
JOIN shipments s ON oi.order_id = s.order_id AND oi.product_id = s.product_id;Важно: Все колонки составного ключа должны быть в условии ON.
PostgreSQL автоматически создаёт индекс для PRIMARY KEY, но для внешних ключей:
CREATE INDEX idx_shipments_order_product
ON shipments(order_id, product_id);Порядок колонок в индексе важен для производительности.
Соединение по условию диапазона, а не точного совпадения.
CREATE TABLE salaries (
id SERIAL PRIMARY KEY,
employee_id INTEGER,
amount DECIMAL(10, 2)
);
CREATE TABLE tax_brackets (
id SERIAL PRIMARY KEY,
min_salary DECIMAL(10, 2),
max_salary DECIMAL(10, 2),
tax_rate DECIMAL(5, 2)
);
INSERT INTO tax_brackets (min_salary, max_salary, tax_rate) VALUES
(0, 30000, 10.0),
(30000.01, 70000, 20.0),
(70000.01, 150000, 30.0),
(150000.01, NULL, 40.0); -- NULL означает "без верхнего предела"SELECT
s.employee_id,
s.amount AS salary,
tb.tax_rate
FROM salaries s
JOIN tax_brackets tb ON s.amount BETWEEN tb.min_salary AND tb.max_salary;-- Для последнего диапазона (без верхнего предела)
SELECT
s.employee_id,
s.amount AS salary,
tb.tax_rate
FROM salaries s
JOIN tax_brackets tb
ON s.amount >= tb.min_salary
AND (tb.max_salary IS NULL OR s.amount <= tb.max_salary);CREATE TABLE promotions (
id SERIAL PRIMARY KEY,
product_id INTEGER,
discount DECIMAL(5, 2),
valid_from DATE,
valid_to DATE
);
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
sale_date DATE,
amount DECIMAL(10, 2)
);
-- Найти примененные скидки для каждой продажи
SELECT
s.id AS sale_id,
s.product_id,
s.sale_date,
s.amount,
p.discount
FROM sales s
JOIN promotions p
ON s.product_id = p.product_id
AND s.sale_date BETWEEN p.valid_from AND p.valid_to;Условия с операторами >, <, >=, <=, !=.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
-- Найти пары товаров, где первый дешевле второго
SELECT
p1.name AS cheaper,
p2.name AS expensive,
p1.price,
p2.price
FROM products p1
JOIN products p2 ON p1.price < p2.price;CREATE TABLE bookings (
id SERIAL PRIMARY KEY,
room_id INTEGER,
check_in DATE,
check_out DATE
);
-- Найти перекрывающиеся бронирования
SELECT
b1.id AS booking1,
b2.id AS booking2,
b1.room_id,
b1.check_in,
b1.check_out,
b2.check_in,
b2.check_out
FROM bookings b1
JOIN bookings b2
ON b1.room_id = b2.room_id
AND b1.id < b2.id -- Избегаем дублирования пар
AND b1.check_in < b2.check_out -- Пересечение интервалов
AND b1.check_out > b2.check_in;Комбинирование разных типов условий в одном запросе.
SELECT
u.name AS user_name,
o.id AS order_id,
p.name AS product_name,
c.name AS category_name,
w.name AS warehouse_name
FROM users u
JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id AND oi.warehouse_id = p.warehouse_id
JOIN categories c ON p.category_id = c.id AND c.active = true
JOIN warehouses w ON p.warehouse_id = w.id AND w.region = 'EU';Использование CASE для динамического определения условия соединения.
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
type VARCHAR(20), -- 'retail' или 'wholesale'
retail_region_id INTEGER,
wholesale_region_id INTEGER
);
CREATE TABLE regions (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
-- Соединение по разным колонкам в зависимости от типа клиента
SELECT
c.id,
c.type,
r.name AS region_name
FROM customers c
JOIN regions r ON r.id = CASE
WHEN c.type = 'retail' THEN c.retail_region_id
WHEN c.type = 'wholesale' THEN c.wholesale_region_id
END;CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20)
);
CREATE TABLE email_users (
email VARCHAR(100),
user_id INTEGER
);
CREATE TABLE phone_users (
phone VARCHAR(20),
user_id INTEGER
);
-- Предпочитаем email, если есть, иначе phone
SELECT
c.id,
COALESCE(e.user_id, p.user_id) AS user_id
FROM contacts c
LEFT JOIN email_users e ON c.email = e.email
LEFT JOIN phone_users p ON c.phone = p.phone
AND e.user_id IS NULL; -- Используем phone только если email не найденCREATE TABLE users (
id SERIAL PRIMARY KEY,
primary_email VARCHAR(100),
secondary_email VARCHAR(100)
);
CREATE TABLE subscriptions (
email VARCHAR(100),
plan VARCHAR(50)
);
-- Пробуем соединить по primary, если NULL — по secondary
SELECT
u.id,
s.plan
FROM users u
JOIN subscriptions s ON s.email = COALESCE(u.primary_email, u.secondary_email);-- NULLIF предотвращает соединение строк, где обе колонки NULL
SELECT *
FROM table1 t1
JOIN table2 t2 ON NULLIF(t1.col, t2.col) IS DISTINCT FROM NULL;Использование OR для соединения по альтернативным условиям.
CREATE TABLE legacy_users (
id SERIAL PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20),
external_id VARCHAR(50)
);
CREATE TABLE new_users (
id SERIAL PRIMARY KEY,
email VARCHAR(100),
phone VARCHAR(20),
external_id VARCHAR(50)
);
-- Соединение по любому из трёх возможных ключей
SELECT lu.id AS legacy_id, nu.id AS new_id
FROM legacy_users lu
JOIN new_users nu
ON lu.email = nu.email
OR lu.phone = nu.phone
OR lu.external_id = nu.external_id;Предупреждение:
ORв условии JOIN может значительно замедлить запрос, так как оптимизатор не всегда может использовать индексы эффективно.
-- Для условия ON a.id = b.a_id AND a.type = b.type
CREATE INDEX idx_b_a_id_type ON b(a_id, type);Порядок колонок в индексе должен соответствовать порядку в условии.
-- Плохо: функция предотвращает использование индекса
JOIN table2 t2 ON LOWER(t1.name) = LOWER(t2.name);
-- Хорошо: используйте цитилусы или функциональные индексы
CREATE INDEX idx_table2_name_lower ON table2(LOWER(name));
JOIN table2 t2 ON LOWER(t1.name) = LOWER(t2.name);-- Медленно: требует сканирования диапазона
JOIN tax_brackets tb ON salary BETWEEN min AND max;
-- Оптимизация: используйте WHERE для предварительной фильтрации
JOIN tax_brackets tb ON salary >= tb.min
WHERE salary <= tb.max OR tb.max IS NULL;-- Медленно: OR в JOIN
SELECT * FROM t1
JOIN t2 ON t1.a = t2.a OR t1.b = t2.b;
-- Быстрее: UNION отдельных JOIN
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a
UNION
SELECT * FROM t1 JOIN t2 ON t1.b = t2.b;-- Если в tax_brackets перекрывающиеся диапазоны, salary попадёт в несколько строк
SELECT s.employee_id, tb.tax_rate
FROM salaries s
JOIN tax_brackets tb ON s.amount BETWEEN tb.min AND tb.max;
-- Решение: убедитесь, что диапазоны не пересекаются
-- Или используйте DISTINCT / GROUP BY-- Может вернуть неожиданные результаты
SELECT * FROM t1
JOIN t2 ON t1.a = t2.a AND t1.b = t2.b OR t1.c = t2.c;
-- Правильно: явно группируйте условия
SELECT * FROM t1
JOIN t2 ON (t1.a = t2.a AND t1.b = t2.b) OR t1.c = t2.c;-- Неявное приведение типов замедляет запрос
JOIN table2 t2 ON t1.int_column = t2.varchar_column;
-- Правильно: приведите типы явно или измените схему
JOIN table2 t2 ON t1.int_column = t2.int_column::INTEGER;ON соединяются через AND / ORBETWEEN, >, < и другие операторы сравненияCASE в условии JOIN позволяет динамически выбирать ключ соединенияOR в JOIN может быть медленным — рассмотрите альтернативу с UNIONВопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.