CROSS JOIN, a nice example for a rarely used operation

In 95% of the cases, cartesian products originate from accidental cross join operations and cause unnecessary high load on a database. Maybe the results aren’t even wrong, as someone may have applied a UNION or a DISTINCT keyword, to remove unwanted duplicates.

But there are those 5% of SQL queries, where the cartesian product is intentional, even desireable. Here’s a nice example of such a query. Let’s assume we have these two tables (for simplicity, constraints are omitted):

create table content_hits (
  content_id int,
  subscriber_id int
);

create table content_tags (
  content_id int,
  tag_id int
);

The above tables model some blog content, where content_hits shows how many subscribers are concerned with a given blog entry, and content_tags shows which tag is applied to a given blog entry. So let’s say, we want to have some statistics about how many times any given subscriber/tag combination was actually seen. We cannot just join and group by subscriber_id, tag_id if we want the complete list of combinations (including the ones that have a zero count). So a cross join comes to the rescue!

SELECT combinations.tag_id,
       combinations.subscriber_id,

-- correlated subquery to count the actual hits by tag/subscriber when
-- joining the two tables using content_id

       (SELECT count(*)
        FROM content_hits AS h
        JOIN content_tag AS t ON h.content_id = t.content_id
        WHERE h.subscriber_id = combinations.subscriber_id
        AND t.tag_id = combinations.tag_id) as cnt

-- Create all combinations of tag/subscribers first, before counting
-- anything. This will be necessary to have "zero-counts" for any
-- combination of tag/subscriber

FROM (
  SELECT DISTINCT tag_id, subscriber_id
  FROM content_tag
  CROSS JOIN content_hits
) AS combinations

Instead of using a correlated subquery, for performance reasons (depending on the database) it might be more interesting to left outer join the first subquery to the “combinations” relation and then group by subscriber_id, tag_id, and count(content_id) for every group, resulting in this query:

SELECT combinations.tag_id,
       combinations.subscriber_id,
       count(content_relation.content_id)

-- Create all combinations of tag/subscribers first, before counting
-- anything. This will be necessary to have "zero-counts" for any
-- combination of tag/subscriber

FROM (
  SELECT DISTINCT tag_id, subscriber_id
  FROM content_tag
  CROSS JOIN content_hits
) AS combinations

-- Now, outer join every expected combination to its 
-- actual relation joined on content_id

LEFT OUTER JOIN (
  SELECT h.content_id, 
         h.subscriber_id,
         t.tag_id
  FROM content_hits AS h
  JOIN content_tag AS t ON h.content_id = t.content_id
) AS content_relation
ON combinations.tag_id = content_relation.tag_id
AND combinations.subscriber_id = content_relation.subscriber_id

-- And now, finally, group, in order to get the count(content_id)
GROUP BY combinations.tag_id,
         combinations.subscriber_id

The original Stack Overflow question can be seen here:

http://stackoverflow.com/questions/9924433/mysql-select-query-using-count

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?

Let’s have a look at the sample provided on Wikipedia:

Wikipedia example of a relational division

Wikipedia example of a relational division

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:

SELECT DISTINCT "c1".Student FROM Completed "c1"
WHERE NOT EXISTS (
  SELECT 1 FROM DBProject
  WHERE NOT EXISTS (
    SELECT 1 FROM Completed "c2"
    WHERE "c2".Student = "c1".Student
    AND "c2".Task = DBProject.Task
  )
)

Now, no one sane wants to remember this just for the 1-2 times in a SQL developer’s life that they actually need it. So they use jOOQ, which wraps up the above monster in a concise syntax:

create.select().from(
  Completed.divideBy(DBProject)
           .on(Completed.Task.equal(DBProject.Task))
           .returning(Completed.Student)
);

Note that from the above SQL statement, it is immediately clear that proper indexing is of the essence. Be sure to have indexes on all columns referenced from the on(…) and returning(…) clauses.

More information

For more information about relational division and some nice, real-life examples, see

jOOQ compared to iBatis

Nice feedback from a jOOQ user comparing it with iBATIS:

jOOQ rocks! We implemented a large scale ETL service with it, and couldn’t be happier. I’ve used iBATIS, and jOOQ gives all the same advantages of low level SQL without the reams of XML and lack of type safety.

[…] one of the best features of jOOQ: Complex and/or dynamically generated queries can be assembled in a controlled way and verified by the Java compiler, saving you from all those ambiguous syntax errors databases throw, and providing compile-time regression coverage for when the schema changes under you.

Feedback by Stack Overflow user dacc on Stack Overflow:

http://stackoverflow.com/questions/296587/light-weight-alternative-to-hibernate/4522144#comment12069361_4522144