This article provides an overview of the CROSS JOIN in SQL, as well as some basic examples.

The SQL CROSS JOIN (or FULL OUTER JOIN) returns rows which combine each row from the first table with each row from the second table.

In other words, it returns the Cartesian product of rows from tables in the join.

Syntax

You specify a cross join in the FROM clause.

The syntax goes like this:

SELECT *
FROM Table1 CROSS JOIN Table2 
ON Table1.Column = Table2.Column;

Example 1

Here’s an example to demonstrate.

Sample Data

First, here are the tables we’ll use for the example.

The t1 table:

+--------+
| col1   |
|--------|
| a      |
| b      |
| c      |
+--------+

The t2 table:

+--------+
| col1   |
|--------|
| 1      |
| 2      |
| 3      |
+--------+

The Cross Join Query

Here’s an example of performing a cross join against those two tables.

SELECT * FROM t1 
CROSS JOIN t2;

Result:

+--------+--------+
| col1   | col1   |
|--------+--------|
| a      | 1      |
| b      | 1      |
| c      | 1      |
| a      | 2      |
| b      | 2      |
| c      | 2      |
| a      | 3      |
| b      | 3      |
| c      | 3      |
+--------+--------+
(9 rows affected)

So our query returns 9 rows, even though there are only 3 rows in each table.

This is because of the way the cross join works. The number of rows returned is the number of rows in the left table, multiplied by the number of rows in the right table.

See how it iterates through the left table, outputting each row in the right table for each row in the left table.

This has the same effect as doing the following:

SELECT * FROM t1, t2

Result:

+--------+--------+
| col1   | col1   |
|--------+--------|
| a      | 1      |
| b      | 1      |
| c      | 1      |
| a      | 2      |
| b      | 2      |
| c      | 2      |
| a      | 3      |
| b      | 3      |
| c      | 3      |
+--------+--------+
(9 rows affected)

Example 2

Here’s what happens if we add a WHERE clause to our cross join.

SELECT * FROM t1 
CROSS JOIN t2
WHERE t1.col1 = 'a';

Result:

+--------+--------+
| col1   | col1   |
|--------+--------|
| a      | 1      |
| a      | 2      |
| a      | 3      |
+--------+--------+
(3 rows affected)

Example 3

Suppose we have the following tables.

The PetTypes table:

+-------------+-----------+
| PetTypeId   | PetType   |
|-------------+-----------|
| 1           | Bird      |
| 2           | Cat       |
| 3           | Dog       |
| 4           | Rabbit    |
+-------------+-----------+
(4 rows affected)

The Pets table:

+---------+-------------+-----------+-----------+------------+
| PetId   | PetTypeId   | OwnerId   | PetName   | DOB        |
|---------+-------------+-----------+-----------+------------|
| 1       | 2           | 3         | Fluffy    | 2020-11-20 |
| 2       | 3           | 3         | Fetch     | 2019-08-16 |
| 3       | 2           | 2         | Scratch   | 2018-10-01 |
| 4       | 3           | 3         | Wag       | 2020-03-15 |
| 5       | 1           | 1         | Tweet     | 2020-11-28 |
| 6       | 3           | 4         | Fluffy    | 2020-09-17 |
| 7       | 3           | 2         | Bark      | NULL       |
| 8       | 2           | 4         | Meow      | NULL       |
+---------+-------------+-----------+-----------+------------+
(8 rows affected)

In this case, the Pets.PetTypeId column is a foreign key of the PetTypes.PetTypeId column.

Now, here’s an example of running a cross join on those two tables, but using a WHERE clause.

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p 
CROSS JOIN PetTypes pt
WHERE p.PetTypeId = pt.PetTypeId;

Result:

+-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Cat       |
| Fetch     | Dog       |
| Scratch   | Cat       |
| Wag       | Dog       |
| Tweet     | Bird      |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Cat       |
+-----------+-----------+
(8 rows affected)

Adding the WHERE clause to the cross join turned it into an inner join.

Here’s what happens if we remove the WHERE clause.

SELECT 
    p.PetName,
    pt.PetType
FROM Pets p 
CROSS JOIN PetTypes pt;

Result:

+-----------+-----------+
| PetName   | PetType   |
|-----------+-----------|
| Fluffy    | Bird      |
| Fetch     | Bird      |
| Scratch   | Bird      |
| Wag       | Bird      |
| Tweet     | Bird      |
| Fluffy    | Bird      |
| Bark      | Bird      |
| Meow      | Bird      |
| Fluffy    | Cat       |
| Fetch     | Cat       |
| Scratch   | Cat       |
| Wag       | Cat       |
| Tweet     | Cat       |
| Fluffy    | Cat       |
| Bark      | Cat       |
| Meow      | Cat       |
| Fluffy    | Dog       |
| Fetch     | Dog       |
| Scratch   | Dog       |
| Wag       | Dog       |
| Tweet     | Dog       |
| Fluffy    | Dog       |
| Bark      | Dog       |
| Meow      | Dog       |
| Fluffy    | Rabbit    |
| Fetch     | Rabbit    |
| Scratch   | Rabbit    |
| Wag       | Rabbit    |
| Tweet     | Rabbit    |
| Fluffy    | Rabbit    |
| Bark      | Rabbit    |
| Meow      | Rabbit    |
+-----------+-----------+
(32 rows affected)

We get a cross join that returns 32 rows.