SQL/Подзапрос

Материал из Энциклопедия о программировании
Перейти к: навигация, поиск

Подзапрос (внутренний/вложенный запрос) в SQL — полноценный SQL запрос в контексте другого SQL запроса; запрос внутри запроса; вставка одного запроса внутрь другого.

Синтаксис

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

Синопсис

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

Вложенные запросы используются в основном в конструкции WHERE. В случае с использованием внутреннего запроса и сравнения его результата при помощи оператора "равно" (=) подзапрос не должен возвращать больше одного ряда.

Подзапросы явл. альтернативным путем получения данных из множ. табл.

Наряду с операторами сравнения такими, как =, <, >, >=, <= и др., можно использ. подзапросы со след. конструкциями:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Вариации подзапросов

  1. Обычно, подзапрос возвращает только одну запись, но случается и так, что записей может быть много, тогда в условии WHERE используются такие операторы, как IN, NOT IN. Запрос может выглядеть след. обр.: SELECT first_name, last_name, subject FROM student_details WHERE games NOT IN ('Cricket', 'Football') Тогда результат будет примерно таким:
    first_name    last_name   subject
    ------------ ----------- ----------
    Shekar        Gowda       Badminton
    Priya         Chandra     Chess
    
  2. Рассмотрим используемую ранее таблицу student_details. Если известны имена студентов, изучающих естествознание (science), то можно получить их "id", использ. след. запрос:
    SELECT id, first_name FROM student_details
    WHERE first_name IN ('Rahul', 'Stephen');
    

    но, если их имена неизвестны, то для получения "id" необходимо написать запрос:

    SELECT id, first_name
    FROM student_details
    WHERE first_name IN (SELECT first_name FROM student_details WHERE subject='Science');
    

    Результат:

    Id         first_name
    --------  -------------
    100        Rahul
    102        Stephen
    
    В описанном выше запросе, вначале выполняется внутренний запрос, затем внешний.
  3. Можно использ. подзапрос с оператором INSERT для добавления данных из одной табл. в др. Сгруппировать всех студентов, которые изучают математику в табл. "math_group":
    INSERT INTO maths_group(id, name)
    SELECT id, first_name || ' ' || last_name FROM student_details WHERE subject='Maths'
    
  4. Подзапрос может использоваться с оператором SELECT, как описано ниже. Используем таблицы "product" и "order_items", объединив их между собой:
    SELECT p.product_name,
        p.supplier_name,
        (SELECT order_id FROM order_items WHERE product_id = 101) AS order_id
    FROM p
    WHERE p.product_id = 101
    

    Результат:

    product_name   supplier_name   order_id
    -------------- -------------- ----------
    Television     Onida           5103
    

Соотнесенный подзапрос

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

SELECT p.product_name
FROM p
WHERE p.product_id = (SELECT o.product_id FROM o WHERE o.product_id = p.product_id)

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

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

Ресурсы

Естественно вложенные запросы увеличивают ресурсозатраты, время обработки и выполнения т.к. они, по сути, являются ещё одними запросами. Максимальное количество вложенных запросов ограничено, напр., в СУБД Oracle - 255.

Примечания

  • Можно использ. столько вложенных запросов, сколько необходимо, но не более макс. числа – напр., в СУБД Oracle не рекомендуется использ. > 16 вложений.
  • Если подзапрос не зависит от внешнего запроса – он называется "несоотнесенным" подзапросом.

Вставка одного запроса в др.

Как работает подзапрос?

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

SELECT *
FROM Orders
WHERE snum =
	(SELECT snum
	FROM Salespeople
	WHERE sname = 'Motika');

Чтобы оценить внешний (основной) запрос, SQL сначала должен оценить внутренний запрос (подзапрос) внутри предложения WHERE. Он делает это так как и должен делать запрос имеющий единственную цель - отыскать через таблицу "продавцов" все строки, где поле sname равно значению "Motika", и затем извлечь значения поля snum этих строк.

Единственной найденной строкой естественно будет "snum" = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предикат прочитал это как "WHERE snum = 1004":

SELECT * 
FROM  Orders
WHERE snum =
	(SELECT snum
	FROM Salespeople
	WHERE sname = 'Motika');

  |   onum       amt      odate      cnum     snum  |
  |  -----     -------  ----------  -----    -----  |
  |   3002     1900.10  10/03/1990   2007     1004  |

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

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать:

WHERE snum = 1004

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

Значения, которые могут выдавать подзапросы

Скорее всего было бы удобнее, чтобы подзапрос в предыдущем примере возвращал одно и только одно значение.

Имея выбранным поле snum "WHERE city = 'London'" вместо "WHERE sname = 'Motika'", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку.

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

Это плохая стратегия, чтобы делать что-нибудь подобное следующему:

SELECT *
FROM Orders
WHERE snum =
	(SELECT snum
	FROM Salespeople
	WHERE city = 'Barcelona');

Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum и следовательно будет принят. Но это - только в данном случае. Большинство SQL баз данных имеют многочисленных пользователей, и если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значения, и ваша команда потерпит неудачу.

DISTINCT с подзапросами

Этот раздел также можно назвать, или альтернативные заголовки раздела:
  • Использование DISTINCT, чтобы вынудить получение одного значения из подзапроса

Можно в некоторых случаях использовать DISTINCT, чтобы вынудить подзапрос генерировать одиночное знач. Предположим что мы хотим найти все порядки кредитований для тех продавцов, которые обслуживают Hoffmanа (cnum = 2001). Имеется один способ сделать это:

SELECT *
FROM Orders
WHERE snum =
	(SELECT DISTINCT snum
	FROM Orders
	WHERE cnum = 2001);

  |   onum       amt      odate      cnum     snum  |
  |  -----   ---------  ---------   ------  ------- |
  |   3003      767.19  10/03/1990   2001     1001  |
  |   3008     4723.00  10/05/1990   2006     1001  |
  |   3011     9891.88  10/06/1990   2006     1001  |

Подзапрос установил, что значение поля "snum" совпало с "Hoffman" - 1001, и затем основной запрос выделил все порядки с этим значением "snum" из таблицы Порядков (не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы знаем, что каждая строка в таблице Порядков с данным значением "cnum" должна иметь такое же значение "snum". Однако так как там может быть любое число таких строк, подзапрос мог бы вывести много (хотя и идентичных) значений "snum" для данного поля "cnum". Аргумент DISTINCT предотвращает это. Если подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом.

Альтернативный подход должен ссылаться на таблицу Заказчиков, а не на таблицу Порядков в подзапросе. Так как поле "cnum" это первичный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только, если вы как пользователь имеете доступ к таблице Порядков, но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое показано выше. Учтите, что методика используемая в предшествующем примере применима только, когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она является исключением из правил.

Предикаты с подзапросами являются необратимыми

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

< скалярная форма > < оператор > < подзапрос >,

а, не

< подзапрос > < оператор > < скалярное выражение >

или

< подзапрос > < оператор > < подзапрос >.

Другими словами, вы не должны записывать предыдущий пример так:

SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
		FROM Orders
		WHERE cnum = 2001)
= snum;

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

Использование агрегатных функций в подзапросах

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

Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основном предикате. Напр., вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября. Выбор всех сумм со значением выше средней на 10/04/1990:

SELECT *
FROM Orders
WHERE amt >
	(SELECT AVG (amt)
	FROM Orders
	WHERE odate = 10/04/1990);

  |   onum       amt      odate      cnum     snum  |
  |  -----    --------  ----------  -----    -----  |
  |   3002     1900.10  10/03/1990   2007     1004  |
  |   3005     2345.45  10/03/1990   2003     1002  |
  |   3006     1098.19  10/03/1990   2008     1007  |
  |   3009     1713.23  10/04/1990   2002     1003  |
  |   3008     4723.00  10/05/1990   2006     1001  |
  |   3010     1309.95  10/06/1990   2004     1002  |
  |   3011     9891.88  10/06/1990   2006     1001  |

Средняя сумма приобретений на 4 Октября - 1788.98 ( 1713.23 + 75.75) делится пополам, что в целом равняется = 894.49. Все строки со значением в поле amt выше этого являются выбранными.

Имейте ввиду, что сгруппированные агрегатные функции, которые являются агрегатными функциями определенными в терминах предложения GROUP BY, могут производить многочисленные значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны использовать одиночную агрегатную функцию с предложением WHERE, что устранит нежелательные группы. Напр., следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне -

SELECT AVG (comm)
FROM Salespeople
GROUP BY city
HAVlNG city = "London";

не может использоваться в подзапросе! Во всяком случае это не лучший способ формировать запрос. Другим способом может быть -

SELECT AVG (comm)
FROM Salespeople
WHERE city = "London";

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

Этот раздел также можно назвать, или альтернативные заголовки раздела:
  • Использование подзапросов, которые выдают много строк с помощью оператора IN

Вы можете использовать подзапросы, которые производят любое число строк, если вы используете специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был верным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN, чтобы выполнить такой же подзапрос, который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне:

SELECT *
FROM Orders
WHERE snum IN
	 (SELECT snum
	 FROM Salespeople
	 WHERE city = "LONDON");

  |   onum       amt      odate      cnum     snum  |
  |  -----    --------  ----------  -----   ------  |
  |   3003      767.19  10/03/1990   2001     1001  |
  |   3002     1900.10  10/03/1990   2007     1004  |
  |   3006     1098.19  10/03/1990   2008     1007  |
  |   3008     4723.00  10/05/1990   2006     1001  |
  |   3011     9891.88  10/06/1990   2006     1001  |

В ситуации подобно этой, подзапрос - более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали объединение:

SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum = Salespeople.snum
	AND Salespeople.city = "London";

Хотя это и произведет тот же самый вывод, что и в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля snum, где "city = 'London'", и затем искать эти значения в таблице Порядков, как это делается в варианте с подзапросом. Внутренний запрос дает нам "snums=1001" и "snum=1004". Внешний запрос, затем, дает нам строки из таблицы Порядков, где эти поля snum найдены. Строго говоря, быстрее или нет работает вариант подзапроса, практически зависит от реализации - в какой программе вы это используете. Эта часть вашей программы, называемой "оптимизатор", пытается найти наиболее эффективный способ выполнения ваших запросов.

Хороший оптимизатор во всяком случае преобразует вариант объединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем полагаться полностью на оптимизатор.

Конечно вы можете также использовать оператор IN, даже когда вы уверены, что подзапрос произведет одиночное значение. В любой ситуации, где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу, если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете, что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различи в выводе основного запроса. Напр., рассмотрим команду, которая похожа на предыдущую:

SELECT onum, amt, odate
FROM Orders
WHERE snum =
	(SELECT  snum
	FROM Orders
	WHERE cnum = 2001);

Вы можете устранить потребность в DISTINCT используя IN вместо (=), подобно этому:

SELECT onum, amt, odate
FROM Orders
WHERE snum IN
	(SELECT snum
	FROM Orders
	WHERE cnum = 2001);

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

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

В принципе, если вы знаете, что подзапрос должен (по логике) вывести только одно значение, вы должны использовать "=". IN является подходящим, если запрос может ограниченно производить одно или более значений, независимо от того ожидаете вы их или нет. Предположим, мы хотим знать комиссионные всех продавцов обслуживающих заказчиков в Лондоне. Использование IN с подзапросом для вывода одного значения:

SELECT comm
FROM Salespeople
WHERE snum IN
	(SELECT snum
	FROM Customers
	WHERE city = "London");

Выводимыми для этого запроса являются значения комиссионных продавца Peel (snum = 1001), который имеет обоих заказчиков в Лондоне. Это только для данного случая. Нет никакой причины чтобы некоторые заказчики в Лондоне не могли быть назначенными к кому-то ещё. Следовательно, IN - это наиболее логична форма, чтобы использовать её в запросе.

  |  comm  |
  | ------ |
  |  0.12  |

Между прочим, префикс таблицы для поля city необязателен в предыдущем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает, что имеется ссылка к Customer.city (поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает, что это правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже когда будем говорить об соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.

Подзапросы выбирают одиночные столбцы

Смысл всех подзапросов обсужденных в этой статье в том, что все они выбирают одиночный столбец. Это обязательно, поскольку выбранный вывод сравнивает с одиночным значением. Подтверждением этому то, что "SELECT *" не может использоваться в подзапросе. Имеется исключение из этого, когда подзапросы используются с оператором EXISTS.

Использование выражений в подзапросах

Вы можете использовать выражение основанное на столбце, а не просто сам столбец, в предложении SELECT подзапроса. Это может быть выполнено или с помощью реляционных операторов или с IN. Напр., следующий запрос использует реляционный оператор "=". Использование подзапроса с выражением:

SELECT *
FROM Customers
WHERE cnum =
	(SELECT snum + 1000
	 FROM Salespeople
	 WHERE sname = 'Serres');

Он находит всех заказчиков чьё значение поля cnum равно 1000 выше поля snum Serres. Мы предполагаем, что столбец sname не имеет никаких двойных значений.

  |   cnum     cname     city    rating    snum   |
  |  -----    --------   ----    ------   -----   |
  |   2002    Giovanni   Rome       200    1003   |

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

Подзапросы в предложении HAVING

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

SELECT rating, COUNT ( DISTINCT cnum )
FROM Customers
GROUP BY rating
HAVING rating >
	(SELECT AVG (rating), snum + 1000
	FROM Customers
	WHERE city = 'San Jose';

  |  rating              |
  | --------    -------- |
  |   200             2  |

Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков, которые имели эту оценку.