Вложенный sql запрос: Вложенные запросы (SQL Server)

Содержание

вложенные запросы и временные таблицы

Добро пожаловать в следующую статью из цикла про SQL! В предыдущей статье мы считали Transactions и Gross для приложения на двух платформах и получили отдельный результат для каждого приложения.

Transactions и Gross для приложения «3 in a row»‎. Скриншот из демо devtodev

Но что если мы хотим обобщить его и для каждой метрики иметь только одно значение? Для этого мы будем использовать результат, получившийся с помощью операции union, как таблицу в операторе from. И затем в select вычислять сумму по полю transactions и gross из объединенной таблицы (скриншот выше).

Залогиньтесь на сайте, зайдите в демо и найдите SQL отчёт во вкладке Reports.

select ‘Metrics for all projects’ as «App»
, sum(transactions) as «Transactions»
, sum(gross) as «Gross»
from (
select count() as transactions
, sum(priceusd) as gross
from p102968. payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date

union all
select count() as «Transactions»
, sum(priceusd) as «Gross»
from p104704.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
) as metrics_by_platform

Результат запроса. Скриншот из демо devtodev

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

) as metrics_by_platform

Такую конструкцию можно использовать во всех операторах, обращающихся к таблицам. Например, в join

Inner join (select … from … where …) as join_table
on join_table.param = t.param

Метрики по отдельным приложениям и суммарно по всем

Давайте в одном запросе посчитаем суммарные метрики по всем приложениям, а также выведем расшифровку (метрики по каждому из приложений) ниже.

select ‘Metrics for all projects’ as «App»
, sum(transactions) as «Transactions»
, sum(gross) as «Gross»
from (
select ‘3 in a row. iOS’ as «App»
, count() as transactions
, sum(priceusd) as gross
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date

union all
select ‘3 in a row. Android’ as «App»
, count() as transactions
, sum(priceusd) as gross
from p104704.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
) metrics_by_platform

union all
select ‘3 in a row. iOS’ as «App»
, count() as transactions
, sum(priceusd) as gross
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date

union
select ‘3 in a row. Android’ as «App»
, count() as «Transactions»
, sum(priceusd) as «Gross»
from p104704.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
order by 3 desc

Результат запроса. Скриншот из демо devtodev

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

payments, и к тому же мы два раза написали один и тот же код (при изменении запроса нам придётся вносить изменения в двух местах).

Чтобы избежать этого, мы можем создать представление (Common Table Expression – CTE), и затем в ходе запроса обращаться к нему несколько раз. Конструкция может содержать в себе сколь угодно сложные запросы и обращаться к другим представлениям. Она выглядит следующим образом:

with temp_table_name as
(select … from …)

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

Вот как вышеуказанный запрос будет выглядеть с использованием представлений CTE:

with metrics_by_platform as (
select ‘3 in a row. iOS’ as app
, count() as transactions
, sum(priceusd) as gross
from p102968.payments
where eventtime > current_date — interval ‘9 day’ and eventtime < current_date

union all
select ‘3 in a row. Android’ as app
, count() as transactions
, sum(priceusd) as gross
from p104704.payments
where eventtime > current_date — interval ‘9 day’ and eventtime < current_date
)
select ‘Metrics for all projects’ as «App»
, sum(transactions) as «Transactions»
, sum(gross) as «Gross»
from metrics_by_platform

union all
select app
, transactions
, gross
from metrics_by_platform
order by 3 desc

Выглядит проще, не правда ли? Если мы добавим новое приложение и захотим анализировать и его метрики, мы просто добавим его в представление

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

Доля пользователей, совершивших максимальное количество платежей (вложенные запросы)

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

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

У нас есть таблица со всеми платежами пользователей from p102968.payment. Из нее мы посчитаем количество совершенных платежей для каждого из пользователей, сгруппировав их по devtodevid, а потом найдем максимальное число таких платежей с помощью

max().

select max(user_payments) as «max_payments»
from (
select devtodevid
, count() user_payments
from p102968.payments
where eventtime > current_date — interval ‘9 day’ and eventtime < current_date
group by devtodevid
) as payments_count

