Занятие 28. Summary session 7

📁 Блок: SQL / MySQL / Подзапросы и CTE ⏱️ Время изучения: ~60 мин 🎯 Сложность: Повторение

#in #where #select #from #as #join #null

⚡ Кратко: команды

WHERE column IN (SELECT ...)
FROM (SELECT ...) AS alias
WITH name AS (SELECT ...) SELECT ...
LEFT JOIN ... IS NULL для поиска отсутствующих связей.

🔖 Справочник операторов

Скалярный подзапрос

SELECT *
FROM order_details
WHERE unit_price > (
    SELECT AVG(unit_price) FROM order_details
);

Многострочный подзапрос с IN

SELECT *
FROM orders
WHERE employee_id IN (
    SELECT id FROM employees WHERE job_title = 'Sales Representative'
);

Подзапрос в FROM

SELECT p.product_name, ps.total_orders
FROM (
    SELECT product_id, COUNT(*) AS total_orders
    FROM order_details
    GROUP BY product_id
) AS ps
JOIN products AS p ON ps.product_id = p.id;

CTE

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price);

Поиск отсутствующих связей через LEFT JOIN

SELECT e.first_name, e.last_name
FROM employees AS e
LEFT JOIN employee_privileges AS ep ON e.id = ep.employee_id
WHERE ep.employee_id IS NULL;

🔗 Полный справочник

Более полный каталог SQL-операторов курса — в справочнике SQL.