This is part of a four part post series on SQL Joins and Applies.
If you want to follow the examples given, you can get the T-SQL to create the tables, function and test data from my earlier post SQL Joins and Applies – Test Data.

The other posts in this series are:
SQL Joins and Applies – Understanding INNER JOIN
SQL Joins and Applies – Understanding OUTER JOIN
SQL Joins and Applies – Understanding APPLY

Cross join is probably the least useful join in a relational database.

From Books Online:

A join whose result set includes one row for each possible pairing of rows from the two tables.

Warning:
Cross Join can use a lot of memory when applied to large tables. Every row in the left table is repeated for every row of the right table (Unless you use WHERE to filter the results)

Here is a sample cross join:

SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
CROSS JOIN	dbo.tblSourceStrings tss;

And here is the large result set:

sourceValue_left sysID_left  sysID_Right sourceValue_right
---------------- ----------- ----------- -----------------
100              1           2           Apple
200              2           2           Apple
NULL             NULL        2           Apple
400              4           2           Apple
500              5           2           Apple
100              1           3           Banana
200              2           3           Banana
NULL             NULL        3           Banana
400              4           3           Banana
500              5           3           Banana
100              1           NULL        NULL
200              2           NULL        NULL
NULL             NULL        NULL        NULL
400              4           NULL        NULL
500              5           NULL        NULL
100              1           5           Dog
200              2           5           Dog
NULL             NULL        5           Dog
400              4           5           Dog
500              5           5           Dog
100              1           6           Egg
200              2           6           Egg
NULL             NULL        6           Egg
400              4           6           Egg
500              5           6           Egg

(25 row(s) affected)

Note:
The following two queries are identical both in their result set AND in execution plan.

SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
INNER JOIN	dbo.tblSourceStrings tss
	ON	tsi.sysID = tss.sysID;
 
SELECT		tsi.sourceValue [sourceValue_left],
		tsi.sysID [sysID_left],
		tss.sysID [sysID_Right],
		tss.sourceValue [sourceValue_right]
FROM		dbo.tblSourceIntegers tsi
CROSS JOIN	dbo.tblSourceStrings tss
WHERE		tsi.sysID = tss.sysID;