Export (0) Print
Expand All

Using Cross Joins

SQL Server 2000

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

WHERE

SELECT

Operators

SELECT Examples

Using Operators in Expressions

Show:
© 2015 Microsoft