The above query will generate all the pairings between two players. The same can be written in pre-ANSI SQL-92 with comma-separated table lists:
SELECT p1.name player1, p2.name player2 FROM player p1 CROSS JOIN player p2
The first syntax, however, is more powerful and more expressive as it can be used in nested JOIN expressions and it shows your intent more clearly. A nice example was given here in a previous blog post.
SELECT p1.name player1, p2.name player2 FROM player p1, player p2
How to work around a missing CROSS JOINUsually, missing support for
CROSS JOINcan be emulated trivially using an
INNER JOINwith a
TRUEpredicate as such:
This is what jOOQ does for the Sybase Adaptive Server Enterprise database. But this doesn’t work for MS Access, because the
SELECT p1.name player1, p2.name player2 FROM player p1 JOIN player p2 ON 1 = 1
JOINoperation there explicitly requires column references from either table on either side. The documentation reads:
Syntax:This is quite a bummer from many points of view, not only forFROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
CROSS JOINemulation. Given that any ANSI-92
JOINsyntax can be transformed into an ANSI-86 join expression (table list in the
FROMclause and all predicates in the
WHEREclause), it is also a bit surprising. A simple workaround that seems to work for some use-cases is to take any numeric column from either table, and multiply it by zero:
But if the database itself is already this quirky, I suspect that it might not be able to optimise the above SQL.
SELECT p1.name player1, p2.name player2 FROM player p1 JOIN player p2 ON p1.id * 0 = p2.id * 0
In short…MS Access does not support
CROSS JOIN. For the time being, try to work around it using comma-separated table lists, while we work out more sophisticated SQL transformation in jOOQ.