Cross Join by Example in SQL Server

Cross join joins each row of one table with each row of another table. The result of the cross join is a Cartesian product (also called the cross product) of the two.

Syntax

There are two syntaxes available for a cross join

Select [columns]
from TableA
cross join TableB

You can also use the tables in from clause, without a where clause.

Select [columns]
from TableA , TableB

Cross Join

The best example of a cross product is a deck of cards. It contains 13 cards with rank from A, K, Q, J, 10, 9, 8, 7, 6, 5, 4, 3 & 2. It contains a card suite of heart, diamond, club, and spade. The cartesian product of the above cards results in a 52 elements representing every card is a set.

The following queries create the sample database for our cross join. It contains two tables. cards & suites.

create table cards (
 card char(2) primary key
)

insert into cards values ('A'), ('K'), ('Q'), ('J'),('10'),('9'),('8'),('7'),('6'),('5'),('4'),('3'),('2')


create table suites (
 suite char(1) primary key
)

insert into suites values ('S'), ('H'), ('D'), ('C')

Cross join Example

The following is the cross join of the above two tables.

select s.suite, c.card
from  cards c
cross join suites s

OR 

select s.suite, c.card
from  cards c , suites s 

The query will result in all possible combination of cards & suites totaling 52 rows

References

1. Join Syntax (SQL Server)
2. Sample database