Результат запроса.

Скриншот из демо devtodev

Осталось узнать, сколько пользователей совершили 12 платежей за это же время. Для этого только что выполненный запрос мы помещаем в фильтр where user_payments = (запрос), который оставит нам только пользователей с соответствующим максимальному количеством платежей. Сам запрос будет возвращать число таких пользователей select count() as «Users» и максимальное количество платежей max(user_payments) as «Max payments count» из таблицы from (…) as payments_count

select count(devtodevid) as «Users»
, max(user_payments) as «Max payments count»
from (
select devtodevid
, count() user_payments
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
group by devtodevid
) as payments_count

where user_payments = (select max(user_payments)
from
(select devtodevid
, count() user_payments
from p102968. payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
group by devtodevid) as payments_count
)

Результат запроса. Скриншот из демо devtodev

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

максимального количества платежей пользователей where user_payments = (…) . В коде мы два раза использовали один и тот же запрос, поэтому давайте оптимизируем его с помощью представления CTE.

with payments_count as (
select devtodevid
, count() user_payments
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
group by devtodevid
)

select count() as «Users»
, max(user_payments) as «Payments count»
from payments_count
where user_payments = (select max(user_payments)
from payments_count

А какова доля пользователей с таким количеством платежей среди всех платящих пользователей? Может быть он всего один и платил?

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

На это число мы затем и поделим количество пользователей с максимальным платежом.

with payments_count as (
select devtodevid
, count() user_payments
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
group by devtodevid
)

select count() as «Users with max payments count»
, max(user_payments) as «Payments count»
, round(count()*100::numeric / (select count(distinct devtodevid)
from p102968.payments
where eventtime > current_date — interval ‘7 day’ and eventtime < current_date
, 2) ||’%’ as «% of all payers»
from payments_count
where user_payments = (select max(user_payments)
from payments_count)

Результат запроса. Скриншот из демо devtodev

Вложенные запросы внутри select – довольно распространенная практика.

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

P.S.

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

Вложенные запросы SQL — CodeTown.ru

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

Введение

Итак, само название говорит о том, что запрос во что-то вложен. Так вот, вложенный запрос в SQL означает, что запрос select выполняется в еще одном запросе select — на самом деле вложенность может быть и многоуровневой, то есть select в select в select и т.д.

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

Структура ранее созданных таблиц

Прежде чем перейдем к простому примеру, напомним структуру наших таблиц, с которыми будем работать:

Таблица Salespeople (продавцы):

snum	sname	city	comm
1	Колованов	Москва	10
2	Петров	Тверь	25
3	Плотников	Москва	22
4	Кучеров	Санкт-Петербург	28
5	Малкин	Санкт-Петербург	18
6	Шипачев	Челябинск	30
7	Мозякин	Одинцово	25
8	Проворов	Москва	25

Таблица Customers (покупатели):

сnum	сname	city	rating	snum
1	Деснов	Москва	90	6
2	Краснов	Москва	95	7
3	Кириллов	Тверь	96	3
4	Ермолаев	Обнинск	98	3
5	Колесников	Серпухов	98	5
6	Пушкин	Челябинск	90	4
7	Лермонтов	Одинцово	85	1
8	Белый	Москва	89	3
9	Чудинов	Москва	96	2
10	Лосев	Одинцово	93	8

Таблица Orders (заказы)

onum	amt	odate	cnum	snum
1001	128	2016-01-01	9	4
1002	1800	2016-04-10	10	7
1003	348	2017-04-08	2	1
1004	500	2016-06-07	3	3
1005	499	2017-12-04	5	4
1006	320	2016-03-03	5	4
1007	80	2017-09-02	7	1
1008	780	2016-03-07	1	3
1009	560	2017-10-07	3	7
1010	900	2016-01-08	6	8

Основы вложенных запросов в SQL

Вывести сумму заказов и дату, которые проводил продавец с фамилией Колованов.

Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу Salespeople, определили бы snum продавца Колыванова — он равен 1. И выполнили бы запрос SQL с помощью условия WHERE. Вот пример такого SQL запроса:

SELECT amt, odate
FROM orders 
WHERE snum = 1

Очевидно, какой будет вывод:

amt	odate
348	2017-04-08
80	2017-09-02

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

SELECT amt, odate
FROM orders
where snum = (SELECT snum
              FROM salespeople
              WHERE sname = 'Колованов')

В этом примере мы определяем с помощью вложенного запроса идентификатор snum по фамилии из таблицы salespeople, а затем, в таблице orders определяем по этому идентификатору нужные нам значения. Таким образом работают вложенные запросы SQL.

Рассмотрим еще один пример:
Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.

SELECT snum, sname
FROM salespeople
where snum IN (SELECT snum
              FROM orders
              WHERE YEAR(odate) = 2016)

Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN. Его следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений. То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы salespeople в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.

Получился такой результат:

snum	sname
3	Плотников
4	Кучеров
7	Мозякин
8	Проворов

Вложенные запросы SQL с несколькими параметрами

Те примеры, которые мы уже рассмотрели, сравнивали в условии WHERE одно поле. Это конечно хорошо, но стоит отметить, что в SQL предусмотрена возможность сравнения сразу нескольких полей, то есть можно использовать вложенный запрос с несколькими параметрами.

Вывести пары покупателей и продавцов, которые осуществили сделку между собой в 2017 году.

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

SELECT cname as 'Покупатель', sname as 'Продавец'
FROM customers cus, salespeople sal
where (cus.cnum, sal.snum) IN (SELECT cnum, snum
              FROM orders
              WHERE YEAR(odate) = 2017)

Вывод запроса:

Покупатель	Продавец
Краснов	Колованов
Колесников	Кучеров
Лермонтов	Колованов
Кириллов	Мозякин

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

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

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

Примеры на вложенные запросы SQL

1.Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателя с фамилией Краснов. Предположим, что вы не знаете номера этого покупателя, указываемого в поле cnum.

SELECT *
FROM orders
where cnum = (SELECT cnum
              FROM customers
              WHERE cname = 'Краснов')

2. Напишите запрос, который вывел бы имена и рейтинг всех покупателей, которые имеют Заказы, сумма которых выше средней.

SELECT cname, rating
FROM customers
where cnum IN (SELECT cnum
              FROM orders
              WHERE amt > (SELECT AVG(amt)
                          from orders))

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

SELECT snum, SUM(AMT) 
FROM orders 
GROUP BY snum 
HAVING SUM(amt) > (SELECT MAX(amt) 
                        FROM orders)

4. Напишите запрос, который бы использовал подзапрос для получения всех Заказов для покупателей проживающих в Москве.

SELECT *
FROM orders
where cnum IN (SELECT cnum
              FROM customers
              WHERE city =  'Москва')

5. Используя подзапрос определить дату заказа, имеющего максимальное значение суммы приобретений (вывести даты и суммы приобретений).

SELECT amt, odate
FROM orders
WHERE AMT = (SELECT MAX(AMT)
             FROM orders)

6. Определить покупателей, совершивших сделки с максимальной суммой приобретений.

SELECT cname
FROM customers
WHERE cnum IN (SELECT cnum
               FROM orders
               WHERE amt = (SELECT MAX(amt)
                            FROM orders))

Заключение

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

MySQL. Вложеные запросы. JOIN LEFT/RIGHT….

В SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE.

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

SQL подзапрос — это запрос, вложенный в другой запрос.

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

Существует несколько правил, которые применяются к подзапросам:

Подзапросы должны быть заключены в круглые скобки.
Подзапрос может иметь только один столбец в условии SELECT, если только несколько столбцов не указаны в основном запросе для подзапроса для сравнения выбранных столбцов.
Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как оператор IN.
Команда ORDER BY не может использоваться в подзапросе, хотя в основном запросе она использоваться может. В подзапросе может использоваться команда GROUP BY для выполнения той же функции, что и ORDER BY.
С подзапросом не может использоваться оператор BETWEEN. Однако оператор BETWEEN может использоваться внутри подзапроса.
Не рекомендуется создавать запросы со степенью вложения больше трех. Это приводит к увеличению времени выполнения и к сложности восприятия кода.

Синтаксис

SELECT

Подзапросы чаще всего используются с инструкцией SELECT. При этом используется следующий синтаксис


					
    SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
        (SELECT имя_столбца FROM имя_таблицы WHERE часть условия IN
            (SELECT имя_столбца FROM имя_таблицы WHERE условие)
                ...
		)
		;

Ниже представлена струтура таблицы для демонстрации примеров

Пример таблицы продавцов `SALES`
snum	sname	city	comm
1	Колованов	Москва	10
2	Петров	Тверь	25
3	Плотников	Москва	22
4	Кучеров	Санкт-Петербург	28
5	Малкин	Санкт-Петербург	18
6	Шипачев	Челябинск	30
7	Мозякин	Одинцово	25
8	Проворов	Москва	25

Пример таблицы покупателей `CUSTOMERS`
cnum	cname	city	rating	snum
1	Деснов	Москва	90	6
2	Краснов	Москва	95	7
3	Кириллов	Тверь	96	3
4	Ермолаев	Обнинск	98	3
5	Колесников	Серпухов	98	5
6	Пушкин	Челябинск	90	4
7	Белый	Одинцово	85	1
8	Чудинов	Москва	89	3
9	Проворов	Москва	95	2
10	Лосев	Одинцово	75	8

Пример таблицы заказов `ORDERS`
onum	amt	odate(YEAR)	cnum	snum
1001	420	2013	9	4
1002	653	2005	10	7
1003	960	2016	2	1
1004	320	2016	3	3
1005	200	2015	5	4
1006	2560	2014	5	4
1007	1200	2013	7	1
1008	50	2017	1	3
1009	564	2012	3	7
1010	900	2018	6	8

Вывести суммы заказов и даты, которые проводил продавец с фамилией «Плотников».

Начнем с такого примера и для начала вспомним, как бы делали этот запрос ранее: посмотрели бы в таблицу SALES(или выполнили отдельный запрос), определили бы snum продавца «Плотников» — он равен 3. И выполнили бы запрос SQL с помощью условия WHERE.


					
    SELECT amt, odate
    FROM orders 
    WHERE snum = 3

Результат работы
amt	odate
320	2016
50	2017

Такой запрос, очевидно, не очень универсален, если нам захочется выбрать тоже самое для другого продавца, то всегда придется определять его snum. В SQL предусмотрена возможность объединять такие запросы в один путем превращения одного из них в подзапрос (вложенный запрос).


					
    SELECT amt, odate
    FROM orders
    WHERE snum = (  SELECT snum
                    FROM sales
                    WHERE sname = 'Плотников')

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

Показать уникальные номера и фамилии продавцов, которые провели сделки в 2016 году.


					
    SELECT snum, sname
    FROM sales
    WHERE snum IN ( SELECT snum
                    FROM orders
                    WHERE odate = 2016)

Этот SQL запрос отличается тем, что вместо знака = здесь используется оператор IN.

Оператор IN следует использовать в том случае, если вложенный подзапрос SQL возвращает несколько значений.

То есть в запросе происходит проверка, содержится ли идентификатор snum из таблицы SALES в массиве значений, который вернул вложенный запрос. Если содержится, то SQL выдаст фамилию этого продавца.

Результат запроса
snum	sname
1	Колованов
3	Плотников

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

Вывести пары покупателей и продавцов, которые осуществили сделку между собой, но не позднее 2014 года

Запрос чем то похож на предыдущий, только теперь мы добавляем еще одно поле для сравнения.


					
    SELECT cname as 'Покупатель', sname as 'Продавец'
    FROM customers cus, sales sal
    WHERE (cus.cnum, sal.snum) IN ( SELECT cnum, snum
                                    FROM orders
                                    WHERE odate < 2014 )

Список пар покупатель — продавец
Покупатель	Продавец
Проворов	Кучеров
Лосев	Мозякин
Белый	Колованов
Кириллов	Мозякин

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

На самом деле, такой запрос SQL используется крайне редко, обычно используют оператор INNER JOIN.

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

Подзапросы могут использоваться с инструкциями SELECT, INSERT, UPDATE и DELETE вместе с операторами типа =, <, >, >=, <=, IN, BETWEEN и т. д.

Далее будут показы примеры использования вложеных запросов с использованием базы данных world. БД находится в папке _lec\7\db вместе с лекцией.

CREATE

Данный пример несовсем относится к теме занятия, но по жизни он очень может пригодиться.

Задача — создать копию существующей таблицы.

Копия существующей таблицы может быть создана с помощью комбинации CREATE TABLE и SELECT.

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


					
    CREATE TABLE NEW_TABLE_NAME AS
                                    SELECT [ column1, column2. ..columnN ]
                                    FROM EXISTING_TABLE_NAME
                                    [ WHERE ]

Создадим копию таблицы city. Вопрос — почему 1=0?


					
    CREATE TABLE city_bkp AS
                            SELECT *
                            FROM city
                            WHERE 1=0

INSERT

Задача — создать копию существующей таблицы.

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


					
    INSERT INTO table_name [ (column1 [, column2 ]) ]
                SELECT [ *|column1 [, column2 ]
                FROM table1 [, table2 ]
                [ WHERE VALUE OPERATOR ]

Копирование всей таблицы полностью


					
    INSERT INTO city_bkp SELECT * FROM city

Копируем города которые находся в стране с численостью не меньше 500тыс. человек, но не больше 1 миллиона.


					
    INSERT INTO city_bkp 
                SELECT * FROM city 
                WHERE CountryCode IN 
                                (SELECT Code FROM country 
                                 WHERE Population < 1000000 AND Population > 500000)

UPDATE

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


					
    UPDATE table
    SET column_name = new_value
        [ WHERE OPERATOR [ VALUE ]
        (SELECT COLUMN_NAME
         FROM TABLE_NAME)
         [ WHERE) ]

Исходя из того, что у нас есть таблица CITY_BKP, которая является резервной копией таблицы CITY, в следующем примере для всех записей, для которых Population больше или равно 100000, применяет коэффициент 0,25.


					
    UPDATE city_bkp SET Population = Population * 0.25 
    WHERE Population IN (
                         SELECT Population FROM city 
                         WHERE Population >= 100000 )

DELETE

Подзапрос может использоваться в сочетании с инструкцией DELETE, так же как и со всеми описанными выше инструкциями. Основной синтаксис следующий.


					
    DELETE FROM TABLE_NAME
    [ WHERE OPERATOR [ VALUE ]
        (SELECT COLUMN_NAME
         FROM TABLE_NAME)
         [ WHERE) ]

Далее будут показы примеры использования вложеных запросов с использованием базы данных world. БД находится в папке _lec\7\db вместе с лекцией.

Внутреннее объединение

Вывести идентификатор и название города, а так же страну нахождения

Для этого проще всего обратиться к таблице CITY


				
    SELECT ID, Name, CountryCode FROM city

Но, что если нам необходимо, чтобы в ответе на запрос был не код страны, а её название? Вложенные запросы нам не помогут. А нам надо получить данные из двух таблиц и объединить их в одну. Запросы, которые позволяют это сделать, в SQL называются объединениями. Синтаксис самого простого объединения следующий:


				
    SELECT city.ID, city.Name, country.Name FROM city, country

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

Чтобы результирующая таблица выглядела так, как мы хотели, необходимо указать условие объединения. Мы связываем наши таблицы по идентификатору, это и будет нашим условием.


				
    SELECT city.ID, city.Name, country.Name 
    FROM city, country 
    WHERE city.CountryCode = country.Code

Т.е. мы в запросе сделали следующее условие: если в обеих таблицах есть одинаковые идентификаторы, то строки с этим идентификатором необходимо объединить в одну результирующую строку.

Как вы понимаете, объединения дают возможность выбирать любую информацию из любых таблиц, причем объединяемых таблиц может быть и три, и четыре, да и условие для объединения может быть не одно.

JOIN LEFT/RIGHT

JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Входит в предложение FROM операторов SELECT, UPDATE и DELETE.

JOIN используется для объединения строк из двух или более таблиц на основе соответствующего столбца между ними.

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

Особенности операции соединения

в схему таблицы-результата входят столбцы обеих исходных таблиц
каждая строка таблицы-результата является «сцеплением» строки из одной таблицы со строкой второй таблицы

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

Ниже представлена струтура таблицы для демонстрации примеров

Таблица персонала `Person`
id	name	city_id
1	Колованов	1
2	Петров	3
3	Плотников	12
4	Кучеров	4
5	Малкин	2
6	Иванов	13

Ниже представлена струтура таблицы для демонстрации примеров

Таблица городов `City`
id	name	population
1	Москва	100
2	Нижний Новгород	25
3	Тверь	22
4	Санкт-Петербург	80
5	Выборг	18
6	Челябинск	30
7	Одинцово	5
8	Павлово	5

INNER JOIN

Оператор внутреннего соединения INNER JOIN соединяет две таблицы. Порядок таблиц для оператора неважен, поскольку оператор является симметричным.

Заголовок таблицы-результата является объединением (конкатенацией) заголовков соединяемых таблиц.


					
    SELECT * FROM Person
    INNER JOIN
        City
    ON Person.city_id = City.id

Результат запроса
Person.id	Person.name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25

INNER JOIN

Тело результата логически формируется следующим образом. Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы, после чего для полученной «соединённой» строки проверяется условие соединения (вычисляется предикат соединения). Если условие истинно, в таблицу-результат добавляется соответствующая «соединённая» строка.

В SQL существуют разные типы объединений. Мы рассмотрим только некоторые из них.

LEFT JOIN

Возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

LEFT JOIN


					
    SELECT * FROM Person
    LEFT JOIN
        City
    ON Person.city_id = City.id

Для записей неудовлетворяющих условия объединения поля правой таблицы заполняются значениями NULL

Результат запроса
Person.id	Person. name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
3	Плотников	12	NULL	NULL	NULL
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25
6	Иванов	13	NULL	NULL	NULL

RIGHT JOIN

Возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

RIGHT JOIN


					
    SELECT * FROM Person
    RIGHT JOIN
        City
    ON Person.city_id = City.id

Для записей неудовлетворяющих условия объединения поля левой таблицы заполняются значениями NULL

Результат запроса
Person. id	Person.name	Person.city_id	City.id	City.name	City.population
1	Колованов	1	1	Москва	100
2	Петров	3	3	Тверь	22
4	Кучеров	4	4	Санкт-Петербург	80
5	Малкин	2	2	Нижний Новгород	25
NULL	NULL	NULL	5	Выборг	18
NULL	NULL	NULL	6	Челябинск	30
NULL	NULL	NULL	7	Одинцово	5
NULL	NULL	NULL	8	Павлово	5

Python SQLite: вложенные SQL-запросы

Смотреть материал на видео

На этом занятии поговорим о возможности создавать вложенные запросы к СУБД. Лучше всего это понять на конкретном примере. Предположим, что у нас имеются две таблицы:

Первая students содержит информацию о студентах, а вторая marks – их отметки по разным дисциплинам. Каждый из студентов (кроме четвертого) проходил язык Си. От нас требуется выбрать всех студентов, у которых оценка по языку Си выше, чем у Маши (студент с id = 2). По идее нам тут нужно реализовать два запроса: первый получает значение оценки для Маши по языку Си:

SELECT mark FROM marks
WHERE id = 2 AND subject LIKE 'Си'

А второй выбирает всех студентов, у которых оценка по этому предмету выше, чем у Маши:

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > 3 AND subject LIKE 'Си'

Так вот, в языке SQL эти два запроса можно объединить, используя идею вложенных запросов:

SELECT name, subject, mark FROM marks
JOIN students ON students. rowid = marks.id
WHERE mark > (SELECT mark FROM marks
WHERE id = 2 AND subject LIKE 'Си')
AND subject LIKE 'Си'

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

Но, что если вложенный запрос вернет несколько записей (оценок), например, если записать его вот так:

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > (SELECT mark FROM marks WHERE id = 2 )
AND subject LIKE 'Си'

В этом случае будет использован только первый полученный результат, другие попросту проигнорируются и результат будет тем же (так как первое значение – это оценка Маши по предмету Си).

Если же вложенный SELECT ничего не находит (возвращает значение NULL), то внешний запрос не будет возвращать никаких записей.

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

SELECT name, subject, mark FROM marks
JOIN students ON students.rowid = marks.id
WHERE mark > (SELECT avg(mark) FROM marks WHERE id = 2 )
AND subject LIKE 'Си'

Вложения в команде INSERT

Вложенные запросы можно объявлять и в команде INSERT. Предположим, что у нас имеется еще одна таблица female вот с такой структурой:

Она идентична по структуре таблице students со списком студентов. Наша задача добавить в female всех студентов женского пола.

Для начала запишем запрос выбора девушек из таблицы students:

SELECT * FROM students WHERE sex = 2

А, затем, укажем, что их нужно поместить в таблицу female:

INSERT INTO female SELECT * FROM students WHERE sex = 2

После выполнения этого запроса таблица female будет содержать следующие записи:

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

Чтобы поправить ситуацию, можно вложенный запрос написать так:

INSERT INTO female 
SELECT NULL, name, sex, old FROM students WHERE sex = 2

Мы здесь в качестве значения первого поля указали NULL и, соответственно, СУБД вместо него сгенерирует уникальный ключ для добавляемых записей. Теперь таблица female выглядит так:

Вложения в команде UPDATE

Похожим образом можно создавать вложенные запросы и для команды UPDATE. Допустим, мы хотим обнулить все оценки в таблице marks, которые меньше или равны минимальной оценки студента с id = 1. Такой запрос можно записать в виде:

UPDATE marks SET mark = 0
WHERE mark <= (SELECT min(mark) FROM marks WHERE id = 1)

И на выходе получим измененную таблицу:

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

Вложения в команде DELETE

Ну, и наконец, аналогичные действия можно выполнять и в команде DELETE. Допустим, требуется удалить из таблицы students всех студентов, возраст которых меньше, чем у Маши (студента с id = 2). Запрос будет выглядеть так:

DELETE FROM students
WHERE old < (SELECT old FROM students WHERE id = 2)

В результате, получим таблицу:

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

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

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

Видео по теме

Python SQLite #1: что такое СУБД и реляционные БД

Python SQLite #2: подключение к БД, создание и удаление таблиц

Python SQLite #3: команды SELECT и INSERT при работе с таблицами БД

Python SQLite #4: команды UPDATE и DELETE при работе с таблицами

Python SQLite #5: агрегирование и группировка GROUP BY

Python SQLite #6: оператор JOIN для формирования сводного отчета

Python SQLite #7: оператор UNION объединения нескольких таблиц

Python SQLite #8: вложенные SQL-запросы

Python SQLite #9: методы execute, executemany, executescript, commit, rollback и свойство lastrowid

Python SQLite #10: методы fetchall, fetchmany, fetchone, Binary, iterdump

Видео курс SQL Essential.

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

Главная >
Каталог >
SQL Базовый >
Вложенные запросы

Для прохождения теста нужно авторизироваться

Войти Регистрация

Вы открыли доступ к тесту! Пройти тест

Для просмотра полной версии видеокурса, онлайн тестирования и получения доступа к дополнительным учебным материалам купите курс Купить курс

Для просмотра всех видеокурсов ITVDN, представленных в Каталоге, а также для получения доступа к учебным материалам и онлайн тестированию купите подписку Купить подписку

№1

Введение в SQL

0:59:17