Сложные условия JOIN

Условия соединения не ограничиваются простым равенством колонок. В этой теме изучим множественные условия, составные ключи, диапазонные соединения и использование CASE в JOIN.

#Множественные условия в ON

Можно указывать несколько условий в 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;

Оба условия должны выполняться для соединения строк.

#Составные ключи (Composite Keys)

Когда связь между таблицами определяется несколькими колонками одновременно.

#Пример данных

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)  -- Составной первичный ключ
);

#JOIN по составному ключу

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

Порядок колонок в индексе важен для производительности.

#Диапазонные соединения (Range JOIN)

Соединение по условию диапазона, а не точного совпадения.

#Пример: налоговые диапазоны

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 означает "без верхнего предела"

#JOIN с BETWEEN

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;

#Обработка NULL в диапазоне

-- Для последнего диапазона (без верхнего предела)
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;

#Неравенства в JOIN

Условия с операторами >, <, >=, <=, !=.

#Пример: поиск более дорогих товаров

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;

#JOIN с несколькими таблицами и сложными условиями

Комбинирование разных типов условий в одном запросе.

#Пример: комплексный запрос

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 в условии JOIN

Использование 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 не найден

#COALESCE и NULLIF в условиях JOIN

#Пример: соединение с fallback

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

#Пример: игнорирование NULL при соединении

-- NULLIF предотвращает соединение строк, где обе колонки NULL
SELECT *
FROM table1 t1
JOIN table2 t2 ON NULLIF(t1.col, t2.col) IS DISTINCT FROM NULL;

#OR в условии JOIN

Использование 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 может значительно замедлить запрос, так как оптимизатор не всегда может использовать индексы эффективно.

#Производительность сложных JOIN

#1. Индексы для множественных условий

-- Для условия ON a.id = b.a_id AND a.type = b.type
CREATE INDEX idx_b_a_id_type ON b(a_id, type);

Порядок колонок в индексе должен соответствовать порядку в условии.

#2. Избегайте функций в условии JOIN

-- Плохо: функция предотвращает использование индекса
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);

#3. Диапазонные JOIN могут быть медленными

-- Медленно: требует сканирования диапазона
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;

#4. OR в JOIN — используйте UNION как альтернативу

-- Медленно: 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;

#Распространённые ошибки

#1. Дублирование из-за множественных совпадений

-- Если в 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

#2. Неправильный порядок условий с OR

-- Может вернуть неожиданные результаты
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;

#3. Сравнение разных типов данных

-- Неявное приведение типов замедляет запрос
JOIN table2 t2 ON t1.int_column = t2.varchar_column;

-- Правильно: приведите типы явно или измените схему
JOIN table2 t2 ON t1.int_column = t2.int_column::INTEGER;