A cross-join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

This is an example of a Transact-SQL cross-join:

SELECT a.Firstname,
       a.Lastname,
       p.Name
FROM   Author a
CROSS JOIN Publisher p
ORDER BY a.Lastname DESC

The result set contains 184 rows

authors have 23 rows and publishers have 8.

Therefore the cross-join produces 184 rows. (23 x 8 = 184)

However, if a WHERE clause is added, the cross-join behaves as an inner join.

For example, these queries produce the same result set:

SELECT a.Firstname,
       a.Lastname,
       p.Name
FROM   Author a
CROSS JOIN Publisher p
WHERE  a.CityId = p.CityId
ORDER BY a.Lastname DESC

Or

USE pubs
SELECT a.Firstname,
       a.Lastname,
       p.Name
FROM   Author a
INNER JOIN Publisher p
  ON   a.CityId = p.CityId
ORDER BY a.Lastname DESC