SQL для лабораторных: JOIN, GROUP BY и ошибки в запросах

INNER и LEFT JOIN, откуда в выборке дубли, GROUP BY и столбец вне агрегата, WHERE или HAVING, NULL в сравнениях и COUNT, порядок выполнения запроса.

Запрос выполнился без ошибок, но в группе вдруг четыре студента вместо двух, а один студент пропал совсем. В лабораторных по базам данных так ломаются почти всегда одни и те же места: соединение таблиц, группировка и NULL. Разберём их на маленькой учебной базе. Все запросы ниже выполнены в SQLite 3.51, тексты ошибок PostgreSQL получены на PostgreSQL 17.

Учебная база: студенты, курсы, записи

CREATE TABLE students (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    group_name TEXT NOT NULL
);

CREATE TABLE courses (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id  INTEGER REFERENCES courses(id),
    grade      INTEGER,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO students VALUES
    (1, 'Анна',  'ИВТ-21'),
    (2, 'Борис', 'ИВТ-21'),
    (3, 'Вера',  'ИВТ-22'),
    (4, 'Глеб',  'ИВТ-22');

INSERT INTO courses VALUES
    (1, 'Базы данных'),
    (2, 'Python'),
    (3, 'Сети');

INSERT INTO enrollments VALUES
    (1, 1, 5),
    (1, 2, 4),
    (2, 1, 3),
    (2, 2, NULL),
    (3, 1, 4);

В данных специально есть неудобные случаи: Глеб не записан ни на один курс, на курс «Сети» никто не записался, а у Бориса по Python ещё нет оценки. Сохраните скрипт в schema.sql и создайте базу: sqlite3 lab.db < schema.sql. В консоли sqlite3 команда .mode markdown выводит результат таблицей, как ниже.

INNER JOIN: только пары с совпадением

SELECT s.name, c.title, e.grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id
ORDER BY s.id, c.id;
name title grade
Анна Базы данных 5
Анна Python 4
Борис Базы данных 3
Борис Python NULL
Вера Базы данных 4

JOIN (то же, что INNER JOIN) оставляет только строки, для которых нашлась пара. Глеба в результате нет: у него нет записей в enrollments. Если в задании сказано «все студенты», такой запрос ошибочен, хотя и выполняется.

ORDER BY в конце не для красоты. Без него порядок строк не гарантирован и может меняться от запуска к запуску.

LEFT JOIN: все строки левой таблицы

SELECT s.name, c.title, e.grade
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
LEFT JOIN courses c ON c.id = e.course_id
ORDER BY s.id, c.id;
name title grade
Анна Базы данных 5
Анна Python 4
Борис Базы данных 3
Борис Python NULL
Вера Базы данных 4
Глеб NULL NULL

LEFT JOIN сохраняет каждую строку левой таблицы. Где пары нет, столбцы правой таблицы заполняются NULL. На этом построен частый вопрос лабораторных «кто не записан ни на один курс»:

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

Результат: одна строка, Глеб.

Ловушка: условие на правую таблицу в WHERE

Нужны все студенты и их оценка по курсу 1, пустая у тех, кто курс не берёт.

SELECT s.name, e.grade
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.course_id = 1
ORDER BY s.id;

Глеба снова нет. WHERE проверяет строки после соединения, а у Глеба e.course_id равно NULL, и условие для него не выполняется. LEFT JOIN фактически превратился во внутренний. Условие на правую таблицу переносят в ON:

SELECT s.name, e.grade
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id AND e.course_id = 1
ORDER BY s.id;
name grade
Анна 5
Борис 3
Вера 4
Глеб NULL

Откуда берутся дубли

После соединения с enrollments каждый студент повторяется столько раз, сколько у него курсов.

Ряд бумажных фигурок, у одной из них три войлочные тени вместо одной

Считать студентов по такой выборке нельзя:

SELECT s.group_name,
       COUNT(*) AS students,
       ROUND(AVG(e.grade), 2) AS avg_grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
GROUP BY s.group_name;
group_name students avg_grade
ИВТ-21 4 4.0
ИВТ-22 1 4.0

В ИВТ-21 два студента, а не четыре: COUNT(*) посчитал записи на курсы. В ИВТ-22 потерялся Глеб из-за внутреннего соединения. Исправление:

SELECT s.group_name,
       COUNT(DISTINCT s.id) AS students,
       ROUND(AVG(e.grade), 2) AS avg_grade
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.group_name;

Теперь в каждой группе по два студента, средний балл тот же, 4.0.

Второй источник дублей: соединение без условия. SQLite выполнит FROM students s JOIN courses c без ON и вернёт все сочетания, 4 × 3 = 12 строк. PostgreSQL такой запрос не примет: ERROR: syntax error at or near ";".

Простая проверка перед группировкой: выполните запрос без GROUP BY и посмотрите, сколько строк и почему.

GROUP BY и столбец вне агрегата

SELECT s.group_name, s.name, AVG(e.grade) AS avg_grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
GROUP BY s.group_name;

SQLite выполнит запрос и покажет в строке ИВТ-21 имя «Анна». Почему её, а не Бориса? В группе два студента, и SQLite берёт имя из какой-то одной строки группы. Такой столбец в документации SQLite называется «голым» (bare column), и его значение обычно не определено. PostgreSQL отказывается:

ERROR:  column "s.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT s.group_name, s.name, AVG(e.grade) AS avg_grade
                             ^

MySQL по умолчанию тоже отклоняет такие запросы с ошибкой ERROR 1055, в которой упомянут режим only_full_group_by (документация MySQL).

Правило: каждый столбец в SELECT либо перечислен в GROUP BY, либо стоит внутри агрегатной функции (COUNT, SUM, AVG, MIN, MAX). Нужен средний балл каждого студента, значит, группируйте по студенту:

SELECT s.name, AVG(e.grade) AS avg_grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;
name avg_grade
Анна 4.5
Борис 3.0
Вера 4.0

s.id в группировке нужен на случай тёзок: две разные Анны не сольются в одну строку.

WHERE или HAVING

WHERE отбирает строки до группировки, HAVING отбирает группы после. Агрегат в WHERE написать нельзя: групп ещё нет.

SELECT s.name, AVG(e.grade) AS avg_grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
WHERE AVG(e.grade) >= 4
GROUP BY s.id, s.name;
SQLite:      misuse of aggregate: AVG()
PostgreSQL:  ERROR:  aggregate functions are not allowed in WHERE

Условие на среднее переносим в HAVING после GROUP BY:

SELECT s.name, AVG(e.grade) AS avg_grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name
HAVING AVG(e.grade) >= 4;

Остаются Анна (4.5) и Вера (4.0).

Оба условия часто нужны вместе. «Студенты группы ИВТ-21, у которых не меньше двух курсов»:

SELECT s.name, COUNT(*) AS courses
FROM students s
JOIN enrollments e ON e.student_id = s.id
WHERE s.group_name = 'ИВТ-21'
GROUP BY s.id, s.name
HAVING COUNT(*) >= 2;

Результат: Анна и Борис, по 2 курса. Условие на обычный столбец ставьте в WHERE: так в группировку попадает меньше строк.

NULL: не равен ничему, даже себе

NULL означает «значение неизвестно». Сравнение с ним через =, <>, < или > тоже даёт NULL, то есть «неизвестно», и строка не проходит фильтр.

  • WHERE grade = NULL не вернёт ни одной строки. Правильно WHERE grade IS NULL, это запись Бориса по Python.
  • WHERE grade <> 5 вернёт три строки, а не четыре: строка с NULL тоже выпадет. Если она нужна, добавьте OR grade IS NULL.
  • SELECT NULL = NULL даёт NULL. Арифметика с NULL тоже: 1 + NULL равно NULL.

С агрегатами NULL ведёт себя по-своему:

SELECT COUNT(*) AS all_rows,
       COUNT(grade) AS graded,
       AVG(grade) AS avg_grade,
       AVG(COALESCE(grade, 0)) AS avg_with_zero
FROM enrollments;
all_rows graded avg_grade avg_with_zero
5 4 4.0 3.2

COUNT(*) считает строки, COUNT(grade) только непустые значения. AVG пропускает NULL: средний балл 4.0 посчитан по четырём оценкам. Если в задании отсутствие оценки считается нулём, это нужно сказать явно через COALESCE.

Та же разница важна после LEFT JOIN. Посчитаем курсы каждого студента двумя способами:

SELECT s.name, COUNT(*) AS wrong, COUNT(e.course_id) AS courses
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;
name wrong courses
Анна 2 2
Борис 2 2
Вера 1 1
Глеб 1 0

У Глеба одна строка из NULL, и COUNT(*) её посчитал. Считайте по столбцу правой таблицы.

В каком порядке выполняется запрос

Пишем запрос в одном порядке, а логически он выполняется в другом. По документации Microsoft порядок такой:

  1. FROM и JOIN: собрать строки из таблиц.
  2. WHERE: отфильтровать строки.
  3. GROUP BY: разбить на группы.
  4. HAVING: отфильтровать группы.
  5. SELECT: посчитать выражения и псевдонимы.
  6. DISTINCT: убрать повторы.
  7. ORDER BY: отсортировать.
  8. LIMIT или TOP: обрезать.

Сервер может выполнять запрос физически иначе, но результат будет таким, как если бы шаги шли в этом порядке. Отсюда две ошибки. Агрегат нельзя написать в WHERE, потому что групп на шаге 2 ещё нет. Псевдоним из SELECT не виден в WHERE: в PostgreSQL запрос с e.grade * 20 AS points ... WHERE points > 70 падает с ERROR: column "points" does not exist. В ORDER BY тот же псевдоним работает. SQLite псевдоним в WHERE пропускает, но на это лучше не рассчитывать, если лабораторную проверяют в другой СУБД.

Как проверить запрос перед отправкой

  • Выполните часть с FROM и JOIN без группировки и посчитайте строки.
  • Проверьте вручную одну строку результата, например одного студента.
  • Добавьте в тестовые данные неудобные случаи: запись без пары, пустое значение, тёзок.
  • Если в задании указана СУБД, проверяйте именно в ней: SQLite выполняет запросы, которые PostgreSQL отклоняет.

Как с этим помогает Tilpi

Приложите к сообщению методичку или условие лабораторной и вставьте свой запрос: чат задания разберёт его по шагам, объяснит, откуда взялись лишние строки, и даст ссылку на страницу методички, откуда взято правило. Чат по своим файлам бесплатный для подтверждённой почты, действуют лимиты на день и месяц.

ИИ для учёбы. По вашим лекциям.

Пришлите в чат лекцию, задачу или свой текст. Тилпи объяснит тему со ссылкой на страницу, разберёт решение по шагам и проверит оформление.

Разобрать запрос в Tilpi