Подзапросы SQL в разделе FROM. Sql подзапросы в

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

Подзапросы в Transact SQL — запрос внутри другого запроса.

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

Подзапросы можно писать в любом месте запроса (SELECT, FROM, WHERE).

Пример:

SELECT title,price, ( SELECT Count(*) FROM orders) FROM titles

Пример выше — это запрос без корреляции, т.е. 2 запроса между собой никак не связаны.

На практике чаще применяют корреляционные запросы, когда запросы зависят друг от друга.

Рассмотрим пример. Есть 2 таблицы: с книгами и с продажами этих книг. Получим отчет — название проданной книги и количество экземпляров:

SELECT title,price, ( SELECT Sum(qty) FROM sales WHERE title_id=titles.title_id) FROM titles

Другой пример. Есть таблица с товарами разных категорий. Информация о категориях лежит в другой таблице (categories). Узнаем, сколько товаров в каждой категории:

SELECT categoryName, ( SELECT Count(*) FROM products WHERE categoryID=categories.categoryID) FROM categories

Подзапросы в FROM

Рассмотрим на примере. SELECT возвращает список городов, в котором живет хотя бы один продавец.

SELECT DISTINCT city FROM customers

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

SELECT * FROM (SELECT DISTINCT city FROM customers)MyTable

В примере надо придумать синоним для виртуальной таблицы (MyTable) — это обязательно для всех корреляционых запросов во избежание синтаксической ошибки. И с полученной таблицей можно дальше работать, как обычно.

Подзапрос в WHERE

SELECT * FROM employees WHERE employeeID IN ( SELECT employeeID FROM orders WHERE Year(order_date)=2012)

По аналогией с конструкцией FROM. Внутри WHERE пишем то, чего не хватает в нашей таблице. Эти сведения берем из другой table.

Еще пример:

SELECT CustomerID, ContactName,( SELECT Sum(UnitPrice*Quantity*(1-Discount)) FROM [Order Details] WHERE OrderID IN ( SELECT OrderID FROM Orders WHERE CustomerID=CUstomers.CustomerID) ) AS TotalSum FROM Customer

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

1st-network.ru

sql - Подзапрос SQL в предложении FROM

Я нашел несколько вопросов, которые касаются этой проблемы, и кажется, что MySQL этого не позволяет. Это хорошо, я не должен иметь подзапрос в предложении FROM. Однако я не знаю, как обойти это. Здесь моя настройка:

У меня есть таблица показателей, которая имеет 3 столбца, которые я хочу: ControllerID, TimeStamp и State. В принципе, механизм сбора данных связывается с каждым контроллером в базе данных каждые 5 минут и помещает запись в таблицу показателей. В таблице есть три столбца, плюс MetricsID, которые мне не нужны. Может быть, есть лучший способ сохранить эти показатели, но я этого не знаю. Независимо от того, я хочу представление, которое берет самый последний TimeStamp для каждого из разных идентификаторов ControllerID и захватывает TimeStamp, ControllerID и State. Поэтому, если есть 4 контроллера, в представлении всегда должно быть 4 строки, каждый с другим контроллером, а также его последнее состояние.

Мне удалось создать запрос, который получает то, что я хочу, но он полагается на подзапрос в предложении FROM, что не допускается в представлении. Вот что я до сих пор:

SELECT * FROM (SELECT ControllerID, TimeStamp, State FROM Metrics ORDER BY TimeStamp DESC) AS t GROUP BY ControllerID;

Как я уже сказал, это отлично работает. Но я не могу использовать его в представлении. Я пробовал использовать функцию max(), но здесь: SQL: Любой простой способ упорядочить результаты FIRST, THEN group by another column?, если я хочу дополнительные столбцы, кроме столбцов GROUP BY и ORDER BY, max() не работает. Я подтвердил это ограничение, оно не работает.

Я также попытался изменить таблицу показателей для заказа TimeStamp. Это тоже не работает; неправильные строки сохраняются.

Изменить: Вот таблица SHOW CREATE TABLE таблицы Metrics, из которой я извлекаю:

CREATE TABLE Metrics ( MetricsID int(11) NOT NULL AUTO_INCREMENT, ControllerID int(11) NOT NULL, TimeStamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, State tinyint(4) NOT NULL, PRIMARY KEY (MetricsID), KEY makeItFast (ControllerID,MetricsID), KEY fast (ControllerID,TimeStamp), KEY fast2 (MetricsID), KEY MetricsID (MetricsID), KEY TimeStamp (TimeStamp) ) ENGINE=InnoDB AUTO_INCREMENT=8958 DEFAULT CHARSET=latin1

qaru.site

Подзапросы SQL. Язык запросов SQL

Язык запросов SQL. DML.

Подзапрос – это запрос, результат которого используется внутри другого запроса. В большинстве случаев (кроме использования специальных булевых операторов – см. ниже) требуется, чтобы результатом подзапроса было одно поле. Например, пусть требуется вывести список товаров, проданных на сумму свыше 90000:

SELECT GName FROM Goods WHERE GNum in (SELECT GNum FROM Sells WHERE SSum>90000)

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

Gname

Сахар

Мыло

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

Для работы с подзапросами также используются реляционные операторы, булевы операторы, и специальные операторы EXIST, ANY, ALL и SOME.

Оператор EXISTS в запросе используется в тех случаях, когда необходимо проверить результат подзапроса на его существование, т.е. выбрал подзапрос что-нибудь из базы данных или нет. Он может работать автономно в предикате или в комбинации с другими логическими выражениями, использующими булевы операторы AND, OR, и NOT. Оператор EXISTS берет подзапрос как аргумент и оценивает его как верный, если тот производит любой вывод, или как неверный, если тот не делает этого. Например, пусть необходимо выбрать только те товары, которые были проданы на сумму выше, чем цена. Напишем данный запрос с помощью оператора EXISTS:

SELECT Goods.GName FROM Goods WHERE EXISTS (SELECT * FROM Sells WHERE Sells.SSum>Goods.Gprice AND Sells.GNum=Goods.GNum)

Получим:

Gname

Сахар

Мука

Мыло

Пиво

Фен «Philips»

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

EXISTS всегда можно заменить на оператор IN, что, однако, приводит к замедлению работы запроса.

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

Например, выведем только те группы товаров, для которых определены товары:

SELECT * FROM GoodsTypes WHERE GTNum=ANY(SELECT GoodsTypesFROM Goods)

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

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

SELECT * FROM Buyers WHERE BNum<>ALL(SELECT BNum FROM Sells WHERE SDate="05.05.01")

Этот запрос работает следующим образом. Сначала подзапрос в ALL выбирает номера всех покупателей, совершивших покупки 05.05.01. Затем он находит номера покупателей не равных выбранным и выводит их в результате:

BNum	BName	BTown
1	Дельта	1
5	Москва	3

Операторы ANY и ALL допускают использование EXISTS для альтернативного формулирования такого же запроса.

Оператор ALL редко используется со знаком «равно», т.к. предикат может быть верным, если сравниваемое значение равно для всех, т.е. все записи должны быть идентичны. Например, если в предыдущем запросе вместо знака неравенства поставить равенство, то вывод запроса осуществится только в том случае, если номера покупателей, совершивших покупки в этот день, будут совпадать. Основное применение оператор ALL находит со знаками неравенства.

openu.ru

sql-server - Подзапросы SQL в разделе FROM

Я понимаю, что подзапросы заведомо плохи для производительности при неправильном использовании. У меня очень специфический сценарий, когда пользователю нужно получить отфильтрованный набор записей из таблицы. Широкий спектр фильтров будет доступен, и они должны поддерживать композицию. Более того, новые фильтры будут создаваться на регулярной основе группой разработчиков.

Мне не нравится идея одного растущего монолитного SQL-запроса с большим количеством параметров. Мне не нравится идея создания автономных SQL-запросов с идентичными операторами SELECT и различными предложениями WHERE. Мне нравится идея динамического SQL-запроса, но я не уверен, какую структуру я должен использовать. Я могу подумать о четырех основных вариантах: (если есть больше, что мне не хватает, то, пожалуйста, не стесняйтесь предлагать их)

"INNER JOIN": объединить фильтры через INNER JOINS для фильтрации результатов.
"FROM подзапросы": стек фильтрует через подзапросы в выражении FROM.
"WHERE subqueries": Contatenate фильтрует через подзапросы в предложении WHERE.

"Подзапросы INNER JOIN": гибрид wierd.

Я создал скрипт SQL для демонстрации (и профиля):

http://sqlfiddle.com/#!3/4e17b/9

Ниже приводится выдержка из скрипки, чтобы дать представление о том, о чем я говорю:

------------------------------------------------------------------------ --THIS IS AN EXCERPT FROM THE SQL FIDDLE -- IT IS NOT MEANT TO COMPILE-- ------------------------------------------------------------------------ -- --"INNER JOIN" test SELECT COUNT(*) FROM @TestTable Test0 INNER JOIN @TestTable Test1 ON Test1.ID=Test0.ID AND Test1.ID % @i = 0 INNER JOIN @TestTable Test2 ON Test2.ID=Test0.ID AND Test2.ID % @j = 0 INNER JOIN @TestTable Test3 ON Test3.ID=Test0.ID AND Test3.ID % @k = 0 -- --"FROM subqueries" test SELECT COUNT(*) FROM ( SELECT * FROM ( SELECT * FROM ( SELECT * FROM @TestTable Test3 WHERE Test3.ID % @k = 0 ) Test2 WHERE Test2.ID % @j = 0 ) Test1 WHERE Test1.ID % @i = 0 ) Test0 -- --"WHERE subqueries" test SELECT COUNT(*) FROM @TestTable Test0 WHERE Test0.ID IN (SELECT ID FROM @TestTable Test1 WHERE Test1.ID % @i = 0) AND Test0.ID IN (SELECT ID FROM @TestTable Test2 WHERE Test2.ID % @j = 0) AND Test0.ID IN (SELECT ID FROM @TestTable Test3 WHERE Test3.ID % @k = 0) -- --"INNER JOIN subqueries" test SELECT COUNT(*) FROM TestTable Test0 INNER JOIN (SELECT ID FROM TestTable WHERE ID % @i = 0) Test1 ON Test1.ID=Test0.ID INNER JOIN (SELECT ID FROM TestTable WHERE ID % @j = 0) Test2 ON Test2.ID=Test0.ID INNER JOIN (SELECT ID FROM TestTable WHERE ID % @k = 0) Test3 ON Test3.ID=Test0.ID -- --"EXISTS subqueries" test SELECT COUNT(*) FROM TestTable Test0 WHERE EXISTS (SELECT 1 FROM TestTable Test1 WHERE Test1.ID = Test0.ID AND Test1.ID % @i = 0) AND EXISTS (SELECT 1 FROM TestTable Test2 WHERE Test2.ID = Test0.ID AND Test2.ID % @j = 0) AND EXISTS (SELECT 1 FROM TestTable Test3 WHERE Test3.ID = Test0.ID AND Test3.ID % @k = 0)

Рейтинги (время выполнения тестов)

SQL Fiddle:

|INNER JOIN|FROM SUBQUERIES|WHERE SUBQUERIES|INNER JOIN SUBQUERIES|EXISTS SUBQUERIES| ------------------------------------------------------------------------------------- | 5174 | 777 | 7240 | 5478 | 7359 |

Локальная среда: (без кеша: сброс буфера перед каждым тестом)

Локальная среда: (с кешем: выполнение запросов дважды в строке и запись времени второго запуска)

В каждом решении есть преимущества/недостатки. Подзапросы в предложении WHERE имеют довольно ужасную производительность. Подзапросы в предложении FROM имеют довольно хорошую производительность (на самом деле они обычно работают лучше) (ПРИМЕЧАНИЕ. Я считаю, что этот метод отрицает преимущества индексов?). INNER JOINs имеют довольно хорошую производительность, хотя в ней представлены некоторые интересные проблемы, поскольку в отличие от подзапросов INNER JOINs будут работать в одном и том же контексте (для предотвращения столкновений табличных псевдонимов должна быть промежуточная система).

В целом я считаю, что самым чистым решением является подзапросы в предложении FROM. Фильтры легко записывать и тестировать (потому что в отличие от INNER JOINs им не нужно будет предоставлять контекстный/базовый запрос).

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

ОБНОВЛЕНИЕ (2012/10/04):

Обновлен скрипт SQL для включения теста для метода "EXISTS"
Добавлен тест производительности из SQL Fiddle и локальной среды

qaru.site