Оператор SELECT. Часть 2

Продвинутые конструкции SELECT: подзапросы, ограничения, типы и NULL

Вложенные подзапросы в условии WHERE

Условие фильтрации в операторе WHERE может содержать другой запрос — подзапрос. Например, чтобы найти всех самых старших студентов, вместо подстановки конкретного числа в Age = 18 можно написать:

sql
SELECT FirstName, LastName
FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);

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

Возможен вариант, когда само условие целиком выражено подзапросом без явного указания столбца. Например, нужно получить имена и фамилии студентов, чей средний балл превышает 3.5. Средний балл отсутствует в таблице Students, он вычисляется по связанной таблице StudentResults. Запрос можно построить так:

sql
SELECT FirstName, LastName
FROM Students
WHERE (SELECT AVG(Mark)
FROM StudentResults
WHERE StudentResults.StudentID = Students.StudentID) > 3.5;

Фильтрация выполняется по агрегированному значению из другой таблицы. Глубина вложенности подзапросов может быть любой.

Подзапросы в разделе FROM (виртуальные таблицы)

Подзапрос может располагаться на месте таблицы-источника во FROM. Такой подход позволяет не просто фильтровать, но и включать вычисляемые столбцы в итоговую выборку.

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

sql
SELECT st.FirstName, st.LastName, vt.SMark
FROM Students AS st
JOIN (SELECT StudentID AS SID, AVG(Mark) AS SMark
FROM StudentResults
GROUP BY StudentID) AS vt
ON st.StudentID = vt.SID
WHERE vt.SMark > 3.5;

Здесь (SELECT …) AS vt — это производная (виртуальная) таблица, которой мы присвоили псевдоним vt. Она содержит средний балл SMark, и мы одновременно фильтруем по нему и выводим в финальном наборе. В предыдущем подходе с подзапросом в WHERE вывести SMark было невозможно, потому что запрос обращался только к Students.

Таким образом, выбор между WHERE и FROM определяется потребностью: нужен только фильтр — используем WHERE, нужно и значение — формируем виртуальную таблицу.

Ограничение количества возвращаемых строк

Для управления объёмом выборки используются операторы, лимитирующие число строк:
• TOP (SQL Server) – указывает, сколько первых строк вернуть. SELECT TOP 6 * FROM Students возвращает не более шести записей.
• FIRST … SKIP – в стандарте SQL позволяют задать количество строк и смещение. SELECT FIRST 10 SKIP 2 * FROM Students пропускает первые две строки и возвращает следующие десять.
• ROWS … TO – прямое указание диапазона номеров строк. ROWS 2 TO 3 возвращает вторую и третью строки.

Конкретный синтаксис зависит от реализации СУБД, но логика остаётся общей: ограничить выдачу и/или задать смещение.

Преобразование типов данных: функция CAST

Агрегатные функции, например AVG, при работе с целочисленными столбцами могут давать неточный результат из-за целочисленного деления. Если столбец Mark имеет тип INTEGER, то AVG(Mark) вернёт целое число: для оценок 1 и 2 среднее станет равным 2, а не 1.5. Чтобы получить истинное среднее, необходимо преобразовать значения к типу с плавающей точкой.

Функция CAST выполняет явное приведение типа:

sql
SELECT StudentID, AVG(CAST(Mark AS FLOAT)) AS AvgMark
FROM StudentResults
GROUP BY StudentID;

Синтаксис: CAST(выражение AS целевой_тип). После приведения к FLOAT деление становится вещественным, и AVG вернёт 1.5.

Функции для работы с датой и временем

Стандарт SQL требует наличия встроенных функций для манипуляций с датами, чтобы избавить разработчика от ручного разбора строк. Примеры для SQL Server (в других СУБД могут быть аналоги):
• Получение текущих даты и времени: SYSDATETIME() возвращает текущий момент на сервере.
• Извлечение компонентов:
o MONTH(дата) – месяц (число);
o YEAR(дата) – год;
o DATEPART(часть, дата) – универсальная функция: DATEPART(hour, SYSDATETIME()) извлекает часы.
• Разница между датами: DATEDIFF(year, '2015-10-21', SYSDATETIME()) вычисляет количество полных лет между двумя датами. Обратите внимание на формат строки: в некоторых региональных настройках сначала идёт месяц, потом день.

