Сможешь ли написать запрос JOIN в SQL

SQL JOIN запросы и их применение в Node.js разработке

Конечно, я абсолютно уверен в своих навыках написания SQL JOIN запросов. Это базовый навык для любого backend-разработчика.

Типы JOIN запросов

1. INNER JOIN — пересечение таблиц

SELECT 
  o.id, o.order_date, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = "completed";

2. LEFT JOIN — все записи из левой таблицы

SELECT 
  c.id, c.name,
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

3. RIGHT JOIN — все записи из правой таблицы

SELECT p.name, p.price, pc.category_name
FROM products p
RIGHT JOIN product_categories pc ON p.category_id = pc.id;

4. FULL OUTER JOIN — все записи из обеих таблиц

SELECT 
  COALESCE(legacy.order_id, new.order_id) as order_id,
  legacy.created_at as legacy_date,
  new.created_at as new_date
FROM legacy_orders legacy
FULL OUTER JOIN new_orders new ON legacy.order_id = new.order_id
WHERE legacy.order_id IS NULL OR new.order_id IS NULL;

5. CROSS JOIN — декартово произведение

SELECT p.name, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c;

6. Self JOIN — объединение таблицы с собой

SELECT 
  e.name as employee,
  m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Сложные JOIN запросы

Multiple JOINs (более 2 таблиц)

SELECT 
  o.id, o.order_date, c.name as customer_name,
  p.name as product_name, p.price,
  d.status as delivery_status, d.tracking_number
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
LEFT JOIN deliveries d ON o.id = d.order_id
WHERE o.created_at >= NOW() - INTERVAL "30 days";

JOIN с GROUP BY и HAVING

SELECT 
  c.id, c.name, c.email,
  COUNT(DISTINCT o.id) as order_count,
  SUM(o.total) as total_amount,
  AVG(o.total) as avg_order_value
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL "90 days"
GROUP BY c.id, c.name, c.email
HAVING SUM(o.total) > 1000
ORDER BY total_amount DESC;

JOIN с подзапросами

SELECT 
  c.id, c.name, latest.order_id, latest.total, latest.created_at
FROM customers c
INNER JOIN (
  SELECT 
    customer_id, id as order_id, total, created_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) as rn
  FROM orders
) latest ON c.id = latest.customer_id AND latest.rn = 1;

Оптимизация JOIN запросов

Использование индексов

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Избегание проблем

-- ❌ Плохо — может привести к декартову произведению
SELECT * FROM orders o, customers c, products p
WHERE o.customer_id = c.id AND ...;

-- ✅ Хорошо — явный JOIN
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

Node.js примеры

Knex.js

const orders = await db
  .select("o.id", "o.order_date", "c.name")
  .from("orders as o")
  .innerJoin("customers as c", "o.customer_id", "c.id")
  .where("o.status", "completed");

TypeORM

const orders = await getRepository(Order)
  .createQueryBuilder("o")
  .innerJoinAndSelect("o.customer", "c")
  .leftJoinAndSelect("o.items", "oi")
  .where("o.status = :status", { status: "completed" })
  .getMany();

Выводы

SQL JOIN запросы — фундаментальный навык backend-разработчика. Я полностью владею всеми типами JOIN и могу написать как простые, так и сложные запросы с множественными таблицами, подзапросами и агрегирующими функциями. Важно оптимизировать JOIN запросы для лучшей производительности.