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

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

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

.. code-block:: sql


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

Результат:

====== ==================== ========== ======= =====
CNUM CNAME CITY RATING SNUM
====== ==================== ========== ======= =====
2001 ТОО Рога и копыта Москва 100 1001
2002 AО Бендер и К Одесса 200 1003
2003 Фирма ХХХ Рязань 200 1002
2007 ОАО "ООО" ТОМСК 100 1004
2008 ОАО "Валют-транзит" Караганда 300 1007
====== ==================== ========== ======= =====
Как это работает?

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

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

#. Выбрать строку из таблицы внешнего запроса (текущая строка-кандидат)

#. Сохранить значения из этой строки во временном буфере

#. Выполнить подзапрос, используя значения строки-кандидата

#. Вычислить условие внешнего запроса на основе результатов подзапроса

#. Определить, должна ли строка-кандидат попасть в результат

#. Повторить для всех строк

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

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

.. code-block:: sql


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

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

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

.. code-block:: sql


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

Результат:

====== =======
snum sname
====== =======
1001 Иванов
1002 Петров
====== =======

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

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

.. code-block:: sql


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

Результат:

====== ===================== ========== ===== =====
ONUM ODATE AMT CNUM SNUM
====== ===================== ========== ===== =====
3009 1999-10-04 00:00:00 1713.2300 2002 1003
3010 1999-10-06 00:00:00 1309.9500 2004 1002
3011 1999-10-06 00:00:00 9891.8800 2006 1001
====== ===================== ========== ===== =====
Заключение

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