Справочник SQL

Порядок выполнения

В тексте запрос начинается с SELECT, но PostgreSQL читает его иначе. Сначала собирается таблица строк, потом её режут и группируют, и только потом выбирают колонки.

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY

Поэтому в WHERE нельзя написать псевдоним колонки из SELECT: фильтрация уже прошла, когда список колонок ещё не построен. ORDER BY как раз видит и псевдонимы, и исходные имена.

В тексте операторы идут иначе: SELECT … FROM … JOIN … WHERE … GROUP BY … HAVING … ORDER BY. Список колонок пишете первым, а PostgreSQL до него добирается только после FROM, соединений и фильтров.

SELECT

SELECT задаёт колонки результата. Не «какие таблицы взять» — это FROM — а что показать из уже готовых строк.

Перечислите поля явно. В условии задачи почти всегда указаны имена колонок результата.

SELECT name, city
FROM customers
WHERE city = 'Казань'
ORDER BY name;

Псевдоним колонки пишут через AS. Если в условии просят employee, а в таблице поле name — без псевдонима проверка не примет результат.

SELECT name AS customer, city
FROM customers;

SELECT * возвращает все колонки таблицы. В тренажёре так почти никогда нельзя: лишняя колонка — это уже другой результат. Звёздочка удобна, чтобы глазами посмотреть данные, но в решение её не ставят.

DISTINCT убирает повторяющиеся строки целиком, не «уникальные значения одной колонки вперемешку с другими».

SELECT DISTINCT city
FROM customers
ORDER BY city;

FROM

FROM указывает таблицы, из которых берутся строки. Чаще всего одну, можно и несколько.

SELECT id, name
FROM products;

Короткое имя таблицы (алиас) пишут сразу после имени: FROM customers c. Дальше колонки берут как c.name. Без алиасов легко запутаться, когда таблиц больше одной.

В PostgreSQL порядок строк без ORDER BY не обещан. Даже SELECT * FROM customers завтра может прийти в другом порядке.

JOIN

JOIN склеивает строки двух таблиц по условию в ON. Обычно это равенство ключей: заказ знает customer_id, клиент — id.

INNER JOIN

Внутреннее соединение оставляет только пары, которые нашлись с обеих сторон. Клиент без заказов в результат не попадёт. Заказ без клиента в учебной базе тоже не появится: на customer_id стоит внешний ключ.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.name = 'Анна Смирнова'
ORDER BY o.id;

Слово INNER можно не писать: просто JOIN — это то же внутреннее соединение.

LEFT JOIN

Левое соединение берёт все строки левой таблицы. Если справа пары нет, колонки правой таблицы будут NULL. LEFT OUTER JOIN и LEFT JOIN — одно и то же.

У Кирилла Белова заказов нет. Внутренний JOIN вернёт пустой результат. LEFT JOIN вернёт Кирилла один раз, с пустым заказом.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.name = 'Кирилл Белов';

Клиенты без заказов — как раз такие строки: LEFT JOIN и затем WHERE o.id IS NULL. В учебной базе их двое: Павел Морозов и Кирилл Белов.

SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Если после LEFT JOIN фильтр по правой таблице стоит в WHERE (o.status = 'paid'), строки без пары пропадут: у них справа NULL, и соединение станет внутренним. Чтобы клиента без подходящего заказа оставить, такое условие пишут в ON.

Несколько соединений пишут цепочкой: заказ, позиция, товар, категория. Каждое следующее JOIN работает уже с тем, что получилось слева.

SELECT c.name, p.name AS product, oi.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 1
ORDER BY p.name;

RIGHT JOIN

Правое соединение — зеркало левого: берёт все строки правой таблицы. Если слева пары нет, колонки левой таблицы будут NULL. Слово OUTER можно не писать: RIGHT OUTER JOIN и RIGHT JOIN — одно и то же.

Тот же Кирилл без заказов, только таблицы переставлены: клиенты теперь справа, заказы слева.

SELECT c.name, o.id AS order_id, o.status
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
WHERE c.name = 'Кирилл Белов';

На практике чаще пишут LEFT JOIN и ставят «обязательную» таблицу слева, чем RIGHT JOIN с перестановкой.

FULL JOIN

Полное внешнее соединение оставляет строки и слева, и справа. Где пары нет — на противоположной стороне NULL. FULL OUTER JOIN и FULL JOIN — одно и то же.

В учебной базе у каждого заказа есть клиент, поэтому FULL JOIN клиентов с заказами совпадёт с LEFT JOIN: «лишних» заказов нет, а клиенты без заказов уже видны слева. Чтобы увидеть обе стороны, возьмём два коротких множества:

SELECT a.id AS left_id, b.id AS right_id
FROM (VALUES (1), (2), (3)) AS a(id)
FULL JOIN (VALUES (2), (3), (4)) AS b(id) ON a.id = b.id
ORDER BY COALESCE(a.id, b.id);

CROSS JOIN

Перекрёстное соединение — декартово произведение: каждая строка слева с каждой справа. Условия ON нет.

