SQL CROSS JOIN

Summary: in this tutorial, you’ll learn how to use the SQL CROSS JOIN clause to combine every row from the first table with every row in the second table.

Introduction to SQL CROSS JOIN clause #

The CROSS JOIN clause is an optional clause of the SELECT statement. The CROSS JOIN clause allows you to create combinations of all rows from two tables.

Here’s the syntax of the CROSS JOIN clause:

SELECT select_list FROM table1 CROSS JOIN table2;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, provide the first table in the FROM clause.
Second, specify the second table you want to merge the rows of the first table in the CROSS JOIN clause.

Unlike a left join, right join, inner join, and full join, the cross join does not have a condition.

The CROSS JOIN clause merges every row from the first table (table1) with every row in the second table (table2). It returns a result set that includes all possible combinations of the rows in both tables.

The result set of a CROSS JOIN is often called the Cartesian product of two tables.

If the table1 has n rows and table2 has m rows, the CROSS JOIN will return a result set that includes n * m rows.

For example, if the table1 has two rows and table2 has three rows, the result of the cross-join of the two tables will have 6 rows (2 * 3).

Alternatively, you can perform a cross-join by listing the tables in the FROM clause of the SELECT statement as follows:

SELECT select_list FROM table1, table2;Code language: SQL (Structured Query Language) (sql)

Understanding the cross join #

Suppose you have two tables:

The X table has two columns id (key) and x.
The Y table also has two columns id (key) and y.

The cross join combines every row from the left table (X) and the right table (Y) to create the final result set:

The following diagram is another way to illustrate a cross join:

SQL CROSS JOIN clause example #

First, create a new table called trainings to store the training programs:

CREATE TABLE trainings ( id INT PRIMARY KEY, program_name VARCHAR(255) NOT NULL, duration INT NOT NULL );Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert some rows into the trainings table:

INSERT INTO trainings (id, program_name, duration) VALUES (1, 'Leadership Skills', 1), (2, 'Communication Skills', 2);Code language: SQL (Structured Query Language) (sql)

Try it

Third, use a CROSS JOIN clause to generate all possible combinations of employees and training programs:

SELECT first_name, program_name FROM employees CROSS JOIN trainings ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | program_name -------------+---------------------- Adam | Communication Skills Adam | Leadership Skills Alexander | Leadership Skills Alexander | Communication Skills Alexander | Communication Skills Alexander | Leadership Skills Britney | Communication Skills Britney | Leadership Skills ...Code language: plaintext (plaintext)

The following query performs the same CROSS JOIN clause as the example above but uses the alternative syntax:

SELECT first_name, program_name FROM employees, trainings ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Summary #

Use the SQL CROSS JOIN clause to combine every row in the first table with every row from the second table.

Quiz #

<iframe name="quiz" src="/quiz/?quiz=cross-join" height="700" width="600" class="iframe" ></iframe> Code language: HTML, XML (xml)

Databases #

Was this tutorial helpful ?