Add support for the relational division operation: A.divideBy(B).on(A.ID.equal(B.A_ID)).returning(A.X, A.Y, ...)

[lukaseder]

Some background facts:

• http://en.wikipedia.org/wiki/Relational_algebra#Division

Some SQL examples:

• http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
• http://www.cs.arizona.edu/~mccann/research/divpresentation.pdf

[delostilos]

The documentation of the divideBy operator seems to suggest that relational division is implemented via a double nested not exists. This blog post seems to have an easier en speedier implementation: http://gregorulm.com/relational-division-in-sql-the-easy-way/
It references this paper: http://users.dcc.uchile.cl/~cgutierr/cursos/BD/divisionSQL.pdf
Mayby it is an idea to use this method to speed up relational devision performance?

[lukaseder]

Yes, I am aware of this alternative way of calculating and (exact) relational division (#1205). jOOQ currently only implements relational division with remainder, which can only be expressed with doubly nested NOT EXISTS predicates, I think.

I'm curious about the publication date of the paper you referenced. They claim to "be proposing" this alternative way, even if it is cited quite often in literature and blogs, e.g. in both links I've added to the issue. In fact, their proposal is not entirely correct in (non-relational) corner cases, where the dividend or the divisor contains duplicates. A more correct variant would involve using COUNT(DISTINCT B)

Also, I'd be curious about a realistic benchmark backing the claim that using HAVING and COUNT(*) is actually faster.

Clearly, the readability of the produced SQL using HAVING and COUNT() is much better, though!

[delostilos]

Hi Lukas,

I came across this post: http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx
It seems they have a different way to calculate the devision with reminder. Maybe it can be of use for jOOQ :-)

Regards,
JJ.

[lukaseder]

@delostilos : Thanks for sharing, that's very interesting. If only there was an actual SQL syntax for this kind of construct, though... :)