SQL-запросы: примеры и разбор

Две таблицы, на которых всё показано#

Все примеры работают с парой связанных таблиц. Первая — сотрудники, вторая —
отделы; связывает их числовой столбец dept_id.

employees
 id | name  | dept_id | salary
----+-------+---------+--------
  1 | Анна  |       1 |  70000
  2 | Борис |       1 |  90000
  3 | Вера  |       2 | 120000
  4 | Глеб  |       2 | 150000
  5 | Дина  |  (NULL) |  60000

departments
 id | title
----+------------
  1 | Продажи
  2 | Разработка
  3 | Логистика

Обрати внимание на две особенности: у Дины отдел не заполнен, а отдел
«Логистика» пока без людей. Именно они покажут разницу между видами
соединения.

Синтаксис в примерах стандартный и одинаково работает в PostgreSQL, SQLite и
MySQL. Различия начинаются дальше — в типах данных, функциях работы с датами и
способе ограничить выдачу.

SELECT и WHERE: отбор строк#

SELECT перечисляет нужные столбцы, FROM называет таблицу, WHERE
отсеивает строки по условию.

SELECT name, salary
FROM employees
WHERE salary >= 90000;

 name  | salary
-------+--------
 Борис |  90000
 Вера  | 120000
 Глеб  | 150000

Условия складываются через AND, OR и скобки. Полезны три отдельные формы:
BETWEEN для диапазона, IN для списка значений, LIKE для шаблона строки,
где % заменяет любое число символов.

SELECT name FROM employees WHERE salary BETWEEN 70000 AND 120000;
SELECT name FROM employees WHERE dept_id IN (1, 2);
SELECT name FROM employees WHERE name LIKE 'В%';

Пустое значение сравнивать знаком равенства нельзя: dept_id = NULL не вернёт
ничего даже для Дины. Для пустоты есть отдельная проверка:

SELECT name FROM employees WHERE dept_id IS NULL;

 name
------
 Дина

ORDER BY и LIMIT: порядок и отсечение#

Порядок строк без явного указания не гарантирован — его задаёт ORDER BY,
а ASC и DESC выбирают направление.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

 name | salary
------+--------
 Глеб | 150000
 Вера | 120000

Сортировать можно по нескольким столбцам сразу: ORDER BY dept_id, salary DESC
сначала группирует по отделу, а внутри отдела ставит самых оплачиваемых
первыми. В PostgreSQL, SQLite и MySQL работает LIMIT, в SQL Server вместо
него пишут TOP, в старых Oracle — условие по ROWNUM.

GROUP BY и HAVING: считать по группам#

Агрегатные функции сворачивают множество строк в одно число: COUNT, SUM,
AVG, MIN, MAX. GROUP BY говорит, по какому признаку резать таблицу на
группы.

SELECT dept_id, COUNT(*) AS people, SUM(salary) AS fund
FROM employees
GROUP BY dept_id;

 dept_id | people |  fund
---------+--------+--------
       1 |      2 | 160000
       2 |      2 | 270000
  (NULL) |      1 |  60000

🔴 Порядок строк здесь показан такой, каким его отдаёт PostgreSQL. Без
ORDER BY порядок не гарантирован вообще: SQLite, например, поставит строку
с пустым отделом первой. Нужен определённый порядок — пиши ORDER BY
явно.

COUNT(*) считает строки, COUNT(dept_id) — только непустые значения
столбца. На этой таблице первое даст 5, второе — 4: строка Дины в счёт не
войдёт. Разницу между ними спрашивают на собеседованиях чаще любой другой
детали SQL.

WHERE отсеивает строки до группировки, HAVING — готовые группы после неё.

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 100000;

 dept_id |      avg_salary
---------+----------------------
       2 | 135000.0000000000000

Среднее — дробное число даже там, где деление вышло ровным: тип результата
AVG шире целого. Округляют уже при выводе — например, ROUND(AVG(salary)).

Отдел 1 со средней 80 000 в результат не попал, строка Дины — тоже. Порядок
частей запроса фиксирован: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. Поменять их местами база не позволит.

Четыре вида JOIN#

Соединение склеивает строки двух таблиц по условию. Отличаются виды тем, что
делать со строками, которым пара не нашлась.

INNER JOIN оставляет только совпавшие пары.

SELECT e.name, d.title
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

 name  |   title
