2.14. Подзапросы

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

Рассмотрим пример, как можно определить номера телефонов, у которых установлен тип 'Сотовый рабочий'. Для этого сначала необходимо узнать, какой первичный ключ у нужного типа телефона в таблице tbPhoneType:

SELECT idPhoneType 
FROM tbPhoneType 
WHERE vcTypeName = ('Сотовый рабочий')

После этого уже находим все записи в таблице tbPhoneNumbers, где поле "idPhoneType" содержит найденное значение типа телефона:

SELECT * 
FROM tbPhoneNumbers
WHERE idPhoneType = идентификатор

Эта задача достаточно просто решается с помощью подзапросов:

SELECT * 
FROM tbPhoneNumbers
WHERE idPhoneType = 
 (
  SELECT idPhoneType 
  FROM tbPhoneType 
  WHERE vcTypeName = ('Сотовый рабочий')
 )

В данном примере мы выбираем все записи из таблицы tbPhoneNumbers. При этом, поле "idPhoneType" сравнивается с результатом подзапроса, который пишется в круглых скобках. Так как стоит знак равенства, то результат подзапроса должен быть из одного поля и одной строки. Если результатом будет два поля или более одной строки, то сервер вернет нам ошибку.

Попробуем выполнить следующий запрос:

SELECT * 
FROM tbPhoneNumbers
WHERE idPhoneType =
 (
  SELECT idPhoneType 
  FROM tbPhoneType 
  WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний')
 )

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

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(Подзапрос возвращает более чем 1 значение. Это не позволено, когда подзапрос следует после знаков =, !=, <, <= , >, >= или когда подзапрос используется как выражение)

А что же тогда можно использовать? Если немного подумать, то для такого запроса знак равенства нужно заменить на оператор IN:

SELECT * 
FROM tbPhoneNumbers
WHERE idPhoneType IN
 (
  SELECT idPhoneType 
  FROM tbPhoneType 
  WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний')
 )

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

Я уже намекнул на то, что результат подзапроса должен состоять только из одной колонки. Это значит, что вы не можете написать во внутреннем запросе SELECT *, а можно только SELECT ИмяОдногоПоля. Помните, что имя должно быть только одно и тип его должен совпадать с типом сравниваемого значения. Подзапросы нужно использовать очень аккуратно, потому что они могут привести к ошибке.

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

SELECT * 
FROM tbPhoneNumbers
WHERE 
 (
  SELECT idPhoneType 
  FROM tbPhoneType 
  WHERE vcTypeName = ('Сотовый рабочий')
 ) = idPhoneType

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

Основной запрос (так же называемый внешним) может обращаться к подзапросу (внутренний запрос). Для этого таблицам необходимо указать псевдонимы. Посмотрим на следующий запрос:

SELECT * 
FROM tbPhoneNumbers ot
WHERE idPhoneType IN
 (
  SELECT idPhoneType 
  FROM tbPhoneType it
  WHERE vcTypeName in ('Сотовый рабочий', 'Сотовый домашний')
   AND ot.vcPhoneNumber LIKE '(923)%'
 )

Обратите внимание на предпоследнюю строку:

AND ot.vcPhoneNumber LIKE '(923)%'

Здесь происходит сравнение поя "vcPhoneNumber" таблицы ot с шаблоном. Самое интересное здесь в том, что ot – это псевдоним таблицы tbPhoneNumbers, которая описана в секции FROM внешнего запроса. Но, не смотря на это, мы можем из подзапроса обращаться по псевдониму к столбцам внешних запросов. Таким образом, можно наводить достаточно сложные связи между запросами.

Такой запрос будет выполняться по следующему алгоритму:

1. Выбрать строку из таблицы tbPhoneNumbers в внешнем запросе. Это будет текущая строка-кандидат.
2. Сохранить значения из этой строки-кандидата в псевдониме с именем ot.
3. Выполнить подзапрос, при этом, во время поиска участвует и внешний запрос.
4. Оценить "idPhoneType" внешнего запроса на основе результатов подзапроса выполняемого в предыдущем шаге. Он определяет - выбирается ли строка-кандидат для вывода на экран.

Подзапросы могут быть не только в секции WHERE, но и в секции SELECT и в секции FROM. Давайте рассмотрим сначала подзапросы из FROM, для этого поставим достаточно сложную, но интересную задачу. Во время группировки мы смогли научиться определять количество пользователей с именем Андрей. А что если создать таблицу, которая будет состоять из трех колонок:

• Имя;
• Количество пользователей с таким именем;
• Количество номеров телефонов для каждого имени.

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

SELECT p.vcName, COUNT (*) as PeopleNumber
FROM tbPeoples p
GROUP BY p.vcName

Такой запрос мы уже разбирали.

Теперь определим количество телефоном для каждого имени. Именно имени, а не работника. Нас интересует, сколько номеров телефонов у всех Андреев, Иванов и т.д. Эта задача решается с помощью связи из таблиц работников и телефонов, группировкой по имени и подсчетом количества телефонов:

SELECT pl.vcName, COUNT(vcPhoneNumber) AS PhoneNum
FROM tbPeoples pl, tbPhoneNumbers pn
WHERE pl.idPeoples *= pn.idPeoples
GROUP BY vcName

У нас получилось две разных таблицы. А как теперь их объединить в одно целое? Попробуйте самостоятельно решить эту задачу. Мое решение можно увидеть в листинге 2.3.

Листинг 2.3. Получение количества имен и количества телефонов у каждого имени

SELECT *
FROM
  (SELECT p.vcName, COUNT(*) as PeopleNumber
   FROM tbPeoples p
   GROUP BY p.vcName) p1,

  (SELECT pl.vcName, COUNT(vcPhoneNumber) AS PhoneNum
   FROM tbPeoples pl, tbPhoneNumbers pn
   WHERE pl.idPeoples *= pn.idPeoples
   GROUP BY vcName) p2
WHERE p1.vcName=p2.vcName

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

Для таких запросов, есть только одно ограничение – у каждого поля в подзапросе секции FROM должно быть имя. У нас есть поля, подсчитывающие количество записей и для таких полей имя не устанавливается, поэтому я установил псевдонимы. В первом подзапросе колонка с количеством записей названа как PeopleNumber, а во втором подзапросе - PhoneNum.

Внешний объединяющий запрос связывает обе полученные таблицы через поле имени "vcName", а результатом будет общая таблица (см. рис. 2.7), состоящая из четырех колонок – имя и количество из первого запроса, и имя и количество из второго запроса. Одну из колонок имен можно убрать, потому что они идентичны, но я решил оставить, чтобы вы могли увидеть связь.

Результат объединения двух таблиц

Теперь посмотрим, как можно писать подзапросы в секции SELECT. Я не нашел интересного примера, поэтому просто решил вывести всех работников и их телефоны. Но при этом, не наводить связь между таблицами работников и телефонами в секции FROM, а искать номера с помощью подзапросов в секции SELECT:

SELECT pl.*, 
 (
  SELECT vcPhoneNumber 
  FROM tbPhoneNumbers pn 
  WHERE pn.idPhoneNumbers=pl.idPeoples
 )
FROM tbPeoples pl

В секции SELECT сначала запрашиваем все колонки из таблицы tbPeoples (pl.*). После этого, вместо очередного поля в скобках указывается подзапрос, который выбирает данные. При этом в подзапросе в секции WHERE наводиться связь между таблицами. Получается, что и из этого подзапроса мы можем обращаться к полям внешнего запроса.

Единственное ограничение – подзапрос в секции SELECT должен возвращать только одну строку. Если результатом будет несколько строк, то запрос возвращает ошибку.

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

SELECT * 
FROM tbPhoneNumbers
WHERE idPhoneType = 
 (
  SELECT idPhoneType 
  FROM tbPhoneType 
  WHERE vcTypeName = ('Сотовый рабочий')
 )

Эта же задача решается следующим образом:

SELECT pn.* 
FROM tbPhoneNumbers pn, tbPhoneType pt
WHERE pn.idPhoneType = pt.idPhoneType 
 AND vcTypeName = ('Сотовый рабочий')

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

А вот следующий запрос, достаточно сложно сделать без подзапросов. Допустим, что вам нужно определить данные последней, добавленной в таблицу строки. Если в качестве первичного ключа используется автоматически увеличиваемое поле, то необходимо узнать наибольшее значение первичного ключа с помощью оператора MAX, а потом найти строку с этим ключом. Вот как определяется последняя строка в таблице tbPeoples:

SELECT * 
FROM tbPeoples 
WHERE idPeoples=
    (SELECT MAX(idPeoples) FROM tbPeoples)

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