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.
Table of Contents
Syntax
There are two syntaxes available for a cross join
1 2 3 4 5 | Select [columns] from TableA cross join TableB |
You can also use the tables in from clause, without a where clause.
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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.
1 2 3 4 5 6 7 8 9 10 | 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
