SQL Query Transformation Fun: Predicates with Row Value Expressions

Posted on by lukaseder
Recently, I’ve blogged about how well jOOQ’s supported databases implement row value expressions and predicates formed from them. Some sample articles: Row value expressions (or records, tuples) are useful to express more complex predicates, such as this one: 

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)
The above statement semi-joins u to t based on a tuple comparison, not just a single column comparison. This is useful, for example, when you want to select those users from a table whose first AND last name are also contained in another table (without any formal foreign key relationship, of course): 

SELECT *
FROM users u
WHERE (u.first_name, u.last_name) IN (
    SELECT a.first_name, a.last_name FROM addresses a
)
Now, not all databases really support row value expression predicates. In fact, only very few really do. Here is a non-exhaustive list of databases, that will support some form of the above:
  • DB2
  • HSQLDB
  • MySQL
  • Oracle
  • Postgres
And these databases pretend they implement row value expression predicates, but get it wrong:
  • CUBRID (confusing them with sets)
  • H2 (confusing them with arrays)
A feature comparison matrix was listed here: https://blog.jooq.org/row-value-expressions-and-the-between-predicate

Can the above query be simulated?

Yes, it can! And it will be with jOOQ 3.1. Here’s how to transform the above query into an equivalent one, which doesn’t use row value expressions. So, here’s the original query, again: 

SELECT *
FROM t
WHERE (t.t1, t.t2) IN (
    SELECT u.u1, u.u2 FROM u
)
The above can be transformed into the following query, using an EXISTS predicate 

SELECT *
FROM t
WHERE EXISTS (
    SELECT * FROM u
    WHERE t.t1 = u.u1 AND t.t2 = u.u2
)
Now, in the above simple transformation, we have modified the subselect by changing the projection and by adding a predicate. This can be difficult for more complex subselects, so lets avoid touching it, by introducing another derived table: 

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
)
That’s better. Many databases require renaming derived tables, which is why a derived column list v(v1, v2) was introduced. Not all databases support derived column lists, though, as can be seen in a previous blog post. So lets go on transforming the above into an equivalent query: 

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
)
Now, we’ve reached our goal. The above query will run on all databases, retaining the original semantics of a row value expression predicate using a subselect! Note, you possibly have to replace the dual with something more appropriate, of course.

Does this apply to all comparison predicates?

In principle, yes. Let’s look at a few examples. NOT IN 

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
)
Equality and non-equality Equality and non-equality work the same way as IN and NOT IN IFF you are operating on scalar subselects. While actual comparison predicates will raise an error if subselects return more than one row, the EXISTS predicate will not. Beware! Ordering Just as with equality and non-equality, beware of non-scalar subselects! 

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)
)
See the previously cited blog post about the BETWEEN predicate to learn how to simulate “ordering” comparison predicates with row value expressions. Quantified comparison predicates Quantifiers now become quite useful. The ANY quantifier removes the need for having scalar subselects, as in the previous example: 

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)
)
The ALL quantifier, on the other hand, can be expressed with its inverse ANY quantifier, i.e. 

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

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

-- and then transforms into
SELECT *
FROM t
-- (NOT) EXISTS is now formally correct
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)
    OR    (t.t1 = v.v1 AND t.t2 <= v.v2)
)

Conclusion

Happy transforming, and keep an eye out for jOOQ 3.1, conveniently implementing all of the above behind a type-safe Java API!

Disclaimer

Yes, NULLs. The above transformation deliberately left out edge cases where NULLs are involved.

Published by lukaseder

I made jOOQ

13 thoughts on “SQL Query Transformation Fun: Predicates with Row Value Expressions”

  1. Thanks for great blog posts! Looks like a small mistake has slipped in here

    The ALL quantifier can be expressed with the _negation_ of its inverse ANY quantifier, that is
    expression > ALL (expression)
    becomes
    not expression <= ANY (expression)

    see http://sqlfiddle.com/#!17/7c27d/4

    Reply

    1. You’re right, thanks a lot for the hint. By consequence, the subsequently transformed version using EXISTS should use NOT EXISTS as well. Fixed!

      Reply

Leave a Reply