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:
http://blog.jooq.org/2012/12/26/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 (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)
)
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.
