Подзапросы SQL, (в основном) полное руководство

Углубленный обзор наиболее распространенных подзапросов и проблем

Основы

Подзапрос — это запрос, вложенный в предложение SELECT, FROM или WHERE основного запроса. Подзапрос заключен в круглые скобки, чтобы отделить его от основного запроса.

В приведенном ниже примере все пользователи, чей возраст больше среднего, выбираются с помощью подзапроса в предложении WHERE.

SELECT first_name,
       last_name,
       age
FROM   users
WHERE  age > (SELECT Avg(age)
              FROM   users)

Подзапрос появляется справа от сравнения в предложении WHERE. Этот подзапрос вернул одно значение (скалярное значение) для среднего значения. Если мы заменим фактическое среднее значение подзапросом, запрос будет работать точно так же:

SELECT first_name,
       last_name,
       age
FROM   users
WHERE  age > 31

Глубокое погружение

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

Итак, давайте начнем сверху, с SELECT.

Использование подзапросов в SELECT

Подзапросы в предложениях select должны возвращать только одну строку и один столбец; в противном случае возникает ошибка. По этой причине подзапросы в select часто используются для получения агрегированных значений без группировки строк.

Рассмотрим этот пример:

SELECT first_name,
       last_name,
       age,
       (SELECT Avg(age)
        FROM   users) as avg_age
FROM   users

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

Кроме того, подзапрос в предложении SELECT может обращаться к столбцам таблицы из внешнего запроса. Когда подзапрос ссылается на столбцы из внешней таблицы запросов, он называется коррелированным подзапросом. Вот пример коррелированного подзапроса в предложении SELECT.

SELECT first_name,
       last_name,
       (SELECT verification_status
        FROM   verified_users
        WHERE  users.id = verified_users.user_id)
FROM   users

В коррелированном подзапросе запрос выполняется построчно. Механизм SQL сначала выполняет внешний запрос, а затем внутренний запрос. Порядок выполнения позволяет внутреннему запросу ссылаться на значения столбцов из внешнего запроса.

Когда внутренний запрос не ссылается на столбцы внешнего запроса (другими словами, если это не коррелированный подзапрос), запрос не выполняется построчно. Вместо этого механизм SQL сначала выполняет внутренний запрос, а затем внешний запрос.

*Обратите внимание, что если в приведенном выше примере Verified_users содержит повторяющиеся идентификаторы user_id, запрос завершится ошибкой.

Использование подзапросов в FROM

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

Давайте рассмотрим простую версию первого сценария.

SELECT first_name,
       last_name
FROM   (SELECT first_name,
               last_name,
               state
        FROM   users
        WHERE  state = 'Kansas') kansas

Обратите внимание, что в этом примере подзапросу присвоен псевдоним (kansas). Это связано с тем, что все подзапросы в предложениях FROM должны иметь псевдоним.

А вот пример подзапроса, используемого в соединении. Здесь снова подзапрос должен иметь псевдоним.

SELECT first_name,
       last_name,
       verification_status
FROM   users
       INNER JOIN (SELECT verification_status,
                          user_id
                   FROM   verified_users
                   WHERE  verification_status = 'true') v
               ON users.id = v.user_id

Использование подзапросов в WHERE

В этом разделе рассказывается, как использовать подзапрос в предложениях WHERE. Эти запросы бывают двух типов: однострочные и многострочные.

Однострочные подзапросы

В базовом примере в начале этой статьи мы использовали подзапрос в предложении WHERE, который возвращал одно значение.

SELECT first_name,
       last_name,
       age
FROM   users
WHERE  age > (SELECT Avg(age)
              FROM   users)

Вот гораздо более сложный пример, где подзапрос сам по себе является сложным запросом, используемым для определения самой последней выплаты бонуса сотруднику, а затем выбирает все выплаты бонуса, которые выше.

SELECT user_id,
       bonus_amount
FROM   bonus_table
WHERE  bonus_amount > (SELECT bonus_amount
                FROM   (SELECT user_id,
                               bonus_amount,
                               Row_number()
                                 OVER(
                                   partition BY user_id
                                   ORDER BY date DESC) AS last_bonus
                        FROM   bonus_table
                        WHERE  user_id = 123) lb
                WHERE  last_bonus = 1)

Пример демонстрирует, что подзапросы могут быть такими же сложными и надежными, как и обычные запросы (и включать свои собственные подзапросы!). Когда вам нужно отфильтровать данные на основе одного значения, возвращаемого сложным запросом, обычно предпочтительным методом являются подзапросы.

Кроме того, это называется однострочным подзапросом.

Однострочные подзапросы при корреляции могут аппроксимировать внутренние соединения. Например, если бы у нас была таблица пользователей, содержащая всех пользователей, и таблица Verified_users, содержащая только пользователей, чья электронная почта проверена, следующее могло бы имитировать внутреннее соединение между ними.

SELECT first_name,
       last_name
FROM   users
WHERE  users.id = (SELECT user_id
                         FROM   verified_users
                         WHERE  users.id = user_id)

*Обратите внимание, что корреляция активируется тем, что внутренний запрос ссылается на столбец внешнего запроса.

Вот еще один пример коррелированного однострочного подзапроса.

SELECT first_name,
       last_name,
       state,
       age
FROM   user_states AS outer_states
WHERE  age = (SELECT Min(age)
              FROM   user_states AS inner_states
              WHERE  inner_states.state = outer_states.state
              GROUP  BY inner_states.state)

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

*Обратите внимание, что для выполнения этого запроса требуются псевдонимы.

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

Подзапрос, используемый в предложении WHERE и возвращающий несколько строк, называется подзапросом с несколькими строками. Вы должны использовать многострочные подзапросы в сочетании с оператором сравнения наборов (IN, ALL, ANY). Кроме того, как и подзапрос с одной строкой, подзапрос с несколькими строками должен возвращать только один столбец.

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

Использование оператора IN

Оператор IN определяет, существует ли значение в списке значений столбца. В приведенном ниже примере мы возвращаем список всех пользователей, чей возраст отображается в списке возрастов для всех пользователей, живущих в Огайо.

SELECT first_name,
       last_name,
       age
FROM   user_states
WHERE  age IN (SELECT age
               FROM   user_states
               WHERE  state = 'Ohio')

Использование оператора ALL

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

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

SELECT first_name,
       last_name,
       age
FROM   user_states
WHERE  age > ALL (SELECT age
               FROM   user_states
               WHERE  state = 'Kansas')

Использование оператора ANY

Оператор ANY определяет, верны ли хотя бы один раз сравнения между значением одной строки и значением каждой строки результирующего набора. Если сравнение верно хотя бы один раз, включается строка, связанная с левой частью сравнения.

В приведенном ниже примере мы возвращаем список пользователей, чей возраст превышает любой из максимальных возрастов пользователей по штатам.

SELECT first_name,
       last_name,
       state,
       age
FROM   user_states
WHERE  age > ANY (SELECT Max(age)
                  FROM   user_states
                  GROUP  BY state)

Подведение итогов того, что мы узнали

Подзапросы — одна из наиболее запутанных тем SQL. Если вы чувствуете себя потерянным, запомните основные моменты:

1. Все подзапросы должны быть заключены в круглые скобки 🎯
2. Подзапросы, используемые в предложениях SELECT, должны возвращать одно значение 🎯
3. Подзапросы, используемые в предложениях FROM, должны иметь псевдоним 🎯
4. Если подзапрос в предложении WHERE возвращает несколько строк, он должен использовать оператор сравнения наборов (IN, ALL, ANY) 🎯
5. Подзапрос, который ссылается на внешний столбец запроса, коррелируется 🎯

Если вы хотите прочитать несколько различных объяснений подзапросов, попробуйте следующие статьи:

• Ресурсы W3 — что такое подзапрос
• Подзапросы с несколькими строками ресурсов W3
• Коррелированные подзапросы SQL Server
• Режим — обзор подзапросов с практическими задачами

Спасибо за прочтение. Я надеюсь, что вы нашли эту статью полезной. Если вы это сделали, обязательно подпишитесь на меня, чтобы узнать больше о SQL!