Вы когда-нибудь задумывались о сценарии использования ANY
( также SOME
) и ALL
ключевых слов SQL?
Вы, вероятно, еще не сталкивались с этими ключевыми словами в дикой природе. Все же они могут быть чрезвычайно полезными. Но сначала давайте посмотрим, как они определены в стандарте SQL . Легкая часть:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
8.7 < quantified comparison predicate> Function Specify a quantified comparison. Format < quantified comparison predicate> ::= < row value constructor> < comp op> < quantifier > < table subquery> < quantifier > ::= < all > | < some > < all > ::= ALL < some > ::= SOME | ANY |
Интуитивно понятно, что такой количественный предикат сравнения можно использовать как таковой:
1
2
3
4
5
|
-- Is any person of age 42? 42 = ANY ( SELECT age FROM person) -- Are all persons younger than 42? 42 > ALL ( SELECT age FROM person) |
Давайте продолжим с полезными. Заметьте, что вы, вероятно, написали вышеупомянутые запросы с другим синтаксисом, как таковой:
1
2
3
4
5
|
-- Is any person of age 42? 42 IN ( SELECT age FROM person) -- Are all persons younger than 42? 42 > ( SELECT MAX (age) FROM person) |
Фактически, вы использовали <in predicate>
или предикате больше, чем <scalar subquery>
и статистическую функцию.
Предикат IN
Это не совпадение, что вы могли использовать <in predicate>
же, как вышеупомянутый <quantified comparison predicate>
используя ANY
. На самом деле, <in predicate>
указывается так:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
8.4 < in predicate> Syntax Rules 2) Let RVC be the < row value constructor> and let IPV be the < in predicate value>. 3) The expression RVC NOT IN IPV is equivalent to NOT ( RVC IN IPV ) 4) The expression RVC IN IPV is equivalent to RVC = ANY IPV |
Точно! Разве SQL не красив? Обратите внимание, что неявные последствия 3)
приводят к очень специфическому поведению предиката NOT IN
отношению к NULL
, о котором мало кто знает.
Теперь это становится потрясающим
Пока что в этом <quantified comparison predicate>
нет ничего необычного. Все предыдущие примеры можно эмулировать с помощью «более идиоматического» или, скажем, «более повседневного» SQL.
Но истинное удивление <quantified comparison predicate>
появляется только при использовании в сочетании с <row value expression>
где строки имеют степень / арность более одного:
1
2
3
4
5
6
|
-- Is any person called "John" of age 42? (42, 'John' ) = ANY ( SELECT age, first_name FROM person) -- Are all persons younger than 55? -- Or if they're 55, do they all earn less than 150'000.00? (55, 150000.00) > ALL ( SELECT age, wage FROM person) |
Посмотрите вышеупомянутые запросы в действии на PostgreSQL в этом SQLFiddle .
На данный момент стоит упомянуть, что на самом деле немногие базы данных поддерживают…
- выражения значения строки, или …
- количественные предикаты сравнения с выражениями значений строк
Даже если это указано в SQL-92 , похоже, что большинству баз данных все еще не хватает времени для реализации этой функции 22 года спустя.
Эмулируя эти предикаты с помощью jOOQ
Но, к счастью, есть jOOQ, чтобы подражать этим функциям для вас. Даже если вы не используете jOOQ в своем проекте, следующие шаги преобразования SQL могут быть полезны, если вы хотите выразить вышеуказанные предикаты. Давайте посмотрим, как это можно сделать в MySQL:
1
2
3
4
5
6
7
8
|
-- This predicate (42, 'John' ) = ANY ( SELECT age, first_name FROM person) -- ... is the same as this: EXISTS ( SELECT 1 FROM person WHERE age = 42 AND first_name = 'John' ) |
Как насчет другого предиката?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
|
-- This predicate (55, 150000.00) > ALL ( SELECT age, wage FROM person) -- ... is the same as these: ---------------------------- -- No quantified comparison predicate with -- Row value expressions available (55, 150000.00) > ( SELECT age, wage FROM person ORDER BY 1 DESC , 2 DESC LIMIT 1 ) -- No row value expressions available at all NOT EXISTS ( SELECT 1 FROM person WHERE (55 < age) OR (55 = age AND 150000.00 <= wage) ) |
Понятно, что предикат EXISTS
можно использовать практически во всех базах данных, чтобы имитировать то, что мы видели раньше. Если вам просто нужно это для эмуляции одним выстрелом, приведенных выше примеров будет достаточно. Однако, если вы хотите более формально использовать <row value expression>
и <quantified comparison predicate>
, вам лучше получить правильное преобразование SQL.
Читайте дальше о преобразовании SQL в этой статье здесь.
Ссылка: | Замечательная функция SQL: количественные предикаты сравнения (ЛЮБОЙ, ВСЕ) от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и JOOQ . |