Шаг 10 - Связанные подзапросы

Связанные подзапросы в SQLite

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

SELECT *
FROM Customers C
WHERE '1999-10-03' IN (
    SELECT odate
    FROM Orders O
    WHERE O.cnum = C.cnum
)

Результат:

Как это работает?

В приведенном выше запросе C и O - это псевдонимы таблиц. Так как значение в поле cnum внешнего запроса меняется, внутренний запрос выполняется отдельно для каждой строки внешнего запроса. Строка внешнего запроса, для которой выполняется внутренний запрос, называется строкой-кандидатом.

Процедура оценки связанного запроса:

Выбрать строку из таблицы внешнего запроса (текущая строка-кандидат)
Сохранить значения из этой строки во временном буфере
Выполнить подзапрос, используя значения строки-кандидата
Вычислить условие внешнего запроса на основе результатов подзапроса
Определить, должна ли строка-кандидат попасть в результат
Повторить для всех строк

Альтернатива с JOIN

Можно было бы использовать объединение:

SELECT DISTINCT C.*
FROM Customers C
JOIN Orders O ON C.cnum = O.cnum
WHERE O.odate = '1999-10-03'

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

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

SELECT snum, sname
FROM Salespeople S
WHERE 1 < (
    SELECT COUNT(*)
    FROM Customers c
    WHERE c.snum = s.snum
)

Результат:

Сравнение таблицы с собой

Найдем заказы со значениями сумм выше среднего для их заказчиков:

SELECT *
FROM Orders O
WHERE amt > (
    SELECT AVG(amt)
    FROM Orders O1
    WHERE O1.cnum = O.cnum
)

Результат:

Заключение

Вы освоили важную концепцию SQL - связанные подзапросы. Далее вы познакомитесь со специальными операторами, которые используют подзапросы как аргументы: EXISTS, ANY, ALL.