Статьи

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

Март 20, 2018

Задумывались ли вы о сценарии использования SQL ANY( также:)SOME и ALLключевых слов?

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

Интуитивно понятно, что такой количественный предикат сравнения можно использовать как таковой:

-- Is any person of age 42?
42 = ANY (SELECT age FROM person)
 
-- Are all persons younger than 42?
42 > ALL (SELECT age FROM person)

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

-- 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>это указано так:

Точно! Разве SQL не красив? Обратите внимание, что неявные последствия 3)приводят к весьма своеобразному поведению NOT INпредиката в отношенииNULL , о котором мало кто знает.

Теперь это становится потрясающим

Пока что в этом нет ничего необычного <quantified comparison predicate>. Все предыдущие примеры можно эмулировать с помощью «более идиоматического» или, скажем, «более повседневного» SQL.

Но истинная удивительность <quantified comparison predicate>появляется только тогда, когда используется в сочетании с тем, <row value expression>где строки имеют степень / арность больше одного:

-- 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:

-- 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'
)

Как насчет другого предиката?

-- 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 в этой статье здесь.


40
Share: