search

jOOQ / jOOQ

jOOQ is the best way to write SQL in Java
https://www.jooq.org
Other
6.02k stars 1.19k forks source link

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

Closed lukaseder closed 11 years ago

lukaseder commented 11 years ago

Some background facts:

Some SQL examples:

delostilos commented 10 years ago

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 commented 10 years ago

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 commented 9 years ago

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 commented 9 years ago

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