Sql all не работает

Использование ключевых слов SOME (ANY) и ALL с предикатами сравнения

SOME и ANY являются синонимами, то есть может использоваться любое из них. Результатом подзапроса является один столбец величин. Если хотя бы для одного значения V, получаемого из подзапроса, результат операции » оператор сравнения > V » равняется TRUE , то предикат ANY также равняется TRUE .

Исполняется так же, как и ANY , однако значение предиката ALL будет истинным, если для всех значений V, получаемых из подзапроса, предикат » V » дает TRUE .

Найти поставщиков компьютеров, моделей которых нет в продаже (то есть модели этих поставщиков отсутствуют в таблице PC)

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

Maker E

Рассмотрим подробно этот пример. Предикат

вернет значение TRUE , если модель, определяемая полем model основного запроса, найдется в списке моделей таблицы РС (возвращаемом подзапросом). Поскольку предикат используется в запросе с отрицанием NOT , то значение TRUE будет получено, если модели не окажется в списке. Этот предикат проверяется для каждой записи основного запроса, которыми являются все модели ПК (предикат type = ‘pc’) из таблицы Product. Результирующий набор состоит из одного столбца — имени производителя. Чтобы один производитель не выводился несколько раз (что может случиться, если он производит несколько моделей, отсутствующих в таблице РС), используется служебное слово DISTINCT , исключающее дубликаты.

Найти модели и цены портативных компьютеров, стоимость которых превышает стоимость любого ПК

model Price 1298 1050 1750 1200 1752 1150

Приведем формальные правила оценки истинности предикатов, использующих параметры ANY|SOME и ALL.

• Если определен параметр ALL или SOME и все результаты сравнения значения выражения и каждого значения, полученного из подзапроса, являются TRUE, истинностное значение равно TRUE.
• Если результат выполнения подзапроса не содержит строк и определен параметр ALL, результат равен TRUE. Если же определен параметр SOME, результат равен FALSE.
• Если определен параметр ALL и результат сравнения значения выражения хотя бы с одним значением, полученным из подзапроса, является FALSE, истинностное значение равно FALSE.
• Если определен параметр SOME и хотя бы один результат сравнения значения выражения и значения, полученного из подзапроса, является TRUE, истинностное значение равно TRUE.
• Если определен параметр SOME и каждое сравнение значения выражения и значений, полученных из подзапроса, равно FALSE, истинностное значение тоже равно FALSE.
• В любом другом случае результат будет равен UNKNOWN .

Источник

SQL Операторы ANY и ALL

SQL ANY и ALL

Операторы ANY и ALL используются с предложением WHERE или HAVING.

Оператор ANY возвращает true, если какое-либо из значений подзапроса удовлетворяет условию.

Оператор ALL возвращает true, если все значения подзапроса удовлетворяют условию.

Синтаксис ANY

Синтаксис ALL

Примечание: Оператор должен быть стандартным оператором сравнения (=, <>, !=, >, >=,

Демо база данных

Ниже приведен выбор из таблицы «Products» в образце базы данных Northwind:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 — 12 oz bottles	19
3	Aniseed Syrup	1	2	12 — 550 ml bottles	10
4	Chef Anton’s Cajun Seasoning	2	2	48 — 6 oz jars	22
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35

И выбор из таблицы «OrderDetails»:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	51	40

Примеры SQL ANY

Оператор ANY возвращает TRUE, если какое-либо из значений подзапроса удовлетворяет условию.

Следующий оператор SQL возвращает TRUE и перечисляет названия продуктов, если он находит какие-либо записи в таблице OrderDetails, что количество = 10:

Пример

Следующий оператор SQL возвращает TRUE и перечисляет названия продуктов, если он находит какие-либо записи в таблице OrderDetails, что количество > 99:

Пример

Пример SQL ALL

Оператор ALL возвращает TRUE, если все значения подзапроса удовлетворяют условию.

Следующая инструкция SQL возвращает TRUE и перечисляет названия продуктов, если все записи в таблице OrderDetails имеют значение quantity = 10 ( таким образом, этот пример возвращает FALSE, поскольку не все записи в таблице OrderDetails имеют значение quantity = 10):

Источник

Функции ALL и ANY в SQL: больше всех, равно хотя бы какому-либо

Оглавление

• Действие кванторных функций SQL ALL и ANY
• ALL в SQL: больше всех
• ANY в SQL: равно хотя бы какому-либо

Связанные темы

• Оператор SELECT
• Реляционная алгебра и её операции

Назад >>

Действие кванторных функций SQL ALL и ANY

Функции SQL ALL и ANY называются кванторными функциями. Аргументом такой функции является множество значений некоторого учитываемого столбца в подзапросе вида

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

По аналогии поясним действие функции ANY. Часть запроса

может быть прочитана как «хотя бы какое-либо значения учитываемого столбца». У функции ANY есть синоним — SOME (действует полностью идентично).

Функции ALL и ANY применяются с операторами сравнения (>, =, Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

ALL в SQL: больше всех

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

Работаем с базой данных «Недвижимость». Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Таблица Object содержит данные об объектах, причём Space_Total — это общая площадь объекта, а District — район, в котором он находится. Таблица Deal содержит данные о сделках, причём значение столбца Type может быть или Sale (продажа), или Rent (аренда).Таблица Client содержит данные соответственно о клиентах.

Пример 1. Требуется получить общую площадь и районы объектов, у которых общая площадь больше общей площади всех (любого из) объектов, расположенных в районе «Сосновка». Пишем запрос с использованием функции ALL:

В нашей базе данных объекты, расположенные в Сосновке, имеют значения общей площади 120, 33, 60, 44, 33. При помощи сравнения со всем множеством этих значений получена следующая выборка:

Space_Total	District
146	Волжский
210	Волжский

ANY в SQL: равно хотя бы какому-либо

Функция ANY (или её полный аналог SOME) применяется для получения выборки, характеризуемой значениями учитываемого столбца, которые равны хотя бы какому-либо из значений того же столбца другой выборки, которая извлекается подзапросом.

Пример 2. Требуется найти клиентов, которые заключили сделки на аренду недвижимости. Напомним: в таблице Deal (сделка) значение столбца Type может быть или Sale (продажа), или Rent (аренда). Пишем запрос с использованием функции ANY, в котором основной запрос обращён к таблице CLIENT, а подзапрос — к таблице DEAL:

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

Client_ID

3

8

Сравнение с результатом, возвращаемым функцией ANY можно инвертировать при помощи ключевого слова NOT. Тогда прочтение смысла запроса с использованием этой функции будет следующим: «не равно ни одному из каких-либо».

Пример 3. Требуется найти объекты, с которыми не были заключены сделки. Пишем запрос с использованием функции ANY, в котором основной запрос обращён к таблице OBJECT, а подзапрос — к таблице DEAL:

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

Obj_ID

13

Примеры запросов к базе данных «Недвижимость» есть также в уроках по операторам IN, GROUP BY, предикату EXISTS, функциям ALL и ANY и LIMIT.

Источник

10 потенциальных SQL ошибок, которые делают программисты

Оригинал статьи носит название «10 SQL ошибок, которые делают Java разработчики», но, по большому счёту, приведённые в ней принципы можно отнести к любому языку.

Java программисты мешают объектно-ориентированное и императивное мышление в зависимости от их уровня:
— мастерства (каждый может программировать императивно)
— догмы (шаблон для применения шаблонов где-либо и их именование)
— настроения (применять истинный объектный подход немного сложнее чем императивный)

Но всё меняется, когда Java разработчики пишут SQL код.
SQL — это декларативный язык, который не имеет ничего общего с объектно-ориентированным или императивным мышлением. Очень легко выразить запрос в SQ, но довольно трудно выразить его корректно и оптимально. Разработчикам не только необходимо переосмыслить их парадигму программирования, им нужно ещё и думать в рамках теории множеств (set theory).

Ниже перечислены общие ошибки, которые делают Java разработчики, использующие SQL в JDBC или jOOQ (без определённого порядка). Для других 10 ошибок, смотрите эту статью.

1. Забыл о NULL

Непонимание NULL — это скорее всего самая большая ошибка, которую Java разработчик может сделать, когда пишет SQL. Это может быть потому, что NULL ещё называется UNKNOWN. Если бы он назывался просто UNKNOWN, его было бы проще понять. Другая причина в том, что при получении данных и связывании переменных JDBC отражает SQL NULL в Java null. Это может привести к тому, что NULL = NULL (SQL) будет вести себя так же, как и null == null (JAVA).

Другая, более специфическая проблема появляется при отсутствии понимания значения NULL в NOT IN anti-joins.

Лекарство:
Тренируй себя. Ничего сложного — во время написания SQL всегда думай о NULL:
— Этот предикат корректен относительно NULL?
— Влияет ли NULL на результат этой функции?

2. Обработка данных в памяти Java

Не многие Java программисты знают SQL очень хорошо. Случайный JOIN, странный UNION и ладно. А оконные функции? Группирующие наборы? Многие Java разработчики загружают SQL данные в память, трансформируют их в какую-нибудь подходящую коллекцию и выполняют нужные вычисления на этих коллекциях с многословными циклическими структурами (по-крайней мере до улучшения коллекций в JAVA 8).

Но некоторые SQL базы данных поддерживают дополнительные (SQL стандарт!) OLAP функции, которые подходят для этого лучше и являются более простыми в написании. Один из примеров (не стандарт) — это отличный оператор MODEL от Oracle. Просто позволь БД сделать обработку и вытащить результаты в память Java. Потому что, в конце концов, какой-то умный парень уже оптимизировал эти дорогие продукты. Итак, используя OLAP в БД, ты получаешь две вещи:
— Простоту. Скорее всего, проще писать правильно на SQL, чем на Java.
— Производительность. БД скорее всего будут быстрее чем твой алгоритм. И, что важнее, тебе не придётся тянуть миллионы записей по проводам.

Лекарство:
Каждый раз когда ты пишешь ориентированный на данные алгоритм с помощью Java, спрашивай себя: «Есть ли возможность переложить эту работу на базу данных?»

3. Использование UNION вместо UNION ALL

Позор тому, что UNION ALL требует дополнительного слова относительно UNION. Было бы намного лучше, если бы SQL стандарт был определён поддерживать:
— UNION (позволяет дублирование)
— UNION DISTINCT (убирает дублирование)

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

Помни, что даже если SQL стандарт определяет INTERSECT ALL и EXCEPT ALL, не каждая БД может реализовывать эти мало используемые наборы операций.

Лекарство:
Думай, хотел ли ты написать UNION ALL каждый раз, когда пишешь UNION.

4. Использование JDBC для постраничной разбивки большой выборки

Большинство БД поддерживают какие-то средства для постраничной разбивки через LIMIT… OFFSET, TOP… START AT, OFFSET… FETCH операторов. В отсутствии поддержки этих операторов всё ещё есть возможность наличия ROWNUM (Oracle) или ROW_NUMBER() OVER() фильтрации (DB2, SQL Server 2008 и другие), которые намного быстрее разбивки в памяти. Это относится преимущественно к большим смещениям!

Лекарство:
Просто используйте эти операторы, или инструмент(такой, как jOOQ), который может имитировать эти операторы за вас.

5. Соединение данных в памяти Java

С ранних дней SQL и до сих пор некоторые Java программисты с тяжелым сердцем пишут JOINы. У них есть устаревший страх того, что JOINы выполняются медленно. Это может быть так, если оптимизатор накладных расходов выбирает сделать вложенный цикл, загружая целые таблицы в память перед созданием ячеек присоединённой таблицы. Но это случается редко. С нормальными предикатами, ограничениями, индексами, MERGE JOIN или HASH JOIN операции выполняются очень быстро — всё зависит от корректных метаданных (Tom Kyte хорошо написал об этом). Тем не менее, наверняка ещё остались немногие Java разработчики, которые загружают две таблицы двумя отдельными запросами и соединяют их в памяти Java тем или иным способом.

Лекарство:
Если бы выбираете из разных таблиц на различных этапах, подумайте ещё раз, вдруг вы можете выразить ваши запросы одним.

6. Использование DISTINCT или UNION для удаления дубликатов из случайного декартова произведения

Из-за сложных соединений (JOIN) любой разработчик может потерять след в значащих связях SQL запроса. Если конкретнее, то при использовании связи с составными внешними ключами можно забыть добавить значащие предикаты в JOIN… ON утверждения. Это может привести к дублированию строк всегда или только в исключительных ситуациях. Тогда некоторые разработчики могут добавить оператор DISTINCT для прекращения дублирования данных. Это не правильно по трём причинам:
— Это может излечить последствия, но не причину. А ещё это может не решить последствия при граничных условиях.
— Это медленно для больших выборок. DISTINCT выполняет ORDER BY операцию для удаления дублирования.
— Это медленно для больших декартовых произведений которые всё равно будут загружены в память.

Лекарство:
Как правило, если Вы получаете нежелательные дубликаты, пересмотрите свои JOIN предикаты. Вероятно там где-то образовалось небольшое декартово произведение.

7. Избегание оператора MERGE

На самом деле это не ошибка, но, возможно, это отсутствие знаний или страхи мощного оператора MERGE. Некоторые БД знают другие формы UPSERT оператора, например MySQL ON DUPLICATE KEY UPDATE. На самом деле MERGE очень мощен, особенно в БД, которые сильно расширяют SQL стандарт, таких как SQL Server.

Лекарство:
Если Вы делаете UPSERT, выстраивая цепочку из INSERT и UPDATE или SELECT… FOR UPDATE и INSERT/UPDATE, задумайтесь ещё раз. Вместо риска гонки за ресурсами, вы можете написать более простое MERGE запрос.

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

Перед появлением оконных функций, единственным средством для агрегации данных в SQL было использование GROUP BY вместе с агрегатными функциями в проекции. Это хорошо работает в большинстве случаев, и если агрегированные данные должны быть наполнены обычными данными, то сгруппированный запрос может быть написан в присоединённом под запросе.
Но SQL:2003 определяет оконные функции, которые реализованы многими поставщиками БД. Оконные функции могут агрегировать данные на не группированных выборках. По факту, каждая оконная функция поддерживает свою собственную, независимую PARTITION BY операцию, которая является отличным инструментом для построения отчётов.

Использование оконных функций позволит:
— Построить более читаемый SQL (меньше выделенных GROUP BY выражений в под запросах)
— Улучшить производительность т.к. RDBMS может легче оптимизировать оконные функции

Лекарство:
Когда вы пишите GROUP BY выражение в под запросе, задумайтесь, может ли он быть выражен оконной функцией?

9. Использование сортировки в памяти при разных параметрах

Оператор ORDER BY поддерживает множество типов выражений, включая CASE, который может быть очень полезен при определении параметра сортировки. Вам никогда не следует сортировать данные в памяти Java только потому, что:
— SQL сортировка слишком медленная.
— SQL сортировка не может сделать этого.

Лекарство:
Если вы сортируете какие-либо SQL данные в памяти Java, задумайтесь, возможно ли перенести эту сортировку в БД? Это отлично сочетается со страничной разбивкой в БД.

10. Поочерёдная вставка множества записей

JDBC знает, что такое пакет (batch), и Вам следует использовать это. Не делайте INSERT тысяч записей одной за другой, создавая новый PreparedStatement каждый раз. Если все ваши записи идут в одну таблицу, создайте партию INSERT запросов с одним SQL запросом и несколькими связываемыми наборами данных. В зависимости от вашей БД и её конфигурации, что бы сохранить UNDO лог чистым, Вам может потребоваться делать commit спустя какое-то количество вставленных записей.

Лекарство:
Всегда используйте пакетную вставку больших наборов данных.

Источник