Advanced SQL: Relational division in jOOQ

Relational algebra has its treats. One of the most academic features is the relational division. It is hardly ever used, but comes in handy every now and then. And when you need it, you’ll probably hate yourself for having slept during the relevant classes at the university.

What is relational division?

Relational division is the inverse of a cross join operation. The following is an approximate definition of a relational division:

Assume the following cross join / cartesian product
C = A × B
Then it can be said that
A = C ÷ B
B = C ÷ A

What does it mean, typically?

This looks sensible. The division of Completed ÷ DBProject leads to a list of students that have completed all projects.

Now how to phrase that in SQL??

That’s not so simple as it looks. The most commonly documented solution involves a doubly-nested select statement using anti-joins. In human language (using double negative), it is like Fred and Sarah saying “there is no DBProject that we have not Completed“. Or in SQL: