MySQL CROSS JOIN Clause

A CROSS JOIN in MySQL is used to combine all rows from two or more tables. It returns the Cartesian product of rows from both tables, which means it will return a result set that is the number of rows in the first table multiplied by the number of rows in the second table, assuming there is no WHERE clause that restricts the output.

Syntax 

The standard syntax for the CROSS JOIN clause in MySQL is:

SELECT table1.column1, table2.column2,...
FROM table1 
CROSS JOIN table2;

In this syntax: 

table1 and table2: These are the names of the tables you wish to join. table1.

column1, table2.column2, ...: These are the columns you wish to select. 

Note: Unlike other types of joins, a CROSS JOIN doesn't require a condition to join the tables because it returns the Cartesian product of the rows.

Demo Database 

For the demonstration, we'll use our SchoolDB database. Our focus will be on the Students table and the Courses table. 

The Students table includes student_id, first_name, last_name, grade_level columns:

The Courses table includes course_id, course_name, and student_id columns:

Examples 

With our demo database in place, let's look at a few examples of the CROSS JOIN clause: 

Basic CROSS JOIN 

To obtain a list of all possible combinations of students and courses, we can use:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
CROSS JOIN Courses;

This statement will return a list of all students paired with each course, regardless of whether the student is enrolled in the course. 

CROSS JOIN with WHERE Condition 

A CROSS JOIN can be combined with a WHERE clause to filter results. For example, to find only the 12th-grade students and pair them with each course:

SELECT Students.first_name, Students.last_name, Courses.course_name
FROM Students 
CROSS JOIN Courses 
WHERE Students.grade_level = 12;

This statement will fetch all courses and pair them with 12th-grade students only.

Note. If you add a WHERE clause (if table1 and table2 have a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause.

When to Use CROSS JOIN

Use CROSS JOIN when you want a Cartesian product of both tables. Every row from the first table is combined with every row from the second table. 

Scenario: If you have a table Colors with values (Red, Blue) and a table Sizes with values (Small, Large), a CROSS JOIN would yield a result with combinations (Red, Small), (Red, Large), (Blue, Small), (Blue, Large).

Summary

A CROSS JOIN, also known as a Cartesian Product, returns the combination of every row of the first table with every row of the second table. If the first table has 'n' rows and the second table has 'm' rows, the result set will have n x m rows.