Эти функции позволяют легко решать задачи вроде вычисления возраста по дате рождения.

Особенности работы с NULL

NULL обозначает отсутствие значения, это не ноль и не пустая строка. Работа с NULL подчиняется строгим правилам, влияющим на все операции.

Поведение в выражениях

• Любая арифметическая операция или сравнение с участием NULL даёт NULL: 1 + NULL = NULL, 1 = NULL → NULL, NULL = NULL → NULL.
• Логические операторы используют сокращённое вычисление:
o NULL OR TRUE → TRUE (достаточно одной истины).
o NULL AND FALSE → FALSE (достаточно одной лжи).
o NULL OR FALSE → NULL.
o NULL AND TRUE → NULL.
• Проверка NOT NULL также возвращает NULL.

Влияние на агрегатные функции

• SUM, AVG, MAX, MIN игнорируют строки с NULL в соответствующем столбце — как будто этих строк нет.
• COUNT(*) учитывает строку целиком, поэтому она будет посчитана даже при наличии NULL в каких-то полях.
• COUNT(столбец) не засчитывает строки, где этот столбец равен NULL.

Корректные проверки с NULL

Обычные операторы сравнения =, != всегда возвращают NULL, если один из операндов NULL. Это может привести к неожиданным логическим ошибкам. Например, ветвление:

sql
IF A != B
результат = 'Not Equal'
ELSE
результат = 'Equal'

Если A = 1, а B = NULL, то A != B вернёт NULL, условие не сработает как истина, и выполнится ELSE, выдав Equal, что неверно.

Для безопасного сравнения неравенства с учётом NULL применяется конструкция IS DISTINCT FROM:

sql
IF A IS DISTINCT FROM B
результат = 'Not Equal'
ELSE
результат = 'Equal'

Здесь 1 IS DISTINCT FROM NULL даст TRUE, а NULL IS DISTINCT FROM NULL — FALSE.

Другие полезные конструкции:
• IS NULL / IS NOT NULL — прямая проверка на NULL.
• COALESCE(выражение1, выражение2, …) — возвращает первый не-NULL аргумент. Удобна для замены NULL значением по умолчанию: COALESCE(MiddleName, '—').

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

Краткие итоги

Центральной темой является расширение арсенала разработчика при построении запросов SELECT — от использования вложенных запросов до надёжной обработки неопределённых значений. Изложение раскрывает два фундаментальных способа интеграции агрегированных данных: помещение подзапроса в условие WHERE, позволяющее фильтровать строки по вычисляемому скаляру, и размещение подзапроса во FROM в роли виртуальной таблицы, благодаря которому вычисляемый показатель становится частью результирующего набора и доступен для дальнейшего соединения и вывода. Практическая ценность этого разграничения очевидна: когда требуется лишь отсеять записи по среднему баллу, достаточно подзапроса в WHERE; когда же необходимо показать и сам балл, единственным выходом остаётся формирование производной таблицы.

Параллельно рассматриваются средства управления размером ответа: синтаксические вариации TOP, FIRST с пропуском строк и нумерация ROWS. Хотя конкретные формы различаются от СУБД к СУБД, стоящая за ними задача — выдача первых N записей или записей со смещением — едина.

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

Кульминацией становится анализ NULL. Раскрывается его коварство: любое арифметическое или сравнительное взаимодействие с NULL даёт NULL, что при неаккуратном использовании приводит к логически противоречивым исходам в условиях. Правила сокращённого вычисления OR и AND смягчают картину лишь частично. Исключение NULL из агрегатных функций (кроме COUNT(*)) объясняет, почему результаты одних и тех же запросов могут не совпадать с интуитивными ожиданиями. Выходом служат инструменты безопасного сравнения — IS DISTINCT FROM, явные проверки IS NULL и подстановки через COALESCE. Совокупность этих приёмов формирует культуру защитного программирования на SQL, где неучтённый NULL перестаёт быть источником скрытых дефектов и превращается в управляемый элемент модели данных.