-------+------------
 Анна  | Продажи
 Борис | Продажи
 Вера  | Разработка
 Глеб  | Разработка

Четыре строки: Дина выпала (её отдел пуст), «Логистика» выпала (в ней никого).

LEFT JOIN сохраняет все строки левой таблицы, подставляя NULL там, где
пары нет.

SELECT e.name, d.title
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

 name  |   title
-------+------------
 Анна  | Продажи
 Борис | Продажи
 Вера  | Разработка
 Глеб  | Разработка
 Дина  | (NULL)

Пять строк — все сотрудники на месте. Это самый ходовой вид соединения:
«покажи всех, и заодно то, что про них известно».

RIGHT JOIN делает зеркальное: сохраняет все строки правой таблицы.

SELECT e.name, d.title
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

  name  |   title
--------+------------
 Анна   | Продажи
 Борис  | Продажи
 Вера   | Разработка
 Глеб   | Разработка
 (NULL) | Логистика

Тоже пять строк, но лишней оказалась не Дина, а пустая «Логистика». Любой
RIGHT JOIN переписывается как LEFT JOIN перестановкой таблиц, поэтому на
практике его пишут редко — тем более что в старых сборках SQLite его нет вовсе.

FULL OUTER JOIN сохраняет обе стороны сразу: четыре совпавшие пары плюс
Дина без отдела плюс «Логистика» без людей — шесть строк. В MySQL этого вида
нет, его собирают объединением LEFT JOIN и RIGHT JOIN через UNION.

Если условие ON забыть, получится декартово произведение: каждая строка с
каждой, 5 × 3 = 15 строк. На таблицах в миллионы записей такой запрос
останавливает базу, поэтому условие соединения проверяют первым.

Подзапросы#

Подзапрос — обычный SELECT внутри другого запроса. Скалярный возвращает
одно значение и годится для сравнения:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Средняя по таблице — 98 000, поэтому результат такой:

 name | salary
------+--------
 Вера | 120000
 Глеб | 150000

Подзапрос-список подставляют в IN:

SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE title = 'Разработка');

 name
------
 Вера
 Глеб

Проверка существования пары делается через EXISTS — база останавливается на
первом найденном совпадении и не строит весь список:

SELECT d.title
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

Вернёт одну строку — «Логистика». А вот NOT IN с подзапросом, где может
попасться NULL, вернёт пустой результат, и это не ошибка базы, а правила
трёхзначной логики. Привычка «отрицание — через NOT EXISTS» избавляет от
целого класса ловушек.

Частые ошибки#

Столбец в SELECT не из GROUP BY. Если группируешь по dept_id,
показать заодно name нельзя: у группы из двух человек имя не одно. PostgreSQL
откажет прямо, MySQL в нестрогом режиме молча выдаст произвольное значение.

Сравнение с NULL. salary != 100000 не вернёт строки, где зарплата не
заполнена. Нужно дописывать OR salary IS NULL.

UPDATE и DELETE без WHERE. Выполняются по всей таблице. Правило:
сначала тот же фильтр прогнать SELECT, увидеть строки, и только потом менять
ключевое слово.

Проверка запросов на боевой базе. Подними учебную копию отдельно —
например, контейнером PostgreSQL, как описано в статье
что такое Docker. Там таблицы не жалко.

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

План по этапам

1. Поднять учебную базуСоздать две таблицы из примера и заполнить их пятью и тремя строками.
2. Отбор и сортировкаНаписать пять запросов с WHERE, BETWEEN, IN, LIKE и ORDER BY и сверить результат вручную.
3. ГруппировкиПосчитать COUNT, SUM и AVG по отделам, затем отсечь группы через HAVING.
4. Все четыре соединенияПрогнать INNER, LEFT, RIGHT и FULL на тех же таблицах и записать, сколько строк вернул каждый и почему.
5. ПодзапросыНайти сотрудников выше средней зарплаты и отделы без людей через NOT EXISTS.

Проверь себя

Источники

• Документация PostgreSQLПервоисточник по синтаксису SELECT, JOIN и агрегатам
  бесплатно
• Документация SQLiteБаза в одном файле — удобно тренироваться без установки сервера
  бесплатно
• StepikБесплатные курсы по SQL с автопроверкой запросов
  бесплатно

Было полезно?

Да Нет