Joining on No Fields at All

In my previous tip, I explained how to join on multiple fields, but it is actually possible to join two tables on no fields at all. Using the "Old Style" syntax, such a join would look like:

SELECT t1.code, t2.code FROM t1,t2

"What does this type of query return?"

Suppose t1 has the character values "A","B","C" stored in the code column, and that the code column of t2 has the numeric values 1,2,3.

The above query will return the following rows:

In other words, a join without any join condition at all, returns every possible combination of rows from the joined tables. In SQL Server 7, this query can also be written as:

SELECT t1.code, t2.code FROM t1 CROSS JOIN t2

This syntax is clearer, in that it indicates that you intentionally joined the tables this way, and didn't just forget to write a join condition.

"OK, that sounds fascinating, but what the heck would I use it for?"

I actually ran across a use for this technique a few months ago. My client had a table specifying which combinations of values were to be rejected by the system. Something like the following:

Given the table of invalid combinations, the client wanted a list of all the valid combinations. ie the required output had to look like:

To do that, I used a nested query that looked something like: