Using Cross Joins

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:

USE pubs

SELECT au_fname, au_lname, pub_name

FROM authors CROSS JOIN publishers

ORDER BY au_lname DESC

The result set contains 184 rows (authors has 23 rows and publishers has 8; 23 multiplied by 8 equals 184).

However, if a WHERE clause is added, the cross join behaves as an inner join. For example, these Transact-SQL queries produce the same result set:

USE pubs

SELECT au_fname, au_lname, pub_name

FROM authors CROSS JOIN publishers

WHERE authors.city = publishers.city

ORDER BY au_lname DESC

-- Or

USE pubs

SELECT au_fname, au_lname, pub_name

FROM authors INNER JOIN publishers

ON authors.city = publishers.city

ORDER BY au_lname DESC

See Also