SELECT c.name, p.name AS product
FROM customers c
CROSS JOIN products p
WHERE c.name IN ('Кирилл Белов', 'Павел Морозов')
  AND p.name IN ('Шарф', 'Подушка')
ORDER BY c.name, p.name;

Без фильтра по именам это были бы все клиенты на все товары. Тот же смысл даёт FROM customers c, products p — старая запись без слова JOIN.

NATURAL JOIN и USING

NATURAL JOIN сам ищет одноимённые колонки и склеивает по ним, без ON. Если в обеих таблицах есть id, но это разные сущности, соединение получится не то.

USING (col) короче равенства одноимённых полей: JOIN orders o USING (customer_id) — только если колонка customer_id есть с обеих сторон. Явный ON обычно понятнее.

Таблица может соединяться сама с собой: два алиаса, как будто это две таблицы. Так смотрят, например, сотрудника и его руководителя.

WHERE

WHERE отфильтровывает строки до группировки. Остаются только те, для которых условие истинно. NULL в сравнении даёт не истину — строка пропадает, если не написать IS NULL / IS NOT NULL.

SELECT name, price, in_stock
FROM products
WHERE in_stock < 10
ORDER BY name;

Несколько условий связывают AND и OR. Скобки лучше писать явно: AND связывается теснее, чем OR.

SELECT name, city
FROM customers
WHERE city IN ('Казань', 'Самара')
ORDER BY name;

Диапазон дат удобно задавать полуинтервалом: order_date >= DATE '2024-07-01' AND order_date < DATE '2024-08-01' — весь июль, без сюрпризов со временем. BETWEEN тоже работает, если обе границы включительны и тип — дата без времени.

Агрегат в WHERE поставить нельзя: COUNT появляется только после GROUP BY. Фильтр по группе — это HAVING.

Сравнение status = NULL не находит пустые значения. Нужно status IS NULL.

GROUP BY

GROUP BY схлопывает строки в группы: одна строка результата на каждое уникальное сочетание ключей. Рядом пишут агрегаты: COUNT, SUM, AVG, MIN, MAX.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

В SELECT можно поставить ключ группы и агрегаты. Поле, которое не в GROUP BY и не внутри агрегата, PostgreSQL не пропустит.

COUNT(*) считает строки группы. COUNT(колонка) считает только не-NULL. После LEFT JOIN это разные числа: у клиента без заказов COUNT(*) даст 1 (строка клиента есть), а COUNT(o.id) даст 0.

SELECT c.name, COUNT(*) AS rows_n, COUNT(o.id) AS orders_n
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.name IN ('Анна Смирнова', 'Кирилл Белов')
GROUP BY c.name
ORDER BY c.name;

COUNT(DISTINCT product_id) — число разных товаров, а не число позиций. Один клиент с двумя одинаковыми строками и один клиент с двумя разными товарами — для COUNT(*) неотличимы, для COUNT(DISTINCT …) нет.

HAVING

HAVING фильтрует уже готовые группы. Это «WHERE после агрегата».

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY customers DESC, city;

Сначала можно сузить строки в WHERE, потом сгруппировать, потом отбросить группы в HAVING.

SELECT c.city, COUNT(*) AS paid_orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.city
HAVING COUNT(*) >= 2
ORDER BY paid_orders DESC, c.city;

Здесь WHERE оставляет только оплаченные заказы. HAVING оставляет города, у которых таких заказов не меньше двух. Если статус поставить в HAVING, запрос либо не соберётся, либо будет значить другое.

ORDER BY

ORDER BY — последний шаг. Без него порядок строк в PostgreSQL не определён: сегодня пришло как вставили, завтра — иначе. В задачах zedcode сортировка почти всегда прописана в условии, и эталон её проверяет.

SELECT name, price
FROM products
ORDER BY price DESC, name;

Несколько ключей читаются слева направо: сначала цена по убыванию, при равной цене — имя по возрастанию. По умолчанию направление ASC (по возрастанию). DESC пишут у того ключа, который нужно развернуть.

Можно сортировать по псевдониму из SELECT:

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC, city;

NULL в PostgreSQL при ASC уходит в конец, при DESC — в начало. Если в условии сказано «пустые последними», это уже явный NULLS LAST / NULLS FIRST.

Всё вместе

Типичный запрос магазина: соединить таблицы, отфильтровать строки, сгруппировать, отбросить слабые группы, отсортировать.

SELECT cat.name AS category, COUNT(*) AS products_n
FROM products p
JOIN categories cat ON cat.id = p.category_id
WHERE p.price >= 2000
GROUP BY cat.name
HAVING COUNT(*) >= 2
ORDER BY products_n DESC, category;

По шагам: FROM products, присоединить категорию, оставить товары не дешевле 2000, одна строка на категорию, оставить категории с хотя бы двумя такими товарами, в результате колонки category и products_n, затем сортировка.

Книги не проходят HAVING: дороже 2000 там только «Чистый код».

Дальше — подзапросы, CTE и EXISTS.