CROSS JOIN

Sql tutorial · PySpark.in

7.6 Cross Join

A CROSS JOIN combines every row from the first table with every row from the second table.

It is also called a Cartesian Join because it returns the Cartesian product of the two tables.

In simple words:

Each row of the first table is paired with all rows of the second table.

Unlike other joins, CROSS JOIN does not need a matching condition (ON).

Syntax:

```

hide

sql

SELECT *

FROM table1

CROSS JOIN table2;

```

Example

Perform a CROSS JOIN on the course and instructor tables.

Here:

the course table contains course names
the instructor table contains instructor names

A CROSS JOIN creates every possible course–instructor combination.

```

sql

DROP TABLE IF EXISTS course;

DROP TABLE IF EXISTS instructor;

-- Create instructor table

CREATE TABLE instructor (

instructor_id INTEGER PRIMARY KEY,

full_name TEXT

);

-- Create course table

CREATE TABLE course (

course_id INTEGER PRIMARY KEY,

course_name TEXT

);

-- Insert instructors

INSERT INTO instructor (instructor_id, full_name) VALUES

(101, 'Arun'),

(102, 'Neha'),

(103, 'Rohit');

-- Insert courses

INSERT INTO course (course_id, course_name) VALUES

(15, 'Machine Learning'),

(16, 'Cyber Security'),

(17, 'SQL Basics');

-- Perform CROSS JOIN

SELECT course.course_name AS course_name,

instructor.full_name AS instructor_name

FROM course

CROSS JOIN instructor;

```

How It Works

The course table has 3 rows.

The instructor table also has 3 rows.

With a CROSS JOIN:

3 × 3 = 9 rows

That means:

each course is paired with every instructor
no matching condition is checked

Important Point

A CROSS JOIN can quickly create a large number of rows.

If:

table A has 100 rows
table B has 200 rows

then the result will contain:

100 × 200 = 20,000 rows

So, use CROSS JOIN carefully with large tables.

When Is CROSS JOIN Useful?

A CROSS JOIN is useful when you want to generate all possible combinations, such as:

all products with all colors
all students with all available courses
all dates with all employees