Статьи

INTERSECT — недооцененный двусторонний IN Predicate

Март 5, 2019

Задумывались ли вы, как вы можете выразить предикат, который «чувствует», как в SQL:

1
WHERE Var1 OR Var2 IN (1, 2, 3)

/ U / CyBerg90 имеет, на Reddit . Идея состояла в том, чтобы создать предикат, который возвращает истину всякий раз, когда оба значения Var1 и Var2 дают 1, 2 или 3.

Каноническое решение

Каноническим решением, очевидно, было бы записать все это так:

1
2
WHERE Var1 = 1 OR Var1 = 2 OR Var1 = 3
OR    Var2 = 1 OR Var2 = 2 OR Var2 = 3

Много дублирования, хотя.

Использование предикатов IN

Большинство читателей просто связывают два предиката IN :

1
2
WHERE Var1 IN (1, 2, 3)
OR    Var2 IN (1, 2, 3)

Или умные могут обратить предикаты как таковые, чтобы сформировать эквивалент:

1
2
3
WHERE 1 IN (Var1, Var2)
OR    2 IN (Var1, Var2)
OR    3 IN (Var1, Var2)

Хорошее решение с использованием EXISTS и JOIN

Все предыдущие решения в некоторой степени требуют повторения синтаксиса / выражения. Хотя это может не оказать существенного влияния на производительность, оно может определенно взорваться с точки зрения длины выражения. Лучшие решения (с этой точки зрения) используют предикат EXISTS , создавая специальные наборы, которые не являются пустыми, когда и Var1 и Var2 дают 1, 2 или 3.

Вот существует с JOIN

1
2
3
4
5
6
WHERE EXISTS (
    SELECT 1
    FROM (VALUES (Var1), (Var2)) t1(v)
    JOIN (VALUES (1), (2), (3)) t2(v)
    ON t1.v = t2.v
)

Это решение создает две таблицы с одним значением, объединяя их по этому значению:

1
2
3
4
5
6
7
+------+    +------+
| t1.v |    | t2.v |
+------+    +------+
| Var1 |    |    1 |
| Var2 |    |    2 |
+------+    |    3 |
            +------+

Глядя на диаграмму Венна , легко увидеть, как JOIN будет генерировать только те значения из t1 и t2 , которые присутствуют в обоих наборах:

пересекаются

Самое красивое решение с использованием EXISTS и INTERSECT

Тем не менее, люди могут не думать о пересечении множества при чтении JOIN . Так почему бы не использовать фактическое пересечение множеств через INTERSECT ? Вот, на мой взгляд, самое хорошее решение:

1
2
3
4
5
6
7
WHERE EXISTS (
    SELECT v
    FROM (VALUES (Var1), (Var2)) t1(v)
    INTERSECT
    SELECT v
    FROM (VALUES (1), (2), (3)) t2(v)
)

Заметьте, как длина оператора SQL увеличивается с ростом O(m + n) (или просто O(N) , где m, n = number of values in each set , тогда как исходные решения, использующие IN увеличиваются с O(m * n) (или просто O(N 2 ) ).

INTERSECT Поддержка в популярных RDBMS

INTERSECT широко поддерживается как в стандарте SQL, так и в любой из следующих RDBMS, которые поддерживаются jOOQ :

  • CUBRID
  • DB2
  • дерби
  • H2
  • HANA
  • HSQLDB
  • Informix
  • Энгр
  • оракул
  • PostgreSQL
  • SQLite
  • SQL Server
  • Sybase ASE
  • Sybase SQL Anywhere

Фактически, следующие базы данных также поддерживают менее часто используемый INTERSECT ALL , который не удаляет повторяющиеся значения из полученных пакетов (см. Также UNION против UNION ALL )

  • CUBRID
  • PostgreSQL

Счастливого пересекающегося!

Ссылка: INTERSECT — недооцененный двусторонний прогноз от нашего партнера по JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ .
46
Share: