Забава преобразования запроса SQL: предикаты с выражениями значения строки

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

• Выражение значения строки и предикат BETWEEN
• Выражение значения строки и предикат NULL
• Сравнение типов безопасности API-интерфейсов SQL Access

Выражения значений строк (или записи, кортежи) полезны для выражения более сложных предикатов, таких как этот:

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)

Вышеупомянутое утверждение частично присоединяется uк tоснованному на сравнении кортежей, а не просто сравнении одного столбца Это полезно, например, когда вы хотите выбрать тех пользователей из таблицы, чье имя И фамилия также содержатся в другой таблице (конечно, без каких-либо формальных отношений внешнего ключа):

SELECT *
FROM users u
WHERE (u.first_name, u.last_name) IN (
    SELECT a.first_name, a.last_name FROM addresses a
)

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

• DB2
• HSQLDB
• MySQL
• оракул
• Postgres

И эти базы данных делают вид, что они реализуют предикаты выражения значения строки, но ошибаются:

• CUBRID (путая их с наборами)
• H2 (путая их с массивами)

Матрица сравнения характеристик приведена здесь:
http://blog.jooq.org/2012/12/26/row-value-expressions-and-the-between-predicate

Можно ли смоделировать вышеуказанный запрос?

Да, оно может! И это будет с JOOQ 3.1 . Вот как преобразовать вышеуказанный запрос в эквивалентный, который не использует выражения значения строки. Итак, вот опять оригинальный запрос:

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)

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

SELECT *
FROM t
WHERE EXISTS (
    SELECT * FROM u
    WHERE t.t1 = u.u1 AND t.t2 = u.u2
)

Теперь в приведенном выше простом преобразовании мы изменили подвыбор, изменив проекцию и добавив предикат. Это может быть сложно для более сложных подвыборов, поэтому давайте не будем касаться этого, введя другую производную таблицу:

SELECT *
FROM t
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT u.u1, u.u2 FROM u -- untouched
    ) v(v1, v2)                  -- derived table
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

Так-то лучше. Многие базы данных требуют переименования производных таблиц, поэтому v(v1, v2)был введен список производных столбцов . Однако не все базы данных поддерживают производные списки столбцов, как видно из предыдущего сообщения в блоге . Итак, давайте продолжим преобразовывать вышеупомянутое в эквивалентный запрос:

SELECT *
FROM t
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2  -- renaming
        FROM dual                -- if necessary
        WHERE 1 = 0              -- without new rows
        UNION ALL
        SELECT u.u1, u.u2 FROM u -- untouched
    ) v                          -- derived table
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

Теперь мы достигли нашей цели. Приведенный выше запрос будет выполняться во всех базах данных, сохраняя исходную семантику предиката выражения значения строки с использованием вложенного выбора! Обратите внимание, вам, возможно, придется заменить двойное на что-то более подходящее, конечно.

Это относится ко всем предикатам сравнения?

В принципе да. Давайте посмотрим на несколько примеров.

НЕ В

SELECT *
FROM t
WHERE (t.t1, t.t2) NOT IN (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
WHERE NOT EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE t.t1 = v.v1 AND t.t2 = v.v2
)

Равенство и неравенство

Равенство и неравенство работают так же, как INи NOT IN IFF, который вы используете для скалярных подселектов. В то время как фактические предикаты сравнения вызовут ошибку, если подвыборы вернут более одной строки, EXISTSпредикат не будет. Осторожно!

заказ

Как и в случае равенства и неравенства, остерегайтесь нескалярных подвыборов!

SELECT *
FROM t
WHERE (t.t1, t.t2) > (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
-- EXISTS is not formally correct,
-- if the subselect is a non-scalar one
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 > v.v1)
    OR    (t.t1 = v.v1 AND t.t2 > v.v2)
)

См. Цитируемое ранее сообщение в блоге о предикате BETWEEN, чтобы узнать, как имитировать предикаты сравнения «упорядочения» с помощью выражений значений строк.

Количественные предикаты сравнения

Квантификаторы теперь становятся весьма полезными. ANYКвантор устраняет необходимость иметь скалярные подзапросы, как и в предыдущем примере:

SELECT *
FROM t
WHERE (t.t1, t.t2) > ANY (
    SELECT u.u1, u.u2 FROM u
)

-- transforms into
SELECT *
FROM t
-- EXISTS is now formally correct
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 > v.v1)
    OR    (t.t1 = v.v1 AND t.t2 > v.v2)
)

ALLКвантор, с другой стороны, может быть выражен с обратным ANYквантором, т.е.

SELECT *
FROM t
WHERE (t.t1, t.t2) > ALL (
    SELECT u.u1, u.u2 FROM u
)

-- first transforms into
SELECT *
FROM t
WHERE (t.t1, t.t2) <= ANY (
    SELECT u.u1, u.u2 FROM u
)

-- and then transforms into
SELECT *
FROM t
-- EXISTS is now formally correct
WHERE EXISTS (
    SELECT *
    FROM (
        SELECT null v1, null v2
        FROM dual
        WHERE 1 = 0
        UNION ALL
        SELECT u.u1, u.u2 FROM u
    ) v
    WHERE (t.t1 < v.v1)
    OR    (t.t1 = v.v1 AND t.t2 <= v.v2)
)