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

jOOQ in the wild

The first open source projects start to appear on the web with public dependencies on jOOQ. One of them is a small backup tool called blizzys-backup by Maik Schreiber. It uses jOOQ to handle a small H2 database with 3-4 relations describing scheduled backups and backup-file meta data. Interestingly, Maik seems to prefer lazy fetching his data using an org.jooq.Cursor, keeping the underlying java.sql.ResultSet open during iterations. An example code snippet from the BackupRun class can be seen here:

// below code (c) by Maik Schreiber (slightly modified)
Cursor<Record> cursor = null;
try {
  cursor = database.factory()
    .select(Backups.ID)
    .from(Backups.BACKUPS)
    .where(Backups.ID.notEqual(Integer.valueOf(backupId)))
    .orderBy(Backups.RUN_TIME.desc())
    .fetchLazy();

  while (cursor.hasNext()) {
    int backupId = cursor.fetchOne().getValue(Backups.ID).intValue();

    // [...]
  }

  // Note: The above loop could be re-written to this
  // as Cursor<R extends Record> extends Iterable<R>:
  for (Record record : cursor) {
    int backupId = record.getValue(...);
  }
} finally {
  database.closeQuietly(cursor);
}

Another example:

// below code (c) by Maik Schreiber (slightly modified)
Cursor<Record> cursor = null;
try {
  cursor = database.factory()
    .select(Files.ID,
            Files.BACKUP_PATH)
    .from(Files.FILES)
    .leftOuterJoin(Entries.ENTRIES)
      .on(Entries.FILE_ID.equal(Files.ID))
    .where(Entries.FILE_ID.isNull())
    .fetchLazy();

  while (cursor.hasNext()) {
    Record record = cursor.fetchOne();
    FileEntry file = new FileEntry(
      record.getValue(Files.ID).intValue(),
      record.getValue(Files.BACKUP_PATH));

    // [...]
  }
} finally {
  database.closeQuietly(cursor);
}

blizzys-backup auto-installs its schema at the first run. This can be seen in the Database class. It shows that jOOQ’s planned support for DDL statements is going to add even more value to jOOQ, as this is a real need for many open-source tools. Some sample statement (today, an example of going back to string concatenation…):

factory.query("CREATE TABLE IF NOT EXISTS backups (" + 
                "id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, " +
                "run_time DATETIME NOT NULL, " +
                "num_entries INT NULL" +
              ")").execute();

See more of blizzys-backup here:

https://github.com/blizzy78/blizzys-backup/