Что такое SQL-подзапросы

Что такое подзапросы в SQL

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

Подзапросы, или вложенные запросы, — это один из инструментов, предоставляемых этим языком. Они помогают проводить сложные операции более лаконично. Подзапросы включаются в основные (внешние) запросы и выполняются в первую очередь, а их результат используется в основном запросе. Они могут быть вложены в блоки с практически любыми SQL-операторами: SELECT, WHERE, FROM, HAVING и другими. Также они могут быть вложены друг в друга.

Зачем они нужны

Рассмотрим основные функции:

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

Синтаксис

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

SELECT column_name(s)  

FROM table_name  

WHERE column_name operator (  

    SELECT column_name  

    FROM table_name  

    WHERE condition  

);  

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

Ограничения

Чтобы работать с подзапросами, необходимо соблюдать некоторые правила, иначе их выполнение завершится ошибкой. Рассмотрим эти правила с помощью двух таблиц: employees с информацией о сотрудниках и salaries с более подробными данными о зарплатах.

• Если используется оператор сравнения, то результат подзапроса должен содержать одно значение (столбец):

-- Этот вариант ошибочный, так как возвращаются два столбца (salary и age)

SELECT employee_name 

FROM employees 

WHERE salary = (SELECT salary, age FROM salaries);

Исключения из этого правила: операторы IN в работе со списком значений и EXISTS в SELECT *.

Также подзапросы с операторами сравнения (исключая ANY и ALL) не могут содержать GROUP BY или HAVING. Это объясняется правилом выше: возвращено должно быть только одно значение. Пример кода с этой ошибкой:

SELECT employee_name 

FROM employees 

WHERE salary = (SELECT salary FROM salaries GROUP BY department_id);

• Сравниваемые типы данных должны быть совместимыми. Например, текст нельзя сравнивать с числами.

• Столбцы с типами данных ntext, text и image в подзапросах использовать нельзя.

• Если содержится оператор GROUP BY, то DISTINCT применять запрещено. Ошибочный код:

SELECT employee_name 

FROM employees 

WHERE salary IN (SELECT DISTINCT salary FROM salaries GROUP BY department_id);

• Если содержится оператор ORDER BY, то он должен использоваться с TOP.

• Представление (оператор VIEW), созданное с подзапросами, нельзя обновить, например:

CREATE VIEW active_employees AS 

SELECT employee_name, salary 

FROM employees 

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

-- Обновить не удастся

UPDATE active_employees 

SET salary = salary + 1000; 

Вложенные запросы с примерами

UPDATE

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

Допустим, есть таблица employees:

Также есть таблица с информацией об отделах и городах работы (departments):

Необходимо увеличить зарплату сотрудников IT-отдела на 15% — для этого можно написать следующий запрос:

UPDATE employees 

SET salary = salary * 1.15 

WHERE department_id = ( 

SELECT department_id 

FROM departments 

WHERE department_name = 'IT-отдел' 

);

Результатом подзапроса станет идентификатор IT-отдела (2), а внешний запрос обновит зарплаты всем сотрудникам, для которых этот отдел указан.

Итоговая таблица будет выглядеть так:

INSERT

Этот оператор применяется для вставки записей в таблицу. Подзапросы в INSERT удобно использовать для переноса информации из одной таблицы в другую на основании определенного условия. 

Рассмотрим это на примере таблицы с товарами интернет-магазина техники (products):

В таблицу top_discounted_products (она пустая и имеет те же столбцы, что products) нужно вставить информацию обо всех товарах, на которые действует скидка больше, чем средняя по всему ассортименту:

INSERT INTO top_discounted_products (product_id, product_name, price, discount) 

SELECT product_id, product_name, price, discount 

FROM products 

WHERE CAST(REPLACE(discount, '%', '') AS DECIMAL) > (

    SELECT AVG(CAST(REPLACE(discount, '%', '') AS DECIMAL)) 

    FROM products

);

В подзапросе значение в процентах преобразуется в числа, а затем вычисляется средняя скидка (5). Внешний же запрос выбирает только те строки, где скидка больше пяти (то есть результата вложенного запроса) и вставляет их в top_discounted_products:

Умная колонка в эту таблицу не попала, так как скидка должна быть строго больше 5%.

DELETE

Вложенные в DELETE запросы нужны, чтобы удалять записи в таблице по определенному условию.

Допустим, у автосервиса есть таблица (repairs), куда заносится информация о проведенных ремонтах:

Нужно удалить всю информацию о ремонтах, которые были проведены более 5 лет назад:

DELETE FROM repairs 

WHERE completion_date < ( 

SELECT DATE_SUB(CURDATE(), INTERVAL 5 YEAR) 

);

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

Результат:

SELECT

Подзапросы внутри SELECT позволяют выбирать данные, соответствующие какому-то условию.

Рассмотрим пример с таблицей tours, в которую занесена информация о доступных турах компании:

Необходимо получить города и сопоставленные им цены, значение которых выше среднего:

SELECT tour_id, destination, price 

FROM tours 

WHERE price > ( 

SELECT AVG(price) 

FROM tours 

);

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

Итоговая таблица:

Несколько уровней вложения

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

Рассмотрим три таблицы, входящие в базу данных института:

• students — информация о студентах:

• enrollments — информация о зачислении:

• courses — данные о факультативах:

Нужно получить имена и фамилии студентов, которые зачислены на факультативы, на которые также зачислены те студенты, что записаны на математику. Запрос получится таким:

/*

Основной запрос, который выбирает имена и фамилии студентов по результатам вложенного

*/

SELECT first_name, last_name 

FROM students 

WHERE student_id IN (

    /*

    Первый уровень вложенности — поиск обучающихся на тех же факультативах, куда зачислены студенты,

    записанные на математику (результат: 1, 3, 4, 5)

    */

    SELECT student_id 

    FROM enrollments 

    WHERE course_id IN (

        /*

        Второй уровень вложенности — поиск факультативов, посещаемых зачисленными на математику

        (результат: 101, 103, 104)

        */

        SELECT course_id 

        FROM enrollments 

        WHERE student_id IN (

            /*

            Третий уровень вложенности — поиск студентов, зачисленных на математику (результат: 1, 3, 4)

            */

            SELECT student_id 

            FROM enrollments 

            WHERE course_id = 101 

        )

    )

);

Результат будет выглядеть так: