As we know, cross joins without WHERE clause produce the Cartesian product of the tables. So, for example, if first table has 5 rows and second table has 3 rows, result set will have 15 rows with all possible combination of the values from the both tables.

Quite limited real-life usage though – I believe I used it maybe 2 times during last 5 years. First time it was the task to combine account specific configuration settings and system configuration settings into one row. Something like:


select
a.ConfigValue1
,a.ConfigValue2
,c.GlobalConfigValue1
,c.GlobalConfigValue2
from
dbo.Accounts a cross join dbo.Config c

Second time it was similar task – combined information from master record and list of detail record IDS in XML format. Could be easily accomplished with the function of course.


;with Details(UserList)
as
(
select UserId
from dbo.Users
where AccountId = @AccountID
for XML raw('User'), Root('Users')
)
select a.AccountId, a.AccountNumber, d.UserList
from dbo.Accounts a cross join Details d
where a.AccountId = @AccountID

Only problem with this approach – If one of the rowsets in the join is empty, it behaves as inner join and would not return any rows. This is absolutely correct behavior if you think about it, although you typically don’t expect it to work like that when you write it. You can see it with the following example:


;with CTE1(field1)
as
(
select 'field1' where 1 = 1
)
,CTE2(field2)
as
(
select 'field2' where 1 = 0
)
select CTE1.Field1, CTE2.Field2
from CTE1 cross join CTE2

So if you want to handle it properly, you need to change it from cross-join to full outer join with always true predicate. ;with CTE1(field1)
as
(
select 'field1' where 1 = 1
)
,CTE2(field2)
as
(
select 'field2' where 1 = 0
)
select CTE1.Field1, CTE2.Field2
from CTE1 full outer join CTE2 on
1 = 1

It will do the trick.