Статьи

Замечательная функция SQL: количественные предикаты сравнения (ЛЮБОЙ, ВСЕ)

Декабрь 26, 2018

Вы когда-нибудь задумывались о сценарии использования 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 .
56